Case statement on existing case

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Case statement on existing case

Author

Topic

vsempoux
Starting Member

5 Posts

Posted - 2008-08-01 : 04:15:49

Hello all,

I have the following query:

select '', ca0.nrid ,  ca0.ref as CampaignId, ca0.var1, ca0.var2, ca0.var3,
	ca0.var6, ca0.var12, var19, ca0.dat1, ca0.begin_date,
	(select case count(ea.advert_id) when 1 then 'X' end from sysadm.extadvert ea where ea.ca0_nrid=ca0.nrid and ea.advert_lang='nl-BE') as 'NL',
	(select case count(ea.advert_id) when 1 then 'X' end from sysadm.extadvert ea where ea.ca0_nrid=ca0.nrid and ea.advert_lang='fr-BE') as 'FR',
                (select case count(ea.advert_id) when 1 then 'X' end from sysadm.extadvert ea where ea.ca0_nrid=ca0.nrid and ea.advert_lang='en-GB') as 'EN'from sysadm.ca0 ca0 where 
(select count(am6.ap01_name) from sysadm.am6 am6 right join sysadm.am0 am0 on am0.nrid=am6.am0_nrid 
where am6.ap00_name='Languages' and am6.template IS NULL and am0.titulaire = :strUserNom  
and ap01_name in (select advert_lang from sysadm.extadvert ea where ea.ca0_nrid=ca0.nrid)) 
=
(select count(ea.advert_lang) from sysadm.extadvert ea where ea.ca0_nrid=ca0.nrid)
and ca0.type='SEM' and ca0.var12='1 - New' order by ca0.dat1, ca0.ref

The section in red is putting an X in my column if the language exists in a campaign.
We use this query for different country's and now they have asked me if it's possible to use this query for each country,
based on the two last digits of the ea.advert_lang field.
If it is BE or GB, the section in red should remain, if it is something else it should not be shown (because the other countries have only one language specification)

I think i must place another case on top of the red section, but i'm not sure how (because of the choice of the last two digits
in the language).

Any hints, pointers ..
Thanks alot already

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-01 : 12:45:12

(select case when right(ea.advert_lang,2) ='BE' or right(ea.advert_lang,2) ='GB' then case count(ea.advert_id) when 1 then 'X' end end from sysadm.extadvert ea where ea.ca0_nrid=ca0.nrid and ea.advert_lang='nl-BE') as 'NL'

similarly the other one..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources