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

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

haneeshmarella — Wed, 25 Nov 2020 16:16:04 GMT

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

);

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

MK9885 — Mon, 09 Jul 2018 14:25:05 GMT

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

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

haneeshmarella — Mon, 09 Jul 2018 14:33:59 GMT

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.

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

MK9885 — Mon, 09 Jul 2018 14:40:24 GMT

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

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

olivierrobin — Mon, 09 Jul 2018 14:44:35 GMT

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)

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

haneeshmarella — Mon, 09 Jul 2018 14:52:12 GMT

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.

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

haneeshmarella — Mon, 09 Jul 2018 14:53:08 GMT

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.

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

olivierrobin — Mon, 09 Jul 2018 14:59:06 GMT

as it says T1, have a look here

'$(Pickdate)' T1

i thing a ) is missing before T1

same for T2 and T3

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

MK9885 — Mon, 09 Jul 2018 15:01:00 GMT

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...

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

haneeshmarella — Mon, 09 Jul 2018 15:15:01 GMT

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.

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

olivierrobin — Mon, 09 Jul 2018 15:21:33 GMT

in the error message, you have

Token CAPM01 was not valid

try to see where it is defined, how it is defined and what it should be

it could be a good idea to test independently each subquery in a tool like dbvis, toad, ...

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

haneeshmarella — Mon, 09 Jul 2018 16:21:01 GMT

So that was a comma missing, my bad.

OK, so the queries work individually. The problem I face is when I try to name them T1 or T2 to join them. For example from the question, when I name the below select statement from multiple tables as T2, it errors out. Do you know if I am using the right method to name it T2?

(SELECT

B.PRCASN,

C.PRUSER

FROM

CAPM01.WM0272CSPD.PRDLOG00 C,

CAPM01.WM0272PRDD.PRTRAN00 B

WHERE

B.PRCASN = C.PRCASN AND

B.PRTXTP='500' AND

B.PRTXCD='002' AND

B.PRWHSE='FG3' AND

C.PRWHSE='FG3' AND

B.PRDIV='08' AND

B.PRDCR> '20180708' AND

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

B.PRCASN IS NOT NULL AND

B.PRFLOC Like '%M2%' AND

C.PRERRDS='Crtn Scanned OK') T2

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

BalaBhaskar_Qlik — Mon, 09 Jul 2018 17:07:48 GMT

Table1 sql script left join table 2,.. On what basis you declared, the left join in the above sql script.

Like:

Sql:

select abc.col1,bcd.col2 from abc left join bcd on

abc.col1 = bcd.col1;

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

sunny_talwar — Mon, 09 Jul 2018 17:17:25 GMT

Can you try this

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;

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

olivierrobin — Tue, 10 Jul 2018 07:06:39 GMT

what is the error message ?

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

Miguel_Angel_Baeyens — Tue, 10 Jul 2018 07:11:38 GMT

You will have to refer to the driver syntax documentation. In many systems AS is also used to reference queries as table names. However again, the error is quite explicit, and it prompts for any of the following 4 keywords: FETCH, ORDER, UNION or EXCEPT.

Have you tried the query in a query manager like Toad using the same driver than QlikView uses? Does it allow the syntax?