This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C215A8.58421510
Content-Type: text/plain

Nested Case statements?

regards
David Cameron
nOw.b2b
dcameron@i...

> -----Original Message-----
> From: webgoddess63@y... [mailto:webgoddess63@y...]
> Sent: Friday, 14 June 2002 3:19 AM
> To: sql language
> Subject: [sql_language] Case Statement within a Select
> 
> 
> SELECT 	mFact.TimeKey, 
> 	mFact.VendorKey,
> 
> CASE 
> WHEN	(Month(tDim.sDate)<7) AND (SELECT SUM(sales) 
> 	FROM 		tblMSales
> 	INNER JOIN 	tblTimeData
> 	ON 		tblMSales.TimeKey = tblTimeData.TimeKey 
> 	INNER JOIN 	tblVendorData
> 	ON 		tblMSales.VendorKey = tblVendorData.VendorKey
> 	WHERE		tblTimeData.TimeKey = mFact.TimeKey
> 	AND		tblMSales.VendorKey = mFact.VendorKey) <> 0 
> THEN 	(SELECT 	(Sum(sales) - SUM(MajorCost)) / SUM(sales)
> 	FROM 		tblMSales
> 	INNER JOIN 	tblTimeData
> 	ON 		tblMSales.TimeKey = tblTimeData.TimeKey 
> 	INNER JOIN 	tblVendorData
> 	ON 		tblMSales.VendorKey = tblVendorData.VendorKey
> 	WHERE		tblTimeData.sDate >= ('7/1/'+CONVERT(varchar, 
> (DatePart(yyyy, tDim.sDate)-1)))
> 	AND		tblTimeData.sDate <=tDim.sDate
> 	AND		tblMSales.VendorKey = mFact.VendorKey)
> WHEN	(Month(tDim.sDate)>7) AND (SELECT 	SUM(sales) 
> 	FROM 		tblMSales
> 	INNER JOIN 	tblTimeData
> 	ON 		tblMSales.TimeKey = tblTimeData.TimeKey 
> 	INNER JOIN 	tblVendorData
> 	ON 		tblMSales.VendorKey = tblVendorData.VendorKey
> 	WHERE		tblTimeData.TimeKey = mFact.TimeKey
> 	AND		tblMSales.VendorKey = mFact.VendorKey) <> 0 
> THEN 	(SELECT 	(Sum(sales) - SUM(MajorCost)) / SUM(sales)
> 	FROM 		tblMSales
> 	INNER JOIN 	tblTimeData
> 	ON 		tblMSales.TimeKey = tblTimeData.TimeKey 
> 	INNER JOIN 	tblVendorData
> 	ON 		tblMSales.VendorKey = tblVendorData.VendorKey
> 	WHERE		tblTimeData.sDate >= ('7/1/'+CONVERT(varchar, 
> DatePart(yyyy, tDim.sDate)))
> 	AND		tblTimeData.sDate <=tDim.sDate
> 	AND		tblMSales.VendorKey = mFact.VendorKey)
> ELSE 	0 
> END
> 
> AS	GrossProfit
> 
> FROM 		tblMSales AS mFact
> INNER JOIN 	tblTimeDim AS tDim
> ON 		mFact.TimeKey = tDim.TimeKey 
> INNER JOIN 	tblVendorData AS mDim
> ON 		mFact.VendorKey = mDim.VendorKey
> 
> GROUP BY	mFact.VendorKey,
> 		mFact.TimeKey, 
> 		tDim.sDate
> ORDER BY 	mFact.VendorKey, 
> 		mFact.TimeKey
> 
> 
> 
> Using this statement I receive a divide by 0 error when Sales is 0, 
> althought I thought my ELSE in the Case statement would take care of 
> that.  If I comment out the THEN portions and Hard code a 
> number into the 
> THEN, it appears to work perfectly.  Is there something I'm 
> missing?  Is 
> there a book or a site that would cover learning how to do 
> Queries such as 
> this one?
> 
> Thanks in Advance.
>