SQL Z97 if INSUB and Greater than 199

gpsdh
gpsdh used Ask the Experts™
on
How can I get matlxfer.xtype to equal 'Z97' if matlxfer.xtype = 'INVSUB' and matlxfer.xfer_vndlot > '199' and '551' if it's less.



 SELECT matlxfer.fm_cost_ctr ,
        matlxfer.to_cost_ctr ,
               CASE matlxfer.xfer_vndlot
        WHEN '200' THEN '1200'
        WHEN '201' THEN '1201'
        WHEN '202' THEN '1202'
        WHEN '203' THEN '1203'
        WHEN '204' THEN '1204'
        WHEN '205' THEN '1205'
        WHEN '206' THEN '1206'
        WHEN '207' THEN '1207'
      ELSE matlxfer.xfer_vndlot
      END AS xfer_vndlot   ,
        CASE matlxfer.xtype
          WHEN 'ADDINV' THEN '552'
          WHEN 'INVSUB' THEN '551'

Open in new window



xfer_vndlot	ADJUSTMENT
108	551
109	551
110	551
1200	551
1201	551
1202	551
1203	551
1204	551
1205	551
1206	551
1207	551

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Lead Software Engineer
Commented:
You can nest CASE Statements:
CASE matlxfer.xtype
          WHEN 'ADDINV' THEN '552'
          WHEN 'INVSUB' THEN
                 CASE WHEN matlxfer.xfer_vndlot > 199 THEN 'z97' ELSE '551' END
END

Author

Commented:
Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial