Filter results of column alias in SELECT statement

[Anonymous]

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;

[Anonymous]

I should have added that this SELECT statement is written for DB2 as it is going out onto the server to retrieve data.

[calvindk]

just add

AND  CAST(LEFT(PSSYR || PSSTYL || PSSSFX,3) AS INTEGER) = 806


[Anonymous]

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.

[calvindk]

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