Adding case statement to existing query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Adding case statement to existing query

Author

Topic

dest
Starting Member

8 Posts

Posted - 2008-04-03 : 17:53:57

I was asked to add an additional column to an existing query. I'm using Microsoft Query with a MS SQL 2000 server, and don't have much knowledge of SQL in general. Here's the existing query:


SELECT   A.COMPANYCODE,
         A.INVOICENUMBER,
         A.LINENUMBER,
         A.SONUMBER,
         A.CUSTOMERCODE,
         A.SHIPPERNUMBER,
         A.INVOICEDATE,
         A.ITEMCODE,
         A.QUANTITYINVOICED,
         A.UNITPRICE                                                                        AS 'InvPrice',
         A.QUANTITYINVOICED * A.UNITPRICE                                                   AS 'ExtInvPrice',
         INVENTORY.UNITPRICE                                                                AS 'StdPrice',
         INVENTORY.STANDARDCOST,
         A.QUANTITYINVOICED * INVENTORY.STANDARDCOST                                        AS 'ExtCost',
         (A.QUANTITYINVOICED * A.UNITPRICE) - (A.QUANTITYINVOICED * INVENTORY.STANDARDCOST) AS 'GM$',
         (INVENTORY.UNITPRICE - A.UNITPRICE) * -1                                           AS 'PriceListDiff'
FROM     ABW.DBO.SALESANALYSISHISTORY A,
         ABW.DBO.INVENTORY INVENTORY
WHERE    INVENTORY.COMPANYCODE = A.COMPANYCODE
         AND INVENTORY.ITEMCODE = A.ITEMCODE
         AND ((A.COMPANYCODE = 'csp')
              AND (A.QUANTITYINVOICED <>$ 0)
              AND (A.INVOICEDATE BETWEEN '03/1/08' AND '03/31/08'))
ORDER BY A.INVOICEDATE,
         A.ITEMCODE

They want a column added to the current query where if A.Unitprice is greater than or equal to Inventory.UnitPrice then populate the column with A.QuantityInvoiced*A.UnitPrice. I posted on another forum, and the advice I got was to add this:


SELECT NewColumn = CASE
  WHEN A.Unitprice >= Inventory.UnitPrice THEN A.QuantityInvoiced * A.Unitprice
  ELSE 'null'
END,
FROM     ABW.DBO.SALESANALYSISHISTORY A,
         ABW.DBO.INVENTORY INVENTORY

I'm not sure how to integrate it to the current query, so I've tried running it by itself, and I get the error: Didn't expect 'A' after the SELECT column list.

Any help would be greatly appreciated to modify the current query to display the new column.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-04-03 : 18:42:59

just tack it on to the rest of the stuff in your select list:


SELECT   A.COMPANYCODE,
         A.INVOICENUMBER,
         A.LINENUMBER,
         A.SONUMBER,
         A.CUSTOMERCODE,
         A.SHIPPERNUMBER,
         A.INVOICEDATE,
         A.ITEMCODE,
         A.QUANTITYINVOICED,
         A.UNITPRICE                                                                        AS 'InvPrice',
         A.QUANTITYINVOICED * A.UNITPRICE                                                   AS 'ExtInvPrice',
         INVENTORY.UNITPRICE                                                                AS 'StdPrice',
         INVENTORY.STANDARDCOST,
         A.QUANTITYINVOICED * INVENTORY.STANDARDCOST                                        AS 'ExtCost',
         (A.QUANTITYINVOICED * A.UNITPRICE) - (A.QUANTITYINVOICED * INVENTORY.STANDARDCOST) AS 'GM$',
         (INVENTORY.UNITPRICE - A.UNITPRICE) * -1                                           AS 'PriceListDiff'

,CASE
  WHEN A.Unitprice >= Inventory.UnitPrice THEN A.QuantityInvoiced * A.Unitprice
  ELSE null END as NewColumn

FROM     ABW.DBO.SALESANALYSISHISTORY A,
         ABW.DBO.INVENTORY INVENTORY
WHERE    INVENTORY.COMPANYCODE = A.COMPANYCODE
         AND INVENTORY.ITEMCODE = A.ITEMCODE
         AND ((A.COMPANYCODE = 'csp')
              AND (A.QUANTITYINVOICED <>$ 0)
              AND (A.INVOICEDATE BETWEEN '03/1/08' AND '03/31/08'))
ORDER BY A.INVOICEDATE,
         A.ITEMCODE

elsasoft.org

dest
Starting Member

8 Posts

Posted - 2008-04-03 : 18:57:18

That worked! Thank you so much, I really appreciate it!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources