Improve company productivity with a Business Account.Sign Up


SQL Z97 if INSUB and Greater than 199

Posted on 2014-02-28
Medium Priority
Last Modified: 2014-02-28
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

Question by:gpsdh
LVL 27

Accepted Solution

Shaun Kline earned 2000 total points
ID: 39895974
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

Author Closing Comment

ID: 39895987

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

602 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question