CASE in a VIEW

Hello,
I've have a query that I want to convert into a view... but it contains
a CASE statement. Views will not accept CASE and I really need to use a
case statement here because of the way the tables are structured. Am I
doomed to creating a new (very messy) query that will suffice in a view,
do I have to use a stored procedure or is there an elegant way to
resolve this problem?
Thanks in advance,
Craig H.
select le.Name as Client, lec.Name as CompanyType, div.name as Division,
p.Name as PName,
case
when apc.paymentrouting_id IS NOT NULL THEN 'A'
when bpc.paymentrouting_id IS NOT NULL THEN 'B'
when cpc.paymentrouting_id IS NOT NULL THEN 'C'
when cpc.paymentrouting_id IS NOT NULL THEN 'D'
else 'UNKNOWN'
end as ServiceType
into #temptable
from PROD.dbo.LegalEntity le
inner join PROD.dbo.contract c
on ((c.contracting_legalentity_id = le.legalentity_id) and (c.enddate IS
NULL or c.enddate > getdate()))
inner join PROD.dbo.LegalEntity div
on div.legalEntity_ID = c.providing_legalentity_id
inner join PROD.dbo.LegalEntityCode lec
on lec.LegalEntityCode = le.LegalEntityCode
inner join PROD.dbo.payee p
on ((p.contract_id = c.contract_id) and (p.active = 'true'))
inner join PROD.dbo.paymentrouting pr
on pr.payee_id = p.payee_id
left join PROD.dbo.APaymentChannel apc
on ((apc.paymentrouting_id = pr.paymentrouting_id) and (apc.active =
'true'))
left join PROD.dbo.BPaymentChannel bpc
on ((bpc.paymentrouting_id = pr.paymentrouting_id) and (bpc.active =
'true'))
left join PROD.dbo.CPaymentChannel cpc
on ((cpc.paymentrouting_id = pr.paymentrouting_id) and (cpc.active =
'true'))
left join PROD.dbo.DPaymentChannel dpc
on ((dpc.paymentrouting_id = pr.paymentrouting_id) and (dpc.active =
'true'))
go
select tt.client, tt.CompanyType, tt.Division, tt.PName, tt.ServiceType
from #temptable tt
group by tt.client, tt.CompanyType, tt.Division, tt.PName, tt.ServiceType
order by tt.client
go
drop table #temptable
go
"The power of accurate observation is frequently called cynicism by
those who don't have it."CASE statements are supported in views. You can't create them using the
Query Designer in Enterprise Manager, as that has limitations on what is
allowed in a view, but if you create the view in Query Analyzer is should
all work fine.
Jacco Schalkwijk
SQL Server MVP
"Craig H." <spam@.thehurley.com> wrote in message
news:OPzJy5pJFHA.2356@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I've have a query that I want to convert into a view... but it contains a
> CASE statement. Views will not accept CASE and I really need to use a
> case statement here because of the way the tables are structured. Am I
> doomed to creating a new (very messy) query that will suffice in a view,
> do I have to use a stored procedure or is there an elegant way to resolve
> this problem?
> Thanks in advance,
> Craig H.
>
> select le.Name as Client, lec.Name as CompanyType, div.name as Division,
> p.Name as PName,
> case
> when apc.paymentrouting_id IS NOT NULL THEN 'A'
> when bpc.paymentrouting_id IS NOT NULL THEN 'B'
> when cpc.paymentrouting_id IS NOT NULL THEN 'C'
> when cpc.paymentrouting_id IS NOT NULL THEN 'D'
> else 'UNKNOWN'
> end as ServiceType
> into #temptable
> from PROD.dbo.LegalEntity le
> inner join PROD.dbo.contract c
> on ((c.contracting_legalentity_id = le.legalentity_id) and (c.enddate IS
> NULL or c.enddate > getdate()))
> inner join PROD.dbo.LegalEntity div
> on div.legalEntity_ID = c.providing_legalentity_id
> inner join PROD.dbo.LegalEntityCode lec
> on lec.LegalEntityCode = le.LegalEntityCode
> inner join PROD.dbo.payee p
> on ((p.contract_id = c.contract_id) and (p.active = 'true'))
> inner join PROD.dbo.paymentrouting pr
> on pr.payee_id = p.payee_id
> left join PROD.dbo.APaymentChannel apc
> on ((apc.paymentrouting_id = pr.paymentrouting_id) and (apc.active =
> 'true'))
> left join PROD.dbo.BPaymentChannel bpc
> on ((bpc.paymentrouting_id = pr.paymentrouting_id) and (bpc.active =
> 'true'))
> left join PROD.dbo.CPaymentChannel cpc
> on ((cpc.paymentrouting_id = pr.paymentrouting_id) and (cpc.active =
> 'true'))
> left join PROD.dbo.DPaymentChannel dpc
> on ((dpc.paymentrouting_id = pr.paymentrouting_id) and (dpc.active =
> 'true'))
> go
> select tt.client, tt.CompanyType, tt.Division, tt.PName, tt.ServiceType
> from #temptable tt
> group by tt.client, tt.CompanyType, tt.Division, tt.PName, tt.ServiceType
> order by tt.client
> go
> drop table #temptable
> go
>
> --
> "The power of accurate observation is frequently called cynicism by those
> who don't have it."|||"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23obv4FqJFHA.3084@.TK2MSFTNGP10.phx.gbl...
> CASE statements are supported in views. You can't create them using the
> Query Designer in Enterprise Manager, as that has limitations on what is
> allowed in a view, but if you create the view in Query Analyzer is should
> all work fine.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Craig H." <spam@.thehurley.com> wrote in message
> news:OPzJy5pJFHA.2356@.TK2MSFTNGP14.phx.gbl...
>
Jacco is correct, it's a limitation of EM, not SQL Server itself (assuming
you're running service pack 3). Here's a proof of concept that obviates the
need for a CASE statement by making use of NULL propagation, in case you're
interested. You'll want to check the setting of CONCAT_NULL_YIELDS_NULL and
make sure it's on. I believe that's the default.
DECLARE @.a INT, @.b INT, @.c INT, @.d INT
SELECT @.a = NULL, @.b = 2, @.c = NULL, @.d = 4
SELECT COALESCE(
LEFT('A' + CAST(@.a AS VARCHAR),1),
LEFT('B' + CAST(@.b AS VARCHAR),1),
LEFT('C' + CAST(@.c AS VARCHAR),1),
LEFT('D' + CAST(@.d AS VARCHAR),1),
'UNKNOWN'
)
-Chris Hohmann