Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
qliknerd
New Contributor III

Nested Select using ODBC on Load

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_IDCATEGORYREGIONCASE_DATE
1AUK01-Apr-12
2BUK20-Apr-12
3BUK30-Apr-12
4BUK11-May-12
5AUK15-May-12
6AGB18-Jun-12
7AUK21-Jun-12
8BUK25-Jul-12
9BGB31-Aug-12
10AUK24-Sep-12

EXTM:

EXTM_IDCASE_IDBU
111
221
312
432
581
621
711
811
922
1012

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_IDMB
21
3
4
81

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

Tags (3)
1 Solution

Accepted Solutions

Re: Nested Select using ODBC on Load

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#));


talk is cheap, supply exceeds demand
4 Replies

Re: Nested Select using ODBC on Load

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.


talk is cheap, supply exceeds demand
qliknerd
New Contributor III

Re: Nested Select using ODBC on Load

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

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#));


talk is cheap, supply exceeds demand
qliknerd
New Contributor III

Re: Nested Select using ODBC on Load

Fantastic! Works a treat, thanks!

Community Browser