SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Using a SELECtT Statment in side a CASE statment
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Eagle_f90
Constraint Violating Yak Guru

USA
419 Posts

Posted - 03/27/2013 :  10:40:52  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 03/27/2013 :  10:49:44  Show Profile  Reply with Quote
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

USA
419 Posts

Posted - 03/27/2013 :  12:42:33  Show Profile  Visit Eagle_f90's Homepage  Reply with Quote
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

India
52249 Posts

Posted - 03/28/2013 :  05:27:46  Show Profile  Reply with Quote

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


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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000