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

Cascading Security.

Hi,
I am looking for a way to implement the following security schema in SQL
2005.
Database 1 contains the Raw Data
Data base 2 contains views On the data in Database 1.
We need to create a user who can access in read mode the views in Database
2, but should not be allowed to explore through ODBC connection teh tables
and data in Database 1.
Is there a method to implement this?
thanks in AdvanceZrod
Do the users own the objects?
GRANT only SELECT on Views to the user in db2. DENY VIEW DEDINITION (for
details please refer to the BOL) on db1
"Zrod" <zrod@.aims-co.com> wrote in message
news:ubCuwCEVHHA.4076@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I am looking for a way to implement the following security schema in SQL
> 2005.
> Database 1 contains the Raw Data
> Data base 2 contains views On the data in Database 1.
> We need to create a user who can access in read mode the views in Database
> 2, but should not be allowed to explore through ODBC connection teh tables
> and data in Database 1.
> Is there a method to implement this?
> thanks in Advance
>
>

Cascade Delete

Hi,
I need to delete data from child table when I delete data from parent
table. I think trigger can be used in this scenario.
I want to know your views on using trigger for this purpose.
And can someone give me a sample on how to do it using triggers.
Thanks
Kiranor define your foreign key to cascade on delete. No trigger required
"Kiran" <kiran@.nospam.net> wrote in message
news:ussCTgMqFHA.3520@.tk2msftngp13.phx.gbl...
> Hi,
> I need to delete data from child table when I delete data from parent
> table. I think trigger can be used in this scenario.
> I want to know your views on using trigger for this purpose.
> And can someone give me a sample on how to do it using triggers.
> Thanks
> Kiran|||You can "automate" deletes using ON DELETE CASCADE when creating/altering
foreign key constraints.
Read more here:
http://msdn.microsoft.com/library/d...br />
3ied.asp
Example:
alter table owner.foreignkey_table
add constraint fk_name
foreign key (foreignkey_column_name)
references owner.primarykey_table
(primarykey_column_name)
on delete cascade
go
ML|||You can accomplish this in SQL Server 2000 without a trigger, using ON
DELETE CASCADE when creating FOREIGN KEY constraints.
The following script illustrates this:
use tempdb
go
set nocount on
go
create table tbl1 (id int not null primary key)
go
insert tbl1 values (1)
insert tbl1 values (2)
go
create table tbl2
(
id int not null primary key
references tbl1 (id) on delete cascade
)
go
insert tbl2 values (1)
insert tbl2 values (2)
go
delete from tbl1
where id = 1
go
select *
from tbl2
go
drop table tbl2
drop table tbl1
go
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Kiran" <kiran@.nospam.net> wrote in message
news:ussCTgMqFHA.3520@.tk2msftngp13.phx.gbl...
> Hi,
> I need to delete data from child table when I delete data from parent
> table. I think trigger can be used in this scenario.
> I want to know your views on using trigger for this purpose.
> And can someone give me a sample on how to do it using triggers.
> Thanks
> Kiran|||ML wrote:
> You can "automate" deletes using ON DELETE CASCADE when creating/altering
> foreign key constraints.
> Read more here:
> http://msdn.microsoft.com/library/d... />
z_3ied.asp
> Example:
> alter table owner.foreignkey_table
> add constraint fk_name
> foreign key (foreignkey_column_name)
> references owner.primarykey_table
> (primarykey_column_name)
> on delete cascade
> go
>
> ML
Thanks ML|||Adam Machanic wrote:
> You can accomplish this in SQL Server 2000 without a trigger, using ON
> DELETE CASCADE when creating FOREIGN KEY constraints.
> The following script illustrates this:
>
> use tempdb
> go
> set nocount on
> go
> create table tbl1 (id int not null primary key)
> go
> insert tbl1 values (1)
> insert tbl1 values (2)
> go
> create table tbl2
> (
> id int not null primary key
> references tbl1 (id) on delete cascade
> )
> go
> insert tbl2 values (1)
> insert tbl2 values (2)
> go
> delete from tbl1
> where id = 1
> go
> select *
> from tbl2
> go
> drop table tbl2
> drop table tbl1
> go
>
>
Thanks Adam|||Farmer wrote:
> or define your foreign key to cascade on delete. No trigger required
>
> "Kiran" <kiran@.nospam.net> wrote in message
> news:ussCTgMqFHA.3520@.tk2msftngp13.phx.gbl...
>
>
>
thanks Farmer