Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello my friends. I have been unable to figure out this problem. I am trying to filter the SAPMERCHVDIVISIONCODE alias in the statement below. I only want data with a value of 806 in this column. Is it possible to do within the same statement? If not, what would the separate statement look like. Thanks so much!!
SQL Select
PSPLN As TASK,
PSCASN,
PSSEA,
PSLTYP,
PSAREA,
PSZONE,
PSAISL,
PSBAY,
PSLEVL,
PSPOSN,
PSSYR,
PSSTYL,
PSSSFX,
CAST(LEFT(PSSYR || PSSTYL || PSSSFX,3) AS INTEGER) AS SAPMERCHDIVISIONCODE,
PSSYR || PSSTYL || PSSSFX AS SKU,
PSSZDS,
PSSQTY,
PSQPUL,
PSRLOC,
PSSTAT,
PSDCR,
PSCRT,
PLWHSE,
CASE PLWHSE
WHEN '001'
THEN TRIM(PSAREA) || TRIM(PSZONE) || Left(TRIM(PSAISL),1) || Right(TRIM(PSAISL),1) || TRIM(PSBAY)
WHEN '003'
THEN TRIM(PSAISL) || TRIM(PSLEVL) || TRIM(PSPOSN)
ELSE TRIM(PSAISL) || TRIM(PSLEVL) || TRIM(PSPOSN)
END AS RESERVELOCATION
From PKMANH221D.PSPULL00 A, PKMANH221D.PLPULL00 B
Where A.PSPLN = B.PLPLN
And A.PSCRT ='1'
And A.PSDCR >= $(zconvDate)
Order By A.PSPLN;
I should have added that this SELECT statement is written for DB2 as it is going out onto the server to retrieve data.
just add
AND CAST(LEFT(PSSYR || PSSTYL || PSSSFX,3) AS INTEGER) = 806
Thank you for the quick response Anders. Unfortunately this does not work for some reason. I thought if I added “AS SAPMERCHDIVISIONCODE” to the end of the statement that you provided it might work, but the word AS is not recognized.
Sorry then, I don't know DB2.
It should work SQL wise.
Alternatively you can do the filtering in Qlikview by adding
Temp:
SQL Select part
TableName:
Load *, 1 as Diff
Resident Temp
Where SAPMERCHDIVISIONCODE = 806;
Drop Table Temp;
Drop Field Diff;
But this is a wasteful approach.
Best wishes