Query help.... with CASE

14 Sep 2006 12:21 PM

DL account

--DDL
create table systems
(
sysid int primary key,
sysnme varchar(30) not null,
sysuser varchar(40) null,
)
create table software
(
swid int primary key,
sysid int references systems(sysid),
cmpny varchar(30),
prod varchar(30)
)
go

--Data inserts
insert systems values(1,'sys1','user1')
insert systems values(2,'sys2','user2')
insert systems values(3,'sys3','user3')
insert systems values(4,'sys4','user4')
insert systems values(5,'sys5','user5')
go
insert software values(1,1,'Microsoft','Office 2003')
insert software values(2,1,'Microsoft software','Office imaging')
insert software values(3,1,'Microsoft corp','Office 2003')
insert software values(4,1,'Microsoft veritas','Office backup')

insert software values(10,1,'Microsoft Corporation','Office xp')
insert software values(11,1,'Microsoft','Office xp live')
insert software values(12,1,'Microsoft Corp','Office xp systems')
insert software values(13,1,'Microsoft','Office 2000')

insert software values(5,2,'Adobe','acrobat reader')
insert software values(6,2,'Adobe inc','acrobat reader live')
insert software values(7,2,'Adobe','acrobat writer')
insert software values(8,2,'Adobe','acrobat registry')
insert software values(9,2,'Adobe','acrobat')

--Query that I have tried
select sys.sysnme AS 'System name',sys.sysuser AS 'System User',
case
      when soft.prod like '%office%2003%' then 'Office 2003'
      when soft.prod like '%office%xp%' then 'Office XP'
      when soft.prod like '%office%2000%' then 'Office 2000'
end as 'ms office',
case
      when soft.prod like '%acrobat%' then 'Acrobat'
end as 'acrobat'
from systems sys
join software soft ON sys.sysid = soft.sysid

--Question
I need to display those systems that have products - 'Office 2003', or
'Office XP' or 'Office 2000' or 'Acrobat' . From the above inserts, thus
'Office imaging' and 'Office backup' do not qualify. That means, out of
total 13 rows, only 11 should be displayed. But my query above gives 13 with
two rows having NULL values for both "ms office" and "acrobat". I dont want
that -either one should be NULL. Can someone help me with the correct query?

Thanks!

14 Sep 2006 12:26 PM

Hari Prasad

Hello,

You are just controlling the SELECT part, so display will be good for all
the conditons handled for others NULL will come.
To restrict that you will have to add the same in WHERE clause. Just see the
below code.

select sys.sysnme AS 'System name',sys.sysuser AS 'System User',
case
      when soft.prod like '%office%2003%' then 'Office 2003'
      when soft.prod like '%office%xp%' then 'Office XP'
      when soft.prod like '%office%2000%' then 'Office 2000'
end as 'ms office',
case
      when soft.prod like '%acrobat%' then 'Acrobat'
end as 'acrobat'
from systems sys
join software soft ON sys.sysid = soft.sysid
where (soft.prod like '%office%2003%' or soft.prod like '%office%xp%'or
soft.prod like '%office%2000%' or soft.prod like '%acrobat%' )

Thanks
Hari
SQL Server MVP

Show quote

"DL account" <techiedl@nospam.gmail.com> wrote in message
news:uDtx6g$1GHA.1568@TK2MSFTNGP03.phx.gbl...
> --DDL
> create table systems
> (
> sysid int primary key,
> sysnme varchar(30) not null,
> sysuser varchar(40) null,
> )
> create table software
> (
> swid int primary key,
> sysid int references systems(sysid),
> cmpny varchar(30),
> prod varchar(30)
> )
> go
>
> --Data inserts
> insert systems values(1,'sys1','user1')
> insert systems values(2,'sys2','user2')
> insert systems values(3,'sys3','user3')
> insert systems values(4,'sys4','user4')
> insert systems values(5,'sys5','user5')
> go
> insert software values(1,1,'Microsoft','Office 2003')
> insert software values(2,1,'Microsoft software','Office imaging')
> insert software values(3,1,'Microsoft corp','Office 2003')
> insert software values(4,1,'Microsoft veritas','Office backup')
>
> insert software values(10,1,'Microsoft Corporation','Office xp')
> insert software values(11,1,'Microsoft','Office xp live')
> insert software values(12,1,'Microsoft Corp','Office xp systems')
> insert software values(13,1,'Microsoft','Office 2000')
>
> insert software values(5,2,'Adobe','acrobat reader')
> insert software values(6,2,'Adobe inc','acrobat reader live')
> insert software values(7,2,'Adobe','acrobat writer')
> insert software values(8,2,'Adobe','acrobat registry')
> insert software values(9,2,'Adobe','acrobat')
>
>
> --Query that I have tried
> select sys.sysnme AS 'System name',sys.sysuser AS 'System User',
> case
>      when soft.prod like '%office%2003%' then 'Office 2003'
>      when soft.prod like '%office%xp%' then 'Office XP'
>      when soft.prod like '%office%2000%' then 'Office 2000'
> end as 'ms office',
> case
>      when soft.prod like '%acrobat%' then 'Acrobat'
> end as 'acrobat'
> from systems sys
> join software soft ON sys.sysid = soft.sysid
>
>
> --Question
> I need to display those systems that have products - 'Office 2003', or
> 'Office XP' or 'Office 2000' or 'Acrobat' . From the above inserts, thus
> 'Office imaging' and 'Office backup' do not qualify. That means, out of
> total 13 rows, only 11 should be displayed. But my query above gives 13
> with two rows having NULL values for both "ms office" and "acrobat". I
> dont want that -either one should be NULL. Can someone help me with the
> correct query?
>
>
> Thanks!
>
>
>
>
>
>
>
>

14 Sep 2006 12:31 PM

Jens

What about:

SELECT * FROM
(
select sys.sysnme AS 'System name',sys.sysuser AS 'System User',
case
      when soft.prod like '%office%2003%' then 'Office 2003'
      when soft.prod like '%office%xp%' then 'Office XP'
      when soft.prod like '%office%2000%' then 'Office 2000'
end as 'ms office',
case
      when soft.prod like '%acrobat%' then 'Acrobat'
end as 'acrobat'
from systems sys
join software soft ON sys.sysid = soft.sysid
) SUbQuery
Where NOT ([ms office] IS NULL AND acrobat IS NULL)

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

14 Sep 2006 12:34 PM

SQL Menace

select sys.sysnme AS 'System name',sys.sysuser AS 'System User',
case
      when soft.prod like '%office%2003%' then 'Office 2003'
      when soft.prod like '%office%xp%' then 'Office XP'
      when soft.prod like '%office%2000%' then 'Office 2000'
end as 'ms office',
case
      when soft.prod like '%acrobat%' then 'Acrobat'
end as 'acrobat'
from systems sys
join software soft ON sys.sysid = soft.sysid
and prod not in ('Office imaging','Office backup')

Denis the SQL Menace
http://sqlservercode.blogspot.com/

DL account wrote:

Show quote

> --DDL
> create table systems
> (
> sysid int primary key,
> sysnme varchar(30) not null,
> sysuser varchar(40) null,
> )
> create table software
> (
> swid int primary key,
> sysid int references systems(sysid),
> cmpny varchar(30),
> prod varchar(30)
> )
> go
>
> --Data inserts
> insert systems values(1,'sys1','user1')
> insert systems values(2,'sys2','user2')
> insert systems values(3,'sys3','user3')
> insert systems values(4,'sys4','user4')
> insert systems values(5,'sys5','user5')
> go
> insert software values(1,1,'Microsoft','Office 2003')
> insert software values(2,1,'Microsoft software','Office imaging')
> insert software values(3,1,'Microsoft corp','Office 2003')
> insert software values(4,1,'Microsoft veritas','Office backup')
>
> insert software values(10,1,'Microsoft Corporation','Office xp')
> insert software values(11,1,'Microsoft','Office xp live')
> insert software values(12,1,'Microsoft Corp','Office xp systems')
> insert software values(13,1,'Microsoft','Office 2000')
>
> insert software values(5,2,'Adobe','acrobat reader')
> insert software values(6,2,'Adobe inc','acrobat reader live')
> insert software values(7,2,'Adobe','acrobat writer')
> insert software values(8,2,'Adobe','acrobat registry')
> insert software values(9,2,'Adobe','acrobat')
>
>
> --Query that I have tried
> select sys.sysnme AS 'System name',sys.sysuser AS 'System User',
> case
>       when soft.prod like '%office%2003%' then 'Office 2003'
>       when soft.prod like '%office%xp%' then 'Office XP'
>       when soft.prod like '%office%2000%' then 'Office 2000'
> end as 'ms office',
> case
>       when soft.prod like '%acrobat%' then 'Acrobat'
> end as 'acrobat'
> from systems sys
> join software soft ON sys.sysid = soft.sysid
>
>
> --Question
> I need to display those systems that have products - 'Office 2003', or
> 'Office XP' or 'Office 2000' or 'Acrobat' . From the above inserts, thus
> 'Office imaging' and 'Office backup' do not qualify. That means, out of
> total 13 rows, only 11 should be displayed. But my query above gives 13 with
> two rows having NULL values for both "ms office" and "acrobat". I dont want
> that -either one should be NULL. Can someone help me with the correct query?
>
>
> Thanks!

14 Sep 2006 12:40 PM

Harsh Athalye

Like this:

select sys.sysnme AS 'System name',sys.sysuser AS 'System User',
case
      when soft.prod like '%office%2003%' then 'Office 2003'
      when soft.prod like '%office%xp%' then 'Office XP'
      when soft.prod like '%office%2000%' then 'Office 2000'
end as 'ms office',
case
      when soft.prod like '%acrobat%' then 'Acrobat'
end as 'acrobat'
from systems sys
join software soft ON sys.sysid = soft.sysid
where (soft.prod like 'Office 2003%' or soft.prod like 'Office xp%' or
soft.prod like 'Office 2000%' or soft.prod like 'Acrobat%')

--
Regards,
Harsh Athalye
"Nothing is impossible"

Show quote

"DL account" wrote:

> --DDL
> create table systems
> (
> sysid int primary key,
> sysnme varchar(30) not null,
> sysuser varchar(40) null,
> )
> create table software
> (
> swid int primary key,
> sysid int references systems(sysid),
> cmpny varchar(30),
> prod varchar(30)
> )
> go
>
> --Data inserts
> insert systems values(1,'sys1','user1')
> insert systems values(2,'sys2','user2')
> insert systems values(3,'sys3','user3')
> insert systems values(4,'sys4','user4')
> insert systems values(5,'sys5','user5')
> go
> insert software values(1,1,'Microsoft','Office 2003')
> insert software values(2,1,'Microsoft software','Office imaging')
> insert software values(3,1,'Microsoft corp','Office 2003')
> insert software values(4,1,'Microsoft veritas','Office backup')
>
> insert software values(10,1,'Microsoft Corporation','Office xp')
> insert software values(11,1,'Microsoft','Office xp live')
> insert software values(12,1,'Microsoft Corp','Office xp systems')
> insert software values(13,1,'Microsoft','Office 2000')
>
> insert software values(5,2,'Adobe','acrobat reader')
> insert software values(6,2,'Adobe inc','acrobat reader live')
> insert software values(7,2,'Adobe','acrobat writer')
> insert software values(8,2,'Adobe','acrobat registry')
> insert software values(9,2,'Adobe','acrobat')
>
>
> --Query that I have tried
> select sys.sysnme AS 'System name',sys.sysuser AS 'System User',
> case
>       when soft.prod like '%office%2003%' then 'Office 2003'
>       when soft.prod like '%office%xp%' then 'Office XP'
>       when soft.prod like '%office%2000%' then 'Office 2000'
> end as 'ms office',
> case
>       when soft.prod like '%acrobat%' then 'Acrobat'
> end as 'acrobat'
> from systems sys
> join software soft ON sys.sysid = soft.sysid
>
>
> --Question
> I need to display those systems that have products - 'Office 2003', or
> 'Office XP' or 'Office 2000' or 'Acrobat' . From the above inserts, thus
> 'Office imaging' and 'Office backup' do not qualify. That means, out of
> total 13 rows, only 11 should be displayed. But my query above gives 13 with
> two rows having NULL values for both "ms office" and "acrobat". I dont want
> that -either one should be NULL. Can someone help me with the correct query?
>
>
> Thanks!
>
>
>
>
>
>
>
>
>

14 Sep 2006 12:41 PM

Uri Dimant

DL
Thanks for posting DLL

select * from
(
select sys.sysnme AS 'System name',sys.sysuser AS 'System User',
case
      when soft.prod like '%office%2003%' then 'Office 2003'
      when soft.prod like '%office%xp%' then 'Office XP'
      when soft.prod like '%office%2000%' then 'Office 2000'
end as 'ms office',
case
      when soft.prod like '%acrobat%' then 'Acrobat'
end as 'acrobat'
from systems sys
join software soft ON sys.sysid = soft.sysid
) as der where [ms office] is not null or acrobat is not null

Show quote

"DL account" <techiedl@nospam.gmail.com> wrote in message
news:uDtx6g$1GHA.1568@TK2MSFTNGP03.phx.gbl...
> --DDL
> create table systems
> (
> sysid int primary key,
> sysnme varchar(30) not null,
> sysuser varchar(40) null,
> )
> create table software
> (
> swid int primary key,
> sysid int references systems(sysid),
> cmpny varchar(30),
> prod varchar(30)
> )
> go
>
> --Data inserts
> insert systems values(1,'sys1','user1')
> insert systems values(2,'sys2','user2')
> insert systems values(3,'sys3','user3')
> insert systems values(4,'sys4','user4')
> insert systems values(5,'sys5','user5')
> go
> insert software values(1,1,'Microsoft','Office 2003')
> insert software values(2,1,'Microsoft software','Office imaging')
> insert software values(3,1,'Microsoft corp','Office 2003')
> insert software values(4,1,'Microsoft veritas','Office backup')
>
> insert software values(10,1,'Microsoft Corporation','Office xp')
> insert software values(11,1,'Microsoft','Office xp live')
> insert software values(12,1,'Microsoft Corp','Office xp systems')
> insert software values(13,1,'Microsoft','Office 2000')
>
> insert software values(5,2,'Adobe','acrobat reader')
> insert software values(6,2,'Adobe inc','acrobat reader live')
> insert software values(7,2,'Adobe','acrobat writer')
> insert software values(8,2,'Adobe','acrobat registry')
> insert software values(9,2,'Adobe','acrobat')
>
>
> --Query that I have tried
> select sys.sysnme AS 'System name',sys.sysuser AS 'System User',
> case
>      when soft.prod like '%office%2003%' then 'Office 2003'
>      when soft.prod like '%office%xp%' then 'Office XP'
>      when soft.prod like '%office%2000%' then 'Office 2000'
> end as 'ms office',
> case
>      when soft.prod like '%acrobat%' then 'Acrobat'
> end as 'acrobat'
> from systems sys
> join software soft ON sys.sysid = soft.sysid
>
>
> --Question
> I need to display those systems that have products - 'Office 2003', or
> 'Office XP' or 'Office 2000' or 'Acrobat' . From the above inserts, thus
> 'Office imaging' and 'Office backup' do not qualify. That means, out of
> total 13 rows, only 11 should be displayed. But my query above gives 13
> with two rows having NULL values for both "ms office" and "acrobat". I
> dont want that -either one should be NULL. Can someone help me with the
> correct query?
>
>
> Thanks!
>
>
>
>
>
>
>
>

15 Sep 2006 4:31 AM

DL account

Thanks Hari, Jens, SQL Menace, Harsh and Uri. Your resp solution helped.

Now Iam stuck with a different situation. Adding to the previous INSERTS,
....

--new inserts
insert software values(14,1,'Adobe','acrobat reader')
insert software values(15,1,'Adobe','acrobat ')
insert software values(16,1,'Adobe','acrobat writer')

insert software values(17,2,'Microsoft','office 2000')
insert software values(18,2,'Microsoft','office XP')
insert software values(19,2,'Microsoft','office 2003 systems')

--Question
Adding to the revious requirement, now the Front end developer asks me if
its possible to avoid the extra rows when a system has both Office and
acrobat installed and also to avoid the redundant data for the same version.
Below is the required output:

System name System User   ms office   acrobat
----------- -----------        -----------      -------
sys1        user1                 Office 2003        Acrobat
sys1        user1                 Office XP           Acrobat
sys1        user1                 Office 2000        Acrobat
sys2        user2                 Office 2000        Acrobat
sys2        user2                 Office XP           Acrobat
sys2        user2                 Office 2003        Acrobat

Means if 'sys1' has more than one Office2003 and acrobat installed, show it
only as one entry. Then if it has Office XP or a different version than 2003
then only show a extra row and since it has Acrobat installed, fill that
column too instead of leaving it NULL. I hope I did not confuse you. (First
I thought its not possible, then as Harsh's sig says, may be its possible :)

For example, if I add new rows like
insert software values(20,3,'Microsoft','office 2003 systems')
insert software values(21,2,'Microsoft','office 2003 live')

and when I run the query, it can show "acrobat" as NULL for "sys3" because
sys3 does not have acrobat installed. But it should not show an extra row
for "sys2" because already we have shown that "sys2" has office 2003

Thanks!

Show quote

"DL account" <techiedl@nospam.gmail.com> wrote in message
news:uDtx6g$1GHA.1568@TK2MSFTNGP03.phx.gbl...
> --DDL
> create table systems
> (
> sysid int primary key,
> sysnme varchar(30) not null,
> sysuser varchar(40) null,
> )
> create table software
> (
> swid int primary key,
> sysid int references systems(sysid),
> cmpny varchar(30),
> prod varchar(30)
> )
> go
>
> --Data inserts
> insert systems values(1,'sys1','user1')
> insert systems values(2,'sys2','user2')
> insert systems values(3,'sys3','user3')
> insert systems values(4,'sys4','user4')
> insert systems values(5,'sys5','user5')
> go
> insert software values(1,1,'Microsoft','Office 2003')
> insert software values(2,1,'Microsoft software','Office imaging')
> insert software values(3,1,'Microsoft corp','Office 2003')
> insert software values(4,1,'Microsoft veritas','Office backup')
>
> insert software values(10,1,'Microsoft Corporation','Office xp')
> insert software values(11,1,'Microsoft','Office xp live')
> insert software values(12,1,'Microsoft Corp','Office xp systems')
> insert software values(13,1,'Microsoft','Office 2000')
>
> insert software values(5,2,'Adobe','acrobat reader')
> insert software values(6,2,'Adobe inc','acrobat reader live')
> insert software values(7,2,'Adobe','acrobat writer')
> insert software values(8,2,'Adobe','acrobat registry')
> insert software values(9,2,'Adobe','acrobat')
>
>
> --Query that I have tried
> select sys.sysnme AS 'System name',sys.sysuser AS 'System User',
> case
>      when soft.prod like '%office%2003%' then 'Office 2003'
>      when soft.prod like '%office%xp%' then 'Office XP'
>      when soft.prod like '%office%2000%' then 'Office 2000'
> end as 'ms office',
> case
>      when soft.prod like '%acrobat%' then 'Acrobat'
> end as 'acrobat'
> from systems sys
> join software soft ON sys.sysid = soft.sysid
>
>
> --Question
> I need to display those systems that have products - 'Office 2003', or
> 'Office XP' or 'Office 2000' or 'Acrobat' . From the above inserts, thus
> 'Office imaging' and 'Office backup' do not qualify. That means, out of
> total 13 rows, only 11 should be displayed. But my query above gives 13
> with two rows having NULL values for both "ms office" and "acrobat". I
> dont want that -either one should be NULL. Can someone help me with the
> correct query?
>
>
> Thanks!
>
>
>
>
>
>
>
>