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

It does work for me (although it prints warning, that there was a problem with extracting metadata and results are not editable, but the query does work and does return results as expected).

Can you send me your database? Maybe I created my test case somehow different than you. I'd like to test it on your tables and data.


About your SQL - it's technically correct, but it's overcompilcated. You can do it simpler:

Code: Select all
SELECT ta.*, tb."Customer" As SiteStatus
    FROM TableA ta
    LEFT JOIN TableB tb
    ON ta.ID = tb.ID;


But your version should work as well.
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

Created two tables with the DDL (based on what has been posted in this thread):

CREATE TABLE DNBDEMO (
DUNS INTEGER
);
CREATE TABLE [SC-Customers] (
DUNS INTEGER,
Customer TEXT
);

Executed the following querie:
Code: Select all
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;


Returned:
Code: Select all
Duns   Sitestatus
1   TEst
2   tet1
3   test2
4   test3


Executed the following querie:
Code: Select all
SELECT ta.DUNS, tb."Customer" As SiteStatus
FROM DNBDEMO ta
LEFT JOIN [SC-Customers] tb
ON ta.DUNS = tb.DUNS;


Returned:
Code: Select all
Duns   Sitestatus
1   TEst
2   tet1
3   test2
4   test3


Created the view "test" with the following DDL:
Code: Select all
SELECT *
FROM DNBDEMO
LEFT JOIN [SC-Customers]
ON DNBDEMO.DUNS = [SC-Customers].DUNS


Returns:
Code: Select all
Duns Duns:1 Customer
1   1   TEst
2   2   tet1
3   3   test2
4   4   test3


If you have nothing really special for those tables I don't see an issue here why the queries shouldn't execute normally.
Let's go the easy way and choose SQLite!
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

Your SQL query indicates that you wanted the column "customer" from the SC-Customer table. (see below bold marked spot)

ccjav wrote: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


That's why you would get the error: "no such column: Customer" (or because of the join a maybe different one)

Which table contains the column "customer"?


As a side note: A column defined as INTEGER PRIMARY KEY is UNIQUE. (as far as I know)
Let's go the easy way and choose SQLite!
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