Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
haneeshmarella
Contributor II

What am I doing wrong in the below query and sub query in QV SQL script?

I tried different changes, and have got different errors:


Let Pickdate = Date(Today(),'YYYYMMDD');


SQL

SELECT

T1.PRUSER AS PKMS_USER,

T1.PRCASN AS CASE#,

T1.PRTLOC AS TO_LOCATION,

T1.PRMNOP AS MENU_OPTION_NAME,

T1.PRFLOC AS FROM_LOCATION,

T1.PRUNTS AS UNITS#,

T1.PRSTYL AS STYLE,

T1.PHPKTN AS PICKTICKET#,

T1.PHSVIA AS PLANNED_SHIP_VIA,

T1.PHTCT AS TOTAL_NO_OF_CARTONS,

T1.PHSHCN AS SHIP_TO_COUNTRY,

T1.PRDCR AS DATE_STRING,

T1.PRTCR AS TIME_STRING,

T2.PRUSER AS PKMS_USERS,

T3.CECASN AS CONTAINER

FROM

(

(SQL SELECT

DISTINCT PHPICK00.PHPKTN,

PHPICK00.PHSHCN,

PHPICK00.PHSVIA,

PHPICK00.PHTCT,

PRTRAN00.PRUSER,

PRTRAN00.PRCASN,

PRTRAN00.PRTLOC,

PRTRAN00.PRMNOP,

PRTRAN00.PRFLOC,

PRTRAN00.PRSTYL,

PRTRAN00.PRUNTS,

PRTRAN00.PRDCR,

PRTRAN00.PRTCR

FROM

CAPM01.WM0272PRDD.PHPICK00 PHPICK00,

CAPM01.WM0272PRDD.PRTRAN00 PRTRAN00

WHERE

PHPICK00.PHPCTL = PRTRAN00.PRPCTL AND

PRTRAN00.PRTXTP = '500' AND

PRTRAN00.PRTXCD = '002' AND

PRTRAN00.PRWHSE='FG3' AND

PRTRAN00.PRDIV='08' AND

PHPICK00.PHWHSE='FG3' AND

PHPICK00.PHDIV='08' AND

(PRTRAN00.PRMNOP='Pick/Pack' OR PRTRAN00.PRMNOP = ' ' OR PRTRAN00.PRMNOP = 'Pick to PLT') AND

PRTRAN00.PRCASN IS NOT NULL AND

PRTRAN00.PRFLOC Like ' %M2%' AND

PRTRAN00.PRDCR = '$(Pickdate)' T1

LEFT JOIN

SELECT

PRTRAN00.PRCASN,

PRDLOG00.PRUSER

FROM

CAPM01.WM0272CSPD.PRDLOG00 PRDLOG00

CAPM01.WM0272PRDD.PRTRAN00 PRTRAN00

WHERE

PRTRAN00.PRCASN = PRDLOG00.PRCASN AND

PRTRAN00.PRTXTP='500' AND

PRTRAN00.PRTXCD='002' AND

PRTRAN00.PRWHSE='FG3' AND

PRDLOG00.PRWHSE='FG3' AND

PRTRAN00.PRDIV='08' AND

PRTRAN00.PRDCR='$(Pickdate)' AND

(PRTRAN00.PRMNOP='Pick/Pack' OR PRTRAN00.PRMNOP = ' ' OR PRTRAN00.PRMNOP = 'Pick to PLT') AND

PRTRAN00.PRCASN IS NOT NULL AND

PRTRAN00.PRFLOC Like '%M2%' AND

PRDLOG00.PRERRDS='Crtn Scanned OK' T2

ON T1.PRCASN=T2.PRCASN

)

LEFT JOIN

SELECT

DISTINCT CECVIF00.CECASN

FROM

CAPM01.WM0272PRDD.CECVIF00 CECVIF00,

CAPM01.WM0272PRDD.PRTRAN00 PRTRAN00

WHERE

PRTRAN00.PRCASN = CECVIF00.CECASN AND

PRTRAN00.PRTXTP='500' AND

PRTRAN00.PRTXCD='002' AND

PRTRAN00.PRWHSE='FG3' AND

PRTRAN00.PRDIV='08' AND

PRTRAN00.PRDCR='$(Pickdate)' AND

(PRTRAN00.PRMNOP='Pick/Pack' OR

PRTRAN00.PRMNOP = ' ' OR

PRTRAN00.PRMNOP = 'Pick to PLT') AND

PRTRAN00.PRCASN IS NOT NULL AND

PRTRAN00.PRFLOC Like '%M2%' AND

CECVIF00.CEWHSE='FG3' AND

CECVIF00.CEDIV='08' AND

CECVIF00.CEDCR='$(Pickdate)' T3

ON T1.PRCASN=T3.CECASN

);

15 Replies
Khan_Mohammed
Honored Contributor II

Re: What am I doing wrong in the below query and sub query in QV SQL script?

Not an expert in SQL but....

What error you getting?

Why not extract the data as is, store in QVD and then transform it using Qlik script?

You can use your Where in SQL but maybe not do joins yet

haneeshmarella
Contributor II

Re: What am I doing wrong in the below query and sub query in QV SQL script?

The data is huge, it's in millions of rows to store as is in a QVD. Hence, I am trying to only pull the current day data with certain conditions from various tables, make the script work before I could load it into the QVD further.

Khan_Mohammed
Honored Contributor II

Re: What am I doing wrong in the below query and sub query in QV SQL script?

Qlik can handle storing millions of records into QVD....

Anyway what error you getting?

Did you search for the solution on that error? Is it a syntax or related to your join conditions?

I hope anyone with SQL skill can help you

olivierrobin
Valued Contributor III

Re: What am I doing wrong in the below query and sub query in QV SQL script?

hello,

could you post the error ...

at 1st sight, I see a problem here

FROM

(

(SQL SELECT

you should erase sql (it is a Qlik keyword and in the middle of the sql statement, it is send to the relational databse which shoudn't like it)

haneeshmarella
Contributor II

Re: What am I doing wrong in the below query and sub query in QV SQL script?

I removed the SQL in the sub query but still get the following error:

SQL##f - SqlState: 37000, ErrorCode: 4294967192, ErrorMsg: [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token T1 was not valid. Valid tokens: ) FETCH ORDER UNION EXCEPT.

haneeshmarella
Contributor II

Re: What am I doing wrong in the below query and sub query in QV SQL script?

Hi, It's millions of rows per hour, and if I do so on the prod server, it would effect our operations.

This is the error, and I did try to research about it but it did not help.

SQL##f - SqlState: 37000, ErrorCode: 4294967192, ErrorMsg: [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token T1 was not valid. Valid tokens: ) FETCH ORDER UNION EXCEPT.

olivierrobin
Valued Contributor III

Re: What am I doing wrong in the below query and sub query in QV SQL script?

as it says T1, have a look here

'$(Pickdate)' T1

i thing a ) is missing before T1

same for T2 and T3



Khan_Mohammed
Honored Contributor II

Re: What am I doing wrong in the below query and sub query in QV SQL script?

Problem with the execution of SQL (SELECT UNION)

As they say in above link.. try your query in DB and check?

It will be much faster than in Qlik...

haneeshmarella
Contributor II

Re: What am I doing wrong in the below query and sub query in QV SQL script?

Made the changes, getting a new error

SQL##f - SqlState: 37000, ErrorCode: 4294967192, ErrorMsg: [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token CAPM01 was not valid. Valid tokens: ) FETCH ORDER UNION EXCEPT.

I did the above before, hence, I got different errors as mentioned originally.