Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have two tables: PHPICK00 and PRTRAN00. I am trying for an inner join based on the columns PHPCTL.PHPICK00=PRPCTL.PRTRAN00. The QV is connected to a Warehouse Management Software via ODBC connection. It gives me the below error. Please help.
Data_Source:
SQL
Select
DISTINCT PHPKTN.PHPICK00,
PHSHCN.PHPICK00,
PHSVIA.PHPICK00,
PHTCT.PHPICK00,
PRUSER.PRTRAN00,
PRCASN.PRTRAN00,
PRTLOC.PRTRAN00,
PRMNOP.PRTRAN00,
PRFLOC.PRTRAN00,
Date(Date#(PRDCR.PRTRAN00,'YYYYMMDD'),'MM/DD/YYYY'),
Time(Time#(PRTCR.PRTRAN00,'hhmmss'),'hh:mm:ss'),
PRSTYL.PRTRAN00,
PRUNTS.PRTRAN00
FROM PHPICK00 INNER JOIN PRTRAN00
ON PHPCTL.PHPICK00=PRPCTL.PRTRAN00
WHERE PRTXTP.PRTRAN00='500'
AND PRTXCD.PRTRAN00='002'
AND PRWHSE.PRTRAN00='FG3'
AND PRDIV.PRTRAN00='08'
AND PRDCR.PRTRAN00>=?
AND PRMNOP.PRTRAN00='Pick/Pack'
AND PRCASN.PRTRAN00>''
AND wildmatch(PRFLOC.PRTRAN00,* M3*)
ORDER BY PRUSER.PRTRAN00, Date(Date#(PRDCR.PRTRAN00,'YYYYMMDD'),'MM/DD/YYYY'), Time(Time#(PRTCR.PRTRAN00,'hhmmss'),'hh:mm:ss');
Also, the query pulls larger amount of data, how can I minimize the time to execute the above query?
Thank you in advance.
I am thinking your JOIN condition is wrong it should be like TABLENAME.COLUMNNAME
In the select clause
SELECT
TABLENAME.COLUMNNAME
FROM PHPICK00 INNER JOIN PRTRAN00
ON PHPICK00.PHPCTL = PRTRAN00.PRPCTL
For reducing the performance of the query you should remove the ORDER BY and DISTINCT from your query.
You can implement the DISTINCT and ORDER BY in resident load if neccessary after your first load.
I think it is because you are mixing up Qlik script function with SQL commands. Date# AND Time# are functions that don't exist in SQL.
Try this? And you are using your aliases incorrectly, it should be Tablename.Fieldname not Fieldname.TableName. Corrected below.
Data_Source:
LOAD *,
Date(Date#(PRDCR,'YYYYMMDD'),'MM/DD/YYYY') AS PRDCRDt,
Time(Time#(PRTCR,'hhmmss'),'hh:mm:ss') AS PRTCRTime
WHERE wildmatch(PRFLOC,* M3*);
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
FROM PHPICK00 INNER JOIN PRTRAN00
ON PHPICK00.PHPCTL = PRTRAN00.PRPCTL
WHERE PRTRAN00.PRTXTP ='500'
AND PRTRAN00.PRTXCD ='002'
AND PRTRAN00.PRWHSE ='FG3'
AND PRTRAN00.PRDIV ='08'
AND PRTRAN00.PRDCR >= '?'
AND PRTRAN00.PRMNOP ='Pick/Pack'
AND PRTRAN00.PRCASN >''
ORDER BY PRTRAN00.PRUSER, PRTRAN00.PRDCR, PRTRAN00.PRTCR;
Hi, I tweaked it as you have suggested and got the data. However, the last line highlighted in bold does not load when I tried, can you please help me filter out the source data based on the condition that the column PRFLOC should contain only '* M3*'.
Data_Source:
SQL SELECT DISTINCT A.PHPKTN AS PICKTICKET#,
A.PHSHCN AS SHIPTOCOUNTRY,
A.PHSVIA AS PLANNEDSHIPVIA,
A.PHTCT AS TOTALNOOFCARTONS,
B.PRUSER AS PKMS_USERS,
B.PRCASN AS CASE#,
B.PRTLOC AS TO_LOCATION,
B.PRMNOP AS MENU_OPTION_NAME,
B.PRFLOC AS FROM_LOCATION,
B.PRSTYL AS STYLE,
B.PRUNTS AS UNITS#,
B.PRDCR AS DATE_CREATED,
B.PRTCR AS TIME_CREATED
FROM CAPM01.WM0272PRDD.PHPICK00 A, CAPM01.WM0272PRDD.PRTRAN00 B
WHERE A.PHPCTL=B.PRPCTL
AND B.PRTXTP='500'
AND B.PRTXCD='002'
AND B.PRWHSE='FG3'
AND B.PRDIV='08'
AND B.PRDCR>'20180607'
AND B.PRMNOP='Pick/Pack'
AND B.PRCASN IS NOT NULL --> Query worked till here
AND wildmatch("B.PRFLOC",'* M3*'); --> This does not work and I need to filter it out to reduce the data to be loaded.
Thank you in advance.
AND B.PRFLOC Like '*M3*' // For Access
AND B.PRFLOC Like '%M3%' // For SQL Server
Works great with SQL Server syntax. Thank you very much William.