Using a SELECtT Statment in side a CASE statment

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

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

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources