April 3, 2015 at 12:21 am
Hi all,
I need a help to solve following query....
select pub.Publication,
CASE WHEN ed.Edition='ALL' THEN 'Y'
ELSE ed.Edition
END AS Edition
FROM PublicationEditions pe
INNER JOIN Publications pub ON pub.PublicationId=pe.PublicationId
INNER JOIN Editions ed ON ed.EditionId=pe.EditionId
GROUP BY pub.Publication,ed.Edition
In above query when Edition is All I need to display Editions Except All so i need to write select statement after Then clause,I have tried but not getting it.
I have tried with following Query but still not get the result...
select pub.Publication,
CASE WHEN ed.Edition='ALL' THEN ed.Edition<>'ALL'
ELSE ed.Edition
END AS Edition
FROM PublicationEditions pe
INNER JOIN Publications pub ON pub.PublicationId=pe.PublicationId
INNER JOIN Editions ed ON ed.EditionId=pe.EditionId
GROUP BY pub.Publication,ed.Edition
KINDLY REPLY...
Thanx and Regards
Shirish Phadnis
April 3, 2015 at 6:07 am
shirish1987 (4/3/2015)
Hi all,I need a help to solve following query....
select pub.Publication,CASE WHEN ed.Edition='ALL' THEN 'Y'
ELSE ed.Edition
END AS Edition
FROM PublicationEditions pe
INNER JOIN Publications pub ON pub.PublicationId=pe.PublicationId
INNER JOIN Editions ed ON ed.EditionId=pe.EditionId
GROUP BY pub.Publication,ed.Edition
In above query when Edition is All I need to display Editions Except All so i need to write select statement after Then clause,I have tried but not getting it.
I have tried with following Query but still not get the result...
select pub.Publication,CASE WHEN ed.Edition='ALL' THEN ed.Edition<>'ALL'
ELSE ed.Edition
END AS Edition
FROM PublicationEditions pe
INNER JOIN Publications pub ON pub.PublicationId=pe.PublicationId
INNER JOIN Editions ed ON ed.EditionId=pe.EditionId
GROUP BY pub.Publication,ed.Edition
KINDLY REPLY...
Thanx and Regards
Shirish Phadnis
You cannot use criteria in the then like that. You would need another CASE, however I think what you want is a WHERE criteria on the query:
select pub.Publication, ed.Edition AS Edition
FROM PublicationEditions pe
INNER JOIN Publications pub ON pub.PublicationId=pe.PublicationId
INNER JOIN Editions ed ON ed.EditionId=pe.EditionId
WHERE ed.Edition <> 'ALL'
GROUP BY pub.Publication,ed.Edition
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply