locked
Help with CASE in SQL RRS feed

  • Question

  • User261842408 posted

    I have a query that I need to add a CASE to but am not sure how. I'll post the full SQL, without the (names) "AS", except for "ExtdCost", which is the focus of this request.

    Existing SQL:

    SELECT RCHASE5C.M800DAT200.OP100M1.SHSAN, RCHASE5C.M800DAT200.OP100M2.SLPTNO, 
    RCHASE5C.M800DAT200.OP100M2.SLPTD1, 
    RCHASE5C.M800DAT200.OP100M2.SLPTD2, 
    RCHASE5C.M800DAT200.OP100M1.SHONO, 
    RCHASE5C.M800DAT200.OP100M1.SHORDT,
    RCHASE5C.M800DAT200.OP100M2.SLSQTY, RCHASE5C.M800DAT200.OP100M2.SLUNCS,
    RCHASE5C.M800DAT200.OP100M1.SHORCL

    RCHASE5C.M800DAT200.OP100M2.SLSQTY * RCHASE5C.M800DAT200.OP100M2.SLUNCS AS ExtdCost, RCHASE5C.M800DAT200.OP100M1.SHOST, FROM RCHASE5C.M800DAT200.OP100M1, RCHASE5C.M800DAT200.OP100M2 WHERE RCHASE5C.M800DAT200.OP100M1.SHONO = RCHASE5C.M800DAT200.OP100M2.SLONO
    AND RCHASE5C.M800DAT200.OP100M1.SHRLNO = RCHASE5C.M800DAT200.OP100M2.SLRLNO
    AND (RCHASE5C.M800DAT200.OP100M1.SHSAN = ?)
    AND (RCHASE5C.M800DAT200.OP100M1.SHORCL = 'IA'
    OR RCHASE5C.M800DAT200.OP100M1.SHORCL = 'I')
    ORDER BY RCHASE5C.M800DAT200.OP100M2.SLPTNO

    I need to add a CASE to use the cost from another table if the part# begins with '901'. I'll put it in an IF/THEN statement:

      IF 

    RCHASE5C.M800DAT200.OP100M2.SLPTNO  LIKE  '901'

    THEN

    RCHASE5C.M800DAT200.OP100M2.SLSQTY * RCHASE5C.M800DAT200.DE100M.PMSACQ AS ExtdCost, RCHASE5C.M800DAT200.OP100M1.SHOST,

    Can someone please help me insert this as CASE into the rest of the SQL code?

    Thanks,

    JC

    Friday, October 9, 2009 2:21 PM

Answers

  • User77042963 posted

     

    CASE WHEN LEFT(RCHASE5C.M800DAT200.OP100M2.SLPTNO,3)= '901'THEN
    RCHASE5C.M800DAT200.OP100M2.SLSQTY * RCHASE5C.M800DAT200.DE100M.PMSACQ 
    ELSE
    ---Anything here?
    END  AS ExtdCost,


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 9, 2009 2:49 PM

All replies

  • User77042963 posted

     

    CASE WHEN LEFT(RCHASE5C.M800DAT200.OP100M2.SLPTNO,3)= '901'THEN
    RCHASE5C.M800DAT200.OP100M2.SLSQTY * RCHASE5C.M800DAT200.DE100M.PMSACQ 
    ELSE
    ---Anything here?
    END  AS ExtdCost,


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 9, 2009 2:49 PM
  • User261842408 posted

    Many thanks, Limno. The ELSE was using the cost from the original table. This works!!



    Friday, October 9, 2009 3:47 PM
  • User-355840286 posted

    This may also help SQL Case Statement


    Thursday, December 23, 2010 11:42 AM