November 1, 2012 at 11:23 am
Hello-
I'm in the process of building a script with a CASE statement and for some reason I cannot get the CASE statement to work. It's not throwing up errors or anyting, it's just not picking up by looking at p.State. Can someone please tell me what I'm doing worng?
WITH ApplianceMapping AS
(SELECT cp.PackageID AS CurPackageID, gm.CurrentNum, cp.PackageID AS CurPackageID, ap1.AppliancePackageID AS CurAppliancePackageID,
cp2.PackageID AS NewPackageID, gm.NewNum, cp2.PackageID AS NewPackageID, ap2.AppliancePackageID AS NewAppliancePackageID
FROM dbo.tblPackagePackages cp
INNER JOIN Data_User.dbo.SCR32069JCW_GapMapping gm
ON cp.PackageCode = gm.CurrentNum
INNER JOIN dbo.tblPackagePackages cp2
ON gm.NewNum = cp2.PackageCode
AND cp.PackageID = cp2.PackageID
INNER JOIN dbo.tblAppliancePackages ap1
ON cp.PackageID = ap1.PackageID
AND ap1.ApplianceID NOT IN (286,8,9,71,188,257,319,321,322,354,358,386,397,398,399,
403,405,416,457,501,504,522,528,552,556,557,604,615,632)
INNER JOIN dbo.tblAppliancePackages ap2
ON cp2.PackageID = ap2.PackageID
AND ap2.ApplianceID NOT IN (286,8,9,71,188,257,319,321,322,354,358,386,397,398,399,
403,405,416,457,501,504,522,528,552,556,557,604,615,632))
SELECT DISTINCT p.state,pm.CurrentNum,pm.NewNum, pm.NewPackageID, pm.NewAppliancePackageID, vp.ProducerID, vp.Rep1, vp.PackageStat, vp.PackageSplit, vp.PolicyType,
vp.HouseSplit, vp.SalesSplit, vp.EscrowSplit, vp.DealerRemit, vp.ApplianceCom,
'This is a test' AS Comment, vp.ContractDate, NULL AS CancelDate, vp.MasterPolicy,
NULL AS ModifiedBy, vp.Location, vp.WebPackage, GETDATE() AS CreatedOn, NULL AS ModifiedOn, 'I.T.' AS CreatedBy,
'11/01/2012' AS EffectiveDate, vp.RateLookupID, vp.PriValidPackageID, MIN(vp.ValidPackageID) AS PriorValidPackageID, vp.ImportID,
vp.RateLookupStateID
FROM dbo.tblValidPackages vp
INNER JOIN dbo.tblAppliancePackages ap
ON vp.AppliancePackageID = ap.AppliancePackageID
AND ap.ApplianceID NOT IN (286,8,9,71,188,257,319,321,322,354,358,386,397,398,399,
403,405,416,457,501,504,522,528,552,556,557,604,615,632)
INNER JOIN dbo.tblCustomers P
ON vp.ProducerID=P.ProducerID
INNER JOIN ApplianceMapping pm
ON vp.PackageID = pm.CurPackageID
LEFT OUTER JOIN dbo.tblPackagePackages cp
ON CASE
WHEN p.state='NH' AND pm.CurrentNum='CP294' THEN 'CP20960811'
WHEN p.state='CO' AND pm.CurrentNum='CP307' THEN 'CP20970811'
WHEN p.state='NH' AND pm.CurrentNum='CP307' THEN 'CP20980811'
WHEN p.state='NH' AND pm.CurrentNum='CP308' THEN 'CP23020811'
WHEN p.state='NH' AND pm.CurrentNum='CP310' THEN 'CP23020811'
WHEN p.state='NH' AND pm.CurrentNum='CP582' THEN 'CP20090811'
WHEN p.state='NH' AND pm.CurrentNum='CP728' THEN 'CP20130811'
WHEN p.state='NH' AND pm.CurrentNum='CP907' THEN 'CP20200811'
WHEN p.state='NH' AND pm.CurrentNum='CP802' THEN 'CP21040811'
WHEN p.state IN ('CA', 'CT','WY') AND pm.CurrentNum='CP907' THEN 'CP20190811'
WHEN p.state IN ('CA', 'CT','WY') AND pm.CurrentNum='CP635' THEN 'CP21250811'
WHEN p.state IN ('CA', 'CT','WY') AND pm.CurrentNum='CP801' THEN 'CP21020811'
WHEN p.state IN ('CA', 'CT','WY') AND pm.CurrentNum='CP802' THEN 'CP21050811'
WHEN p.state IN ('CA', 'CT','WY') AND pm.CurrentNum='CP1077' THEN 'CP20810811'
WHEN p.state IN ('CA', 'CT','WY') AND pm.CurrentNum='CP1075' THEN 'CP20780811' ELSE 'Daisy' END = cp.Packagecode
WHERE ap.ApplianceID NOT IN(286,8,9,71,188,257,319,321,322,354,358,386,397,398,399,
403,405,416,457,501,504,522,528,552,556,557,604,615,632) AND (vp.CancelDate IS NULL OR vp.CancelDate >= '08/20/2012')
AND p.ProducerStatus='A'
AND vp.PackageStat='A'
November 1, 2012 at 11:43 am
I don't see anything wrong with this syntactically. What do you mean by "it's just not picking up by looking at p.State"? One thing you might do to help you debugging this is to add that case expression as a column in the select. That would let you see what it is evaluating to.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 1, 2012 at 11:56 am
Sean Lange (11/1/2012)
I don't see anything wrong with this syntactically. What do you mean by "it's just not picking up by looking at p.State"? One thing you might do to help you debugging this is to add that case expression as a column in the select. That would let you see what it is evaluating to.
What I mean is that the case statement looks for a specific state and if it has the corresponding pm.CurrentNum than I want it to add the other code. Example after I run the query the state 'NH' with pm.CurrentNum='CP294' is not entering CP20960811. It's almost as if the script isn't acknowledging the CASE statement is there.
November 1, 2012 at 12:10 pm
igloo21 (11/1/2012)
Sean Lange (11/1/2012)
I don't see anything wrong with this syntactically. What do you mean by "it's just not picking up by looking at p.State"? One thing you might do to help you debugging this is to add that case expression as a column in the select. That would let you see what it is evaluating to.What I mean is that the case statement looks for a specific state and if it has the corresponding pm.CurrentNum than I want it to add the other code. Example after I run the query the state 'NH' with pm.CurrentNum='CP294' is not entering CP20960811. It's almost as if the script isn't acknowledging the CASE statement is there.
I can assure you that sql did not ignore your case expression. Did you try adding the case as a column in your select? What is returned in that column?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 1, 2012 at 12:57 pm
quote]
I can assure you that sql did not ignore your case expression. Did you try adding the case as a column in your select? What is returned in that column?
[/quote]
Not sure ho how to do that. This is the Newbie section 😀
November 1, 2012 at 1:28 pm
igloo21 (11/1/2012)
I can assure you that sql did not ignore your case expression. Did you try adding the case as a column in your select? What is returned in that column?
Not sure ho how to do that. This is the Newbie section 😀
Copy-and-paste the Case statement into the Select clause, right after vp.RateLookupStateID.
Like this:
WITH ApplianceMapping
AS (SELECT cp.PackageID AS CurPackageID,
gm.CurrentNum,
cp.PackageID AS CurPackageID,
ap1.AppliancePackageID AS CurAppliancePackageID,
cp2.PackageID AS NewPackageID,
gm.NewNum,
cp2.PackageID AS NewPackageID,
ap2.AppliancePackageID AS NewAppliancePackageID
FROM dbo.tblPackagePackages cp
INNER JOIN Data_User.dbo.SCR32069JCW_GapMapping gm
ON cp.PackageCode = gm.CurrentNum
INNER JOIN dbo.tblPackagePackages cp2
ON gm.NewNum = cp2.PackageCode
AND cp.PackageID = cp2.PackageID
INNER JOIN dbo.tblAppliancePackages ap1
ON cp.PackageID = ap1.PackageID
AND ap1.ApplianceID NOT IN (286, 8, 9, 71, 188, 257, 319, 321, 322, 354, 358, 386, 397,
398, 399, 403, 405, 416, 457, 501, 504, 522, 528, 552, 556,
557, 604, 615, 632)
INNER JOIN dbo.tblAppliancePackages ap2
ON cp2.PackageID = ap2.PackageID
AND ap2.ApplianceID NOT IN (286, 8, 9, 71, 188, 257, 319, 321, 322, 354, 358, 386, 397,
398, 399, 403, 405, 416, 457, 501, 504, 522, 528, 552, 556,
557, 604, 615, 632))
SELECT DISTINCT
p.state,
pm.CurrentNum,
pm.NewNum,
pm.NewPackageID,
pm.NewAppliancePackageID,
vp.ProducerID,
vp.Rep1,
vp.PackageStat,
vp.PackageSplit,
vp.PolicyType,
vp.HouseSplit,
vp.SalesSplit,
vp.EscrowSplit,
vp.DealerRemit,
vp.ApplianceCom,
'This is a test' AS Comment,
vp.ContractDate,
NULL AS CancelDate,
vp.MasterPolicy,
NULL AS ModifiedBy,
vp.Location,
vp.WebPackage,
GETDATE() AS CreatedOn,
NULL AS ModifiedOn,
'I.T.' AS CreatedBy,
'11/01/2012' AS EffectiveDate,
vp.RateLookupID,
vp.PriValidPackageID,
MIN(vp.ValidPackageID) AS PriorValidPackageID,
vp.ImportID,
vp.RateLookupStateID,
CASE WHEN p.state = 'NH'
AND pm.CurrentNum = 'CP294' THEN 'CP20960811'
WHEN p.state = 'CO'
AND pm.CurrentNum = 'CP307' THEN 'CP20970811'
WHEN p.state = 'NH'
AND pm.CurrentNum = 'CP307' THEN 'CP20980811'
WHEN p.state = 'NH'
AND pm.CurrentNum = 'CP308' THEN 'CP23020811'
WHEN p.state = 'NH'
AND pm.CurrentNum = 'CP310' THEN 'CP23020811'
WHEN p.state = 'NH'
AND pm.CurrentNum = 'CP582' THEN 'CP20090811'
WHEN p.state = 'NH'
AND pm.CurrentNum = 'CP728' THEN 'CP20130811'
WHEN p.state = 'NH'
AND pm.CurrentNum = 'CP907' THEN 'CP20200811'
WHEN p.state = 'NH'
AND pm.CurrentNum = 'CP802' THEN 'CP21040811'
WHEN p.state IN ('CA', 'CT', 'WY')
AND pm.CurrentNum = 'CP907' THEN 'CP20190811'
WHEN p.state IN ('CA', 'CT', 'WY')
AND pm.CurrentNum = 'CP635' THEN 'CP21250811'
WHEN p.state IN ('CA', 'CT', 'WY')
AND pm.CurrentNum = 'CP801' THEN 'CP21020811'
WHEN p.state IN ('CA', 'CT', 'WY')
AND pm.CurrentNum = 'CP802' THEN 'CP21050811'
WHEN p.state IN ('CA', 'CT', 'WY')
AND pm.CurrentNum = 'CP1077' THEN 'CP20810811'
WHEN p.state IN ('CA', 'CT', 'WY')
AND pm.CurrentNum = 'CP1075' THEN 'CP20780811'
ELSE 'Daisy'
END
FROM dbo.tblValidPackages vp
INNER JOIN dbo.tblAppliancePackages ap
ON vp.AppliancePackageID = ap.AppliancePackageID
AND ap.ApplianceID NOT IN (286, 8, 9, 71, 188, 257, 319, 321, 322, 354, 358, 386, 397, 398, 399, 403,
405, 416, 457, 501, 504, 522, 528, 552, 556, 557, 604, 615, 632)
INNER JOIN dbo.tblCustomers P
ON vp.ProducerID = P.ProducerID
INNER JOIN ApplianceMapping pm
ON vp.PackageID = pm.CurPackageID
LEFT OUTER JOIN dbo.tblPackagePackages cp
ON CASE WHEN p.state = 'NH'
AND pm.CurrentNum = 'CP294' THEN 'CP20960811'
WHEN p.state = 'CO'
AND pm.CurrentNum = 'CP307' THEN 'CP20970811'
WHEN p.state = 'NH'
AND pm.CurrentNum = 'CP307' THEN 'CP20980811'
WHEN p.state = 'NH'
AND pm.CurrentNum = 'CP308' THEN 'CP23020811'
WHEN p.state = 'NH'
AND pm.CurrentNum = 'CP310' THEN 'CP23020811'
WHEN p.state = 'NH'
AND pm.CurrentNum = 'CP582' THEN 'CP20090811'
WHEN p.state = 'NH'
AND pm.CurrentNum = 'CP728' THEN 'CP20130811'
WHEN p.state = 'NH'
AND pm.CurrentNum = 'CP907' THEN 'CP20200811'
WHEN p.state = 'NH'
AND pm.CurrentNum = 'CP802' THEN 'CP21040811'
WHEN p.state IN ('CA', 'CT', 'WY')
AND pm.CurrentNum = 'CP907' THEN 'CP20190811'
WHEN p.state IN ('CA', 'CT', 'WY')
AND pm.CurrentNum = 'CP635' THEN 'CP21250811'
WHEN p.state IN ('CA', 'CT', 'WY')
AND pm.CurrentNum = 'CP801' THEN 'CP21020811'
WHEN p.state IN ('CA', 'CT', 'WY')
AND pm.CurrentNum = 'CP802' THEN 'CP21050811'
WHEN p.state IN ('CA', 'CT', 'WY')
AND pm.CurrentNum = 'CP1077' THEN 'CP20810811'
WHEN p.state IN ('CA', 'CT', 'WY')
AND pm.CurrentNum = 'CP1075' THEN 'CP20780811'
ELSE 'Daisy'
END = cp.Packagecode
WHERE ap.ApplianceID NOT IN (286, 8, 9, 71, 188, 257, 319, 321, 322, 354, 358, 386, 397, 398, 399, 403, 405, 416,
457, 501, 504, 522, 528, 552, 556, 557, 604, 615, 632)
AND (vp.CancelDate IS NULL
OR vp.CancelDate >= '08/20/2012')
AND p.ProducerStatus = 'A'
AND vp.PackageStat = 'A';
Also, keep in mind that the Case statement might appear to not be working, because of the Left Join to the table it compares values to. You could be getting a perfectly valid Case value, but no matching value in the cp.Packagecode column. Left Join would still return rows for that, just with null in the columns pulled from dbo.tblPackagePackages (it doesn't actually appear to use any columns from that table anywhere in the query - why is that table in there? - or am I just overlooking it?).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 1, 2012 at 2:40 pm
igloo,
I generally try to avoid using case statements in joins. Instead, I prefer to build the join criteria as a series of OR conditions. Try the following construction in place of the case statement:
INNER JOIN dbo.tblPackagePackages cp ON
(
(cp.Packagecode='CP20960811' AND p.state='NH' AND pm.CurrentNum='CP294')
OR
(cp.Packagecode='CP20970811' AND p.state='CO' AND pm.CurrentNum='CP307')
.
.
.
OR
(cp.Packagecode='CP20780811' AND p.state IN ('CA', 'CT','WY') AND pm.CurrentNum='CP1075')
OR
(cp.Packagecode='DAISY')
)
You will probably need to modify the last OR that picks up the default 'DAISY' value, so that it will only use that value when the other criteria is not met.
-gjr
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply