[phpBB Debug] PHP Warning: in file [ROOT]/includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Warning: in file [ROOT]/includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Warning: in file [ROOT]/includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Warning: in file [ROOT]/includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Warning: in file [ROOT]/includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Warning: in file [ROOT]/includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Warning: in file [ROOT]/includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Warning: in file [ROOT]/includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Warning: in file [ROOT]/includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Warning: in file [ROOT]/includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Warning: in file [ROOT]/includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Warning: in file [ROOT]/includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Warning: in file [ROOT]/includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Warning: in file [ROOT]/includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Warning: in file [ROOT]/includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Warning: in file [ROOT]/includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Warning: in file [ROOT]/includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Warning: in file [ROOT]/includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Warning: in file [ROOT]/includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Warning: in file [ROOT]/includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Warning: in file [ROOT]/includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Warning: in file [ROOT]/includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Warning: in file [ROOT]/includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Warning: in file [ROOT]/includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Warning: in file [ROOT]/includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Warning: in file [ROOT]/includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Warning: in file [ROOT]/includes/bbcode.php on line 112: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Warning: in file [ROOT]/includes/bbcode.php on line 112: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Warning: in file [ROOT]/includes/bbcode.php on line 112: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Warning: in file [ROOT]/includes/functions.php on line 4752: Cannot modify header information - headers already sent by (output started at [ROOT]/includes/functions.php:3887)
[phpBB Debug] PHP Warning: in file [ROOT]/includes/functions.php on line 4754: Cannot modify header information - headers already sent by (output started at [ROOT]/includes/functions.php:3887)
[phpBB Debug] PHP Warning: in file [ROOT]/includes/functions.php on line 4755: Cannot modify header information - headers already sent by (output started at [ROOT]/includes/functions.php:3887)
[phpBB Debug] PHP Warning: in file [ROOT]/includes/functions.php on line 4756: Cannot modify header information - headers already sent by (output started at [ROOT]/includes/functions.php:3887)
sqlitestudio.pl • View topic - Issue Joining Table With View

Issue Joining Table With View

Problems, tips, hints - all about usage.

Issue Joining Table With View

Postby ccjav » Mon May 11, 2015 6:08 pm

I am trying to write a SQL join statement, and am unsure if my issue is with SQLite or SQLiteStudio. I remember writing a similar call in sqlite in python and it working, but my memory is hazy. A short example:

I have two tables, each with an ID field. Some of the IDs in TableA match with IDs in TableB.

The first call I tried was:

SELECT *
FROM TableA
LEFT JOIN (SELECT ID, "Customer" As SiteStatus FROM TableB) x
ON TableA.ID = x.ID

Expecting to see "Customer" in the SiteStatus field for my new table, on any record that is in TableB. Running this gives: Error while executing SQL query on database 'CustomerDB'

I then tried creating a view called TableBView, defined as:

SELECT ID, "Customer" As SiteStatus
FROM TableB

Calling:

SELECT *
FROM TableA
LEFT JOIN TableBView
ON TableA.ID = TableBView.ID

Also results in Error while executing SQL query on database 'CustomerDB'

My questions would be:

-Am I doing something wrong in SQL?
-Is there an error description that would be more relevant to this problem?

Windows 7, SQLiteStudio 3.0.5
ccjav
 
Posts: 14
Joined: Fri Feb 20, 2015 2:01 pm

Re: Issue Joining Table With View

Postby Googie » Mon May 11, 2015 8:08 pm

Googie
Site Admin
 
Posts: 790
Joined: Wed Jul 27, 2011 8:04 pm

Re: Issue Joining Table With View

Postby ccjav » Tue May 12, 2015 12:28 am

Hi Googie,

Thanks for your reply. My database is ~12GB, so it would be unfeasible to send. In trying to create a reproducible example, I've grokked that it had to do with my select statement. I was calling ID in my return as:

SELECT ID , ....
FROM TableA
LEFT JOIN (SELECT ID, "Customer" As SiteStatus FROM TableB) x
ON TableA.ID = x.ID

Which gives an error. Changing this to TableA.ID error allows the code to run. Normally the error code would return "duplicate column name", which I did not know to look for. Glad to know the issue wasn't with SQL.

However, I now get a Visual C++ Runtome Library error on the call (after it runs for ~10 seconds). Might be due to the size of the call (26Million records on 30K). Haven't seen anything like it before.
ccjav
 
Posts: 14
Joined: Fri Feb 20, 2015 2:01 pm

Re: Issue Joining Table With View

Postby Googie » Tue May 12, 2015 7:01 am

It should not matter (the size). I will look at it - the whole problem.
Googie
Site Admin
 
Posts: 790
Joined: Wed Jul 27, 2011 8:04 pm

Re: Issue Joining Table With View

Postby ccjav » Tue May 12, 2015 5:00 pm

Perfect thanks. If it helps, the exact call that trips the C++ error:

SELECT DNBDEMO.DUNS, x.SiteStatus
FROM DNBDEMO
LEFT JOIN (SELECT [SC-Customers].DUNS, "Customer" As SiteStatus FROM [SC-Customers]) x
ON DNBDEMO.DUNS = x.DUNS

I also notice that during the call the memory usage of sqlstudio skyrockets to about 2GB then crashes. Every other call I made does not affect memory usage, which hovers atound 43MB

Additionally, your call below does not work for me. I get "no such column: Customer"

SELECT ta.DUNS, tb."Customer" As SiteStatus
FROM DNBDEMO ta
LEFT JOIN [SC-Customers] tb
ON ta.DUNS = tb.DUNS;
ccjav
 
Posts: 14
Joined: Fri Feb 20, 2015 2:01 pm

Re: Issue Joining Table With View

Postby Googie » Wed May 13, 2015 7:53 pm

Could you please paste here the DDL of tables SC-Customers and DNBDEMO, or are they secret?
Googie
Site Admin
 
Posts: 790
Joined: Wed Jul 27, 2011 8:04 pm

Re: Issue Joining Table With View

Postby Artur » Thu May 14, 2015 12:07 am

User avatar
Artur
 
Posts: 129
Joined: Thu Jul 28, 2011 12:10 am
Location: Bavaria, Germany

Re: Issue Joining Table With View

Postby ccjav » Thu May 14, 2015 7:09 pm

A couple of differences:

There is no column called Customer in [SC-Customers]. Here are the DDLs:

CREATE TABLE DNBDEMO (
DUNS VARCHAR (9) PRIMARY KEY
UNIQUE,
DataA VARCHAR(8),
DataB BIGINT,
DataC BIGINT
DataD VARCHAR(2)
);

CREATE TABLE [SC-Customers] (
DUNS VARCHAR (9) PRIMARY KEY
UNIQUE
);
ccjav
 
Posts: 14
Joined: Fri Feb 20, 2015 2:01 pm

Re: Issue Joining Table With View

Postby Artur » Fri May 15, 2015 3:43 am

User avatar
Artur
 
Posts: 129
Joined: Thu Jul 28, 2011 12:10 am
Location: Bavaria, Germany

Re: Issue Joining Table With View

Postby ccjav » Fri May 15, 2015 8:29 pm

To my knowledge, in sqlite you can create columns of strings using the notation above. Take any table in your database, include

"Customer" As SiteStatus

in your select statement, and a column should be created regardless of whether it is in your initial table.
ccjav
 
Posts: 14
Joined: Fri Feb 20, 2015 2:01 pm

Next

Return to Usage

Who is online

Users browsing this forum: No registered users and 2 guests

cron