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
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Using a SELECtT Statment in side a CASE statment

Author  Topic 

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2013-03-27 : 10:40:52
I am trying to write a CASE statment that usese a SELECT stamnet in the THAN and ELSE clause but am not sure how to do this. My existing code gives tha error of

quote:
Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'SELECT'.
Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'ELSE'.
Msg 156, Level 15, State 1, Line 30
Incorrect syntax near the keyword 'END'.



SELECT term.Terminal_Name AS Name,
di.ProductName + ' (' + di.ProductNumber + ')' AS Product,
Beginning = CASE WHEN term.Gross_Gallons_Terminal = 'Y'
THEN
di.StartingGrossInv
ELSE
di.StartingNetInv
END,
Adjustments = CASE WHEN term.Gross_Gallons_Terminal = 'Y'
THEN
SELECT SUM(Gross_Gallons)
FROM dbo.Rcpt_and_Adj
WHERE Type = 'A'
ELSE
SELECT SUM(Net_Gallons)
FROM dbo.Rcpt_AND_Adj
WHERE Type = 'A'
END
FROM dbo.DailyInventory as di
JOIN dbo.Terminal AS term ON di.JDELocation = term.JDELocation
LEFT JOIN dbo.Transactions AS trans ON di.JDELocation = trans.JDELocation
LEFT JOIN dbo.Rcpt_And_Adj AS ra ON di.JDELocation = ra.JDELocation AND trans.Ticket_Number = ra.Ticket_Number
WHERE term.Terminal_ProductionTest = 1
GROUP BY term.Terminal_Name, di.ProductName, di.ProductNumber, term.Gross_Gallons_Terminal, di.StartingGrossInv, di.StartingNetInv


--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-27 : 10:49:44
SQL syntax does not allow you to do it the way you are doing it. You could rewrite it as follows, which should be the same:
SELECT term.Terminal_Name AS Name,
di.ProductName + ' (' + di.ProductNumber + ')' AS Product,
Beginning = CASE WHEN term.Gross_Gallons_Terminal = 'Y'
THEN
di.StartingGrossInv
ELSE
di.StartingNetInv
END,
Adjustments = CASE WHEN term.Gross_Gallons_Terminal = 'Y'
THEN
SUM(CASE WHEN ra.Type='A' THEN ra.Gross_Gallons ELSE 0 END)
ELSE
SUM(CASE WHEN ra.Type='A' THEN ra.Net_Gallons ELSE 0 END)
END
FROM dbo.DailyInventory as di
JOIN dbo.Terminal AS term ON di.JDELocation = term.JDELocation
LEFT JOIN dbo.Transactions AS trans ON di.JDELocation = trans.JDELocation
LEFT JOIN dbo.Rcpt_And_Adj AS ra ON di.JDELocation = ra.JDELocation AND trans.Ticket_Number = ra.Ticket_Number
WHERE term.Terminal_ProductionTest = 1
GROUP BY term.Terminal_Name, di.ProductName, di.ProductNumber, term.Gross_Gallons_Terminal, di.StartingGrossInv, di.StartingNetInv
Go to Top of Page

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2013-03-27 : 12:42:33
Thanks, that looks to have worked.

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-28 : 05:27:46
[code]
SELECT term.Terminal_Name AS Name,
di.ProductName + ' (' + di.ProductNumber + ')' AS Product,
Beginning = MAX(CASE WHEN term.Gross_Gallons_Terminal = 'Y'
THEN
di.StartingGrossInv
ELSE
di.StartingNetInv
END),
Adjustments = SUM(CASE WHEN term.Gross_Gallons_Terminal = 'Y' AND ra.Type='A'
THEN ra.Gross_Gallons
WHEN term.Gross_Gallons_Terminal <> 'Y' AND ra.Type='A'
THEN ra.Net_Gallons ELSE 0 END)
FROM dbo.DailyInventory as di
JOIN dbo.Terminal AS term ON di.JDELocation = term.JDELocation
LEFT JOIN dbo.Transactions AS trans ON di.JDELocation = trans.JDELocation
LEFT JOIN dbo.Rcpt_And_Adj AS ra ON di.JDELocation = ra.JDELocation AND trans.Ticket_Number = ra.Ticket_Number
WHERE term.Terminal_ProductionTest = 1
GROUP BY term.Terminal_Name, di.ProductName, di.ProductNumber, term.Gross_Gallons_Terminal, di.StartingGrossInv, di.StartingNetInv
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -