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

[haneeshmarella]

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

);

[olivierrobin]

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

[haneeshmarella]

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

[balabhaskarqlik]

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;

[sunny_talwar]

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;

[olivierrobin]

what is the error message ?

[Miguel_Angel_Baeyens]

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?