topic Nested Select using ODBC on Load in QlikView

Nested Select using ODBC on Load

qliknerd — Tue, 11 Dec 2012 13:49:05 GMT

Hi there

I have a question regarding loading data from ODBC using a Nested Select. The SQL was initially developed in MS Access, and tested in SQL Server. The queries both ran fine and bring back the data I need. However, when I try the same query in QlikView, I get an error.

Example data tables and SQL:

CASE:

CASE_ID	CATEGORY	REGION	CASE_DATE
1	A	UK	01-Apr-12
2	B	UK	20-Apr-12
3	B	UK	30-Apr-12
4	B	UK	11-May-12
5	A	UK	15-May-12
6	A	GB	18-Jun-12
7	A	UK	21-Jun-12
8	B	UK	25-Jul-12
9	B	GB	31-Aug-12
10	A	UK	24-Sep-12

EXTM:

EXTM_ID	CASE_ID	BU
1	1	1
2	2	1
3	1	2
4	3	2
5	8	1
6	2	1
7	1	1
8	1	1
9	2	2
10	1	2

SQL:

SELECT CASE.CASE_ID, MB.MB
FROM [CASE] LEFT JOIN [SELECT DISTINCT CASE.CASE_ID, 1 as MB
FROM CASE LEFT JOIN EXTM ON CASE.CASE_ID = EXTM.CASE_ID
WHERE EXTM.BU='1']. AS MB ON CASE.CASE_ID = MB.CASE_ID
WHERE (((CASE.CATEGORY)="B") AND ((CASE.REGION)="UK") AND ((CASE.CASE_DATE) Between #4/1/2010# And #3/31/2014#));

OUTPUT:

CASE_ID	MB
2	1
3
4
8	1

What I am doing here is determining whether or not a Case has a related MB, if it does return a 1, if not leave blank.

However, when I tried to use the SQL in a QlikView load, I got this error:

SQL##f - SqlState: 37000, ErrorCode: 103, ErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]The identifier that starts with 'SELECT DISTINCT... is too long. Maximum length is 128.'

Have been stuck for 24 hours now!! Hopefully someone has come across this before and knows a solution?!?

Thanks

QlikNerd

Re: Nested Select using ODBC on Load

Gysbert_Wassenaar — Thu, 20 Dec 2012 18:07:28 GMT

SELECT CASE.CASE_ID, MB.MB

FROM [CASE] LEFT JOIN [SELECT DISTINCT CASE.CASE_ID, 1 as MB

FROM CASE LEFT JOIN EXTM ON CASE.CASE_ID = EXTM.CASE_ID

WHERE EXTM.BU='1']. AS MB ON CASE.CASE_ID = MB.CASE_ID

WHERE (((CASE.CATEGORY)="B") AND ((CASE.REGION)="UK") AND ((CASE.CASE_DATE) Between #4/1/2010# And #3/31/2014#));

Your subselect is between square brackets. Shouldn't those be parentheses? There's also a dot after the closing square bracket. Possible the square brackets and/or the dot are making the database think that the text between the brackets is a table or field name and that it finds that that name is too long to be valid.

Re: Nested Select using ODBC on Load

qliknerd — Fri, 21 Dec 2012 10:44:20 GMT

Thanks for the reply Gysbert, you're correct in that the QlikView is interpretting the bracketed text as a field name. The SQL is how MS Access outputs and includes the square brackets and the dot. If these are replaced with normal brackets and the dot is removed, then the SQL executes in SQL Server but still won't run in QlikView

I am stumped, and may need to rework the SQL. Although I am SQL/ODBC driven, loading data in QlikView is confusing to me!!

Re: Nested Select using ODBC on Load

Gysbert_Wassenaar — Fri, 21 Dec 2012 11:16:34 GMT

It's not Qlikview that interpretting, but the odbc driver. All Qlikview does is hand over the sql statement to the driver. You may indeed want to try to rewrite it. What Access made doesn't make much sense to me. I have no idea if this works, but to me it looks more like what I expect such a statement to look like:

SELECT DISTINCT CASE.CASE_ID, 1 as MB

FROM [CASE] LEFT JOIN CASE AS MB ON CASE.CASE_ID = MB.CASE_ID

LEFT JOIN EXTM ON (MB.CASE_ID = EXTM.CASE_ID and EXTM.BU='1')

WHERE (((CASE.CATEGORY)="B") AND ((CASE.REGION)="UK") AND ((CASE.CASE_DATE) Between #4/1/2010# And #3/31/2014#));

Re: Nested Select using ODBC on Load

qliknerd — Fri, 21 Dec 2012 11:53:03 GMT

Fantastic! Works a treat, thanks!