July 6, 2012 at 3:45 am
Hi,
I have the following query
select Pay.DetailId, clm.AssetId,
case when Pay.DetailId IS null then isnull(clm.NoteIds,0) else isnull(pay.NoteIds,0) end as NoteIds,
case when Pay.DetailId is null then isnull(clm.TagId,0) else isnull(pay.TagId,0) end as TagId from CL_Costco clm left outer join tblstorenabpmap tblstore on clm.nabpno=tblstore.nabpnumber
left outer join PA_Costco Pay on clm.AssetId=Pay.AssetId left outer join OB_Costco ob on clm.ObligorId =ob.ObligorId or Pay.ObligorId=ob.ObligorId
where clm.AmountBilled <> 0 and (Pay.claimmatchflag is null or Pay.claimmatchflag='Y')
and (Pay.TagId is not null and clm.TagId is not null)
and (pay.tagid in (16,24,15,20,23,25) or clm.tagid in (16,24,15,20,23,25))
in which i need to case in following condition
and (Pay.TagId is not null and clm.TagId is not null)
and (pay.tagid in (16,24,15,20,23,25) or clm.tagid in (16,24,15,20,23,25))
Is it possible??
July 6, 2012 at 4:12 am
Hi yes it is possible to have a case statement in a where clause
***The first step is always the hardest *******
July 6, 2012 at 4:18 am
If you dont want to use the case in where condition you can consider using Common Table Expression and then use your new columns in where condition in another select statment.
July 6, 2012 at 4:23 am
Hi,
I used case as below but im getting error
select Pay.DetailId, clm.AssetId,
case when Pay.DetailId IS null then isnull(clm.NoteIds,0) else isnull(pay.NoteIds,0) end as NoteIds,
case when Pay.DetailId is null then isnull(clm.TagId,0) else isnull(pay.TagId,0) end as TagId from CL_Costco clm left outer join tblstorenabpmap tblstore on clm.nabpno=tblstore.nabpnumber
left outer join PA_Costco Pay on clm.AssetId=Pay.AssetId left outer join OB_Costco ob on clm.ObligorId =ob.ObligorId
or Pay.ObligorId=ob.ObligorId
where clm.AmountBilled <> 0 and (Pay.claimmatchflag is null or Pay.claimmatchflag='Y')and
15 = (
case when Pay.DetailId is null then clm.TagId in (16,24,15,20,23,25)
else Pay.TagId in (16,24,15,20,23,25) end)
July 6, 2012 at 4:24 am
abdul.badru (7/6/2012)
If you dont want to use the case in where condition you can consider using Common Table Expression and then use your new columns in where condition in another select statment.
As Abdul says, use a CTE will be more efficient too
***The first step is always the hardest *******
July 6, 2012 at 4:26 am
Whats you error? and you cant use a case statment to procude a IN ( val,val2) clause...
can you post DDL information please and we can look at this for you
***The first step is always the hardest *******
July 6, 2012 at 4:51 am
muthuselvi.devarajan (7/6/2012)
Hi,I used case as below but im getting error
select Pay.DetailId, clm.AssetId,
case when Pay.DetailId IS null then isnull(clm.NoteIds,0) else isnull(pay.NoteIds,0) end as NoteIds,
case when Pay.DetailId is null then isnull(clm.TagId,0) else isnull(pay.TagId,0) end as TagId from CL_Costco clm left outer join tblstorenabpmap tblstore on clm.nabpno=tblstore.nabpnumber
left outer join PA_Costco Pay on clm.AssetId=Pay.AssetId left outer join OB_Costco ob on clm.ObligorId =ob.ObligorId
or Pay.ObligorId=ob.ObligorId
where clm.AmountBilled <> 0 and (Pay.claimmatchflag is null or Pay.claimmatchflag='Y')and
15 = (
case when Pay.DetailId is null then clm.TagId in (16,24,15,20,23,25)
else Pay.TagId in (16,24,15,20,23,25) end)
In your WHERE clause, you are testing to see if the result of an expression is equal to 15. The expression makes no sense to SQL Server, and makes no sense to me either. What are you trying to do?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 6, 2012 at 5:58 am
CASE statements cannot be used in the manner you have used
I think this is what you are trying to achieve
SELECTPay.DetailId, clm.AssetId,
CASE WHEN Pay.DetailId IS NULL THEN ISNULL(clm.NoteIds,0) ELSE ISNULL(pay.NoteIds,0) END AS NoteIds,
CASE WHEN Pay.DetailId IS NULL THEN ISNULL(clm.TagId,0) ELSE ISNULL(pay.TagId,0) END AS TagId
FROMCL_Costco clm
LEFT OUTER JOIN tblstorenabpmap tblstore ON clm.nabpno = tblstore.nabpnumber
LEFT OUTER JOIN PA_Costco Pay ON clm.AssetId = Pay.AssetId
LEFT OUTER JOIN OB_Costco ob ON clm.ObligorId = ob.ObligorId OR Pay.ObligorId = ob.ObligorId
WHEREclm.AmountBilled <> 0
AND( Pay.claimmatchflag IS NULL OR Pay.claimmatchflag = 'Y')
-- The CASE statement should be like this
AND1 = CASE
WHEN Pay.DetailId IS NULL AND clm.TagId IN (16,24,15,20,23,25) THEN 1
WHEN Pay.DetailId IS NOT NULL AND Pay.TagId in (16,24,15,20,23,25) THEN 1
ELSE 0
END
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 6, 2012 at 6:09 am
You cannot use the keyword IN on the "result" side of a CASE statement, only on the conditional side of the statement.
CASE means "please check"
WHEN means "these conditions"
THEN means "this is the result if the conditions are true"
THEN can only have 1 value. It cannot have multiple values. It can, however, use the different values of existing records if those values are recorded in a single column in a table.
For example:
CREATE TABLE #MyTemp (MyID INT IDENTITY(1,1) NOT NULL, FirstName VARCHAR(25) NULL, LastName VARCHAR(25) NULL);
GO
INSERT INTO #MYTemp (FirstName, LastName)
VALUES ('Mickey','Mouse'),('Donald','Duck'),
('Daisy','Duck'), ('SQL','Quack'),
('Scooby','Doo'), ('Velma','Dinkley');
GO
SELECT LastName, FirstName, CASE LastName WHEN 'Duck' THEN 'It Quacks' ELSE FirstName END
FROM #MyTemp;
--Uses single value of LastName as the only condition
SELECT LastName, FirstName, CASE WHEN LastName IN ('Duck', 'Quack') THEN 'It Quacks'
ELSE FirstName END
FROM #MyTemp;
--Uses Multiple values of LastName as the conditions
DROP TABLE #MyTemp;
This may not be the best example, but it's the only one my tired brain can come up with right now.
EDIT: The same principles do apply to the WHERE clause, but in that situation, you're comparing the entire CASE statement to something on the other side of the equals sign.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply