March 20, 2008 at 8:34 am
Good morning all,
I am trying to write a select statement using Case and if, here is what I have so far - this works:
select
CASE incspc_type
when '10' then 'Product'
when '20' then 'Item'
when '40' then 'Location'
when '30' then 'SECTION-DEPARTMENT'
when '50' then 'USER'
when '60' then 'Category'
when '70' then 'SERIOUSNESS'
when '80' then 'Priority'
when '90' then 'PRODUCT CLASS'
end as type,
case incspc_code
when 'a' then 'red'
when 'b' then 'blue'
when 'c' then 'green'
end as color,
incspc_item
from incspc where incspc_id > 0
Now I need to say if type = 50 then join do a join on table incsps50 using the incspc_id and returning the incps50_sc. I can do the join but I'm not sure how to do the if - I'll need to do an if for each type.
Thanks in advance for your help,
Diann
March 20, 2008 at 8:43 am
Well, I can't tell from your description what type of join you need so you'll have to do that part but this should get you what you want. My example will use an outer join.
LEFT JOIN incsps50
ON incsps50.incspc_id = incspc.incspc_id and incspc_type = 50
March 20, 2008 at 8:44 am
This is a little bit confusing. Please will you provide table DLL in the form of CREATE TABLE statements, some sample data in the form of INSERT statements and, most importantly, your expected results in the case were type is and isn't 50.
Thanks
John
March 20, 2008 at 8:47 am
Thanks, now I see - just add the "and incspc_id = 50" to my join. I'll give it a try and see what happens, thanks again - you guys are quick!
Diann
March 20, 2008 at 8:51 am
I hope that helps, but like John M. said, your description is a bit confusing so my reply was kind of a stab in the dark at what you wanted. If that does not do it for you, post table DDL and sample data as he suggested.
March 20, 2008 at 1:43 pm
OK, I got tied up in another project and am just getting back to this, it works fine if there is only one join I need. Here's a create table script and a few inserts for the tables.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[product]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[product]
GO
CREATE TABLE [dbo].[product] (
[product_id] [int] IDENTITY (1, 1) NOT NULL ,
[product_sc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[item]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[item]
GO
CREATE TABLE [dbo].[item] (
[item_id] [int] IDENTITY (1, 1) NOT NULL ,
[item_sc] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[incspc_test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[incspc_test]
GO
CREATE TABLE [dbo].[incspc_test] (
[incspc_id] [int] IDENTITY (1, 1) NOT NULL ,
[incspc_type] [int] NOT NULL ,
[incspc_item] [int] NOT NULL
) ON [PRIMARY]
GO
insert into incspc_test (incspc_type,incspc_item) values (10,1)
insert into incspc_test (incspc_type,incspc_item) values (20,1)
insert into incspc_test (incspc_type,incspc_item) values (10,2)
insert into incspc_test(incspc_type, incspc_item) values (20,2)
insert into item (item_sc) values (24)
insert into item (item_sc) values (44)
insert into product (product_sc) values (12345)
insert into product (product_sc) values (222777)
Basically I need to do a statement that returns
incspc_type, incspc_item, where item is actually the "sc" of either the item table or the product table - depending on the type.
if the type is 10 the incspc_item is the product_sc from the product table - if the type is 20 the incspc_item is the item_sc from the item table. The join is based on incspc_item which is the id from either table.
I need to return something like this:
type item
PRODUCT 12345
ITEM 24
PRODUCT 222777
ITEM 44
Hope I did this right, here's the query I have written so far (with John's help on the join of course)
The join works fine until I add another join to join the incspc_type to product.product_sc.
select
CASE incspc_type
when '10' then 'Product'
when '20' then 'Item'
end as type,
item.item_sc
from incspc_test inner join item on item.item_id = incspc_test.incspc_item and incspc_test.incspc_type = 20
where incspc_id > 0
Again, thanks in advance for any assistance,
Diann
March 26, 2008 at 5:43 am
Diann
Are you sure you need the join at all? From the data you've provided (which looks as if you've simplified it quite a lot), it appears that this will work.
SELECT
'Product' AS [type]
,product_sc AS sc
FROM
product
UNION
SELECT
'Item'
,item_sc
FROM
item
John
March 26, 2008 at 8:41 am
John is right. If you are going to scale your original query out to gather data from multiple tables as such, a UNION is probably the best way to go.
March 26, 2008 at 8:57 am
Thanks guys, a UNION is exactly what I needed.
Diann
March 27, 2008 at 11:49 pm
hi Diann
i am confused at first you asked solution for if condition and
john rowan replied the solution and told that it is working
at the end you telling you required not join but union
please make sure your result
Shamsudheen
March 28, 2008 at 9:17 am
Shamsudheen,
I think this was a case where we were not privy to the full scope of the problem domain that Diann was trying to solve. The solution that I originally posted would work for the original problem that Diann described, but as soon as more tables were thrown into the mix, the rules changed and a UNION made more sense. Again, I think this was a case where we did not originally know enough about what Diann was trying to do to come up with the best solution the first time around.
I think this is why you often see posts asking for a better description of the problem before help is offered. I think in this case, Diann offered a good start in at least posting some code to look at, but it may have helped to know that the solution was going to be applied to multiple tables. In defense of Diann, it may not have been known that the scope was going to change until Diann's first problem was solved.
March 28, 2008 at 9:43 am
Here is the code that worked (after I found out all the variables involved - gotta love end users).
select Case incspc.incspc_type
when '10' then 'Product'
when '20' then 'Item'
when '40' then 'Location'
when '30' then 'SECTION-DEPARTMENT'
when '50' then 'USER'
when '60' then 'Category'
when '70' then 'SERIOUSNESS'
when '80' then 'Priority'
when '90' then 'item CLASS'
end as type,
Case incspc.incspc_code
when 'a' then 'red'
when 'b' then 'blue'
when 'c' then 'green'
end as color,
product.product_sc as CI, incspc.incspc_rmk, incspc.inc_serious, incspc.inc_prior, incspc.display_rmk
FROM incspc inner join product on product.product_id = incspc.incspc_item
WHERE incspc.incspc_id >0 and incspc.incspc_type = 10 and incspc.stat_flag = 'n'
Union ALL select
Case incspc.incspc_type
when '10' then 'Product'
when '20' then 'Item'
when '40' then 'Location'
when '30' then 'SECTION-DEPARTMENT'
when '50' then 'USER'
when '60' then 'Category'
when '70' then 'SERIOUSNESS'
when '80' then 'Priority'
when '90' then 'item CLASS'
end as type,
Case incspc.incspc_code
when 'a' then 'red'
when 'b' then 'blue'
when 'c' then 'green'
end as color, item.item_sc as CI, incspc_rmk, incspc.inc_serious, incspc.inc_prior, incspc.display_rmk
FROM incspc inner join item on item.item_id = incspc.incspc_item
WHERE incspc.incspc_id >0 and incspc.incspc_type = 20 and incspc.stat_flag = 'n'
Union ALL select Case incspc.incspc_type
when '10' then 'Product'
when '20' then 'Item'
when '40' then 'Location'
when '30' then 'SECTION-DEPARTMENT'
when '50' then 'USER'
when '60' then 'Category'
when '70' then 'SERIOUSNESS'
when '80' then 'Priority'
when '90' then 'item CLASS'
end as type,
Case incspc.incspc_code
when 'a' then 'red'
when 'b' then 'blue'
when 'c' then 'green'
end as color, item.item_sc as CI, incspc_rmk, incspc.inc_serious, incspc.inc_prior, incspc.display_rmk
FROM incspc inner join bldng_room on bldng_room.bldng_room_id = incspc.incspc_item
WHERE incspc.incspc_id >0 and incspc.incspc_type = 40 and incspc.stat_flag = 'n'
Union ALL select Case incspc.incspc_type
when '10' then 'Product'
when '20' then 'Item'
when '40' then 'Location'
when '30' then 'SECTION-DEPARTMENT'
when '50' then 'USER'
when '60' then 'Category'
when '70' then 'SERIOUSNESS'
when '80' then 'Priority'
when '90' then 'item CLASS'
end as type,
Case incspc.incspc_code
when 'a' then 'red'
when 'b' then 'blue'
when 'c' then 'green'
end as color, item.item_sc as CI, incspc_rmk, incspc.inc_serious, incspc.inc_prior, incspc.display_rmk
FROM incspc inner join sectn_dept on sectn_dept.sectn_dept_id = incspc.incspc_item
WHERE incspc.incspc_id >0 and incspc.incspc_type = 30 and incspc.stat_flag = 'n'
Union ALL select Case incspc.incspc_type
when '10' then 'Product'
when '20' then 'Item'
when '40' then 'Location'
when '30' then 'SECTION-DEPARTMENT'
when '50' then 'USER'
when '60' then 'Category'
when '70' then 'SERIOUSNESS'
when '80' then 'Priority'
when '90' then 'item CLASS'
end as type,
Case incspc.incspc_code
when 'a' then 'red'
when 'b' then 'blue'
when 'c' then 'green'
end as color, item.item_sc as CI, incspc_rmk, incspc.inc_serious, incspc.inc_prior, incspc.display_rmk
FROM incspc inner join usr on usr.usr_id = incspc.incspc_item
WHERE incspc.incspc_id >0 and incspc.incspc_type = 50 and incspc.stat_flag = 'n'
Union ALL select Case incspc.incspc_type
when '10' then 'Product'
when '20' then 'Item'
when '40' then 'Location'
when '30' then 'SECTION-DEPARTMENT'
when '50' then 'USER'
when '60' then 'Category'
when '70' then 'SERIOUSNESS'
when '80' then 'Priority'
when '90' then 'item CLASS'
end as type,
Case incspc.incspc_code
when 'a' then 'red'
when 'b' then 'blue'
when 'c' then 'green'
end as color, item.item_sc as CI, incspc_rmk, incspc.inc_serious, incspc.inc_prior, incspc.display_rmk
FROM incspc inner join inc_cat on inc_cat.inc_cat_id = incspc.incspc_item
WHERE incspc.incspc_id >0 and incspc.incspc_type = 60 and incspc.stat_flag = 'n'
Union ALL select Case incspc.incspc_type
when '10' then 'Product'
when '20' then 'Item'
when '40' then 'Location'
when '30' then 'SECTION-DEPARTMENT'
when '50' then 'USER'
when '60' then 'Category'
when '70' then 'SERIOUSNESS'
when '80' then 'Priority'
when '90' then 'item CLASS'
end as type,
Case incspc.incspc_code
when 'a' then 'red'
when 'b' then 'blue'
when 'c' then 'green'
end as color, item.item_sc as CI, incspc_rmk, incspc.inc_serious, incspc.inc_prior, incspc.display_rmk
FROM incspc inner join inc_serious on inc_serious.inc_serious_id = incspc.incspc_item
WHERE incspc.incspc_id >0 and incspc.incspc_type = 70 and incspc.stat_flag = 'n'
Union ALL select Case incspc.incspc_type
when '10' then 'Product'
when '20' then 'Item'
when '40' then 'Location'
when '30' then 'SECTION-DEPARTMENT'
when '50' then 'USER'
when '60' then 'Category'
when '70' then 'SERIOUSNESS'
when '80' then 'Priority'
when '90' then 'item CLASS'
end as type,
Case incspc.incspc_code
when 'a' then 'red'
when 'b' then 'blue'
when 'c' then 'green'
end as color, item.item_sc as CI, incspc_rmk, incspc.inc_serious, incspc.inc_prior, incspc.display_rmk
FROM incspc inner join inc_prior on inc_prior.inc_prior_id = incspc.incspc_item
WHERE incspc.incspc_id >0 and incspc.incspc_type = 80 and incspc.stat_flag = 'n'
[/Code]
Thanks much guys,
Diann
March 28, 2008 at 9:51 am
Thanks for following up with the end solution Diann! Also, now that you have the UNION, you no longer need to include the CASE statement for your Types. See examle:
select 'Product' as type,
Case incspc.incspc_code
when 'a' then 'red'
when 'b' then 'blue'
when 'c' then 'green'
end as color,
product.product_sc as CI,
incspc.incspc_rmk,
incspc.inc_serious,
incspc.inc_prior,
incspc.display_rmk
FROM incspc inner join product on product.product_id = incspc.incspc_item
WHERE incspc.incspc_id >0 and incspc.incspc_type = 10 and incspc.stat_flag = 'n'
Union ALL
select 'Item' as type,
Case incspc.incspc_code
when 'a' then 'red'
when 'b' then 'blue'
when 'c' then 'green'
end as color,
item.item_sc as CI,
incspc_rmk,
incspc.inc_serious,
incspc.inc_prior,
incspc.display_rmk
FROM incspc inner join item on item.item_id = incspc.incspc_item
WHERE incspc.incspc_id >0 and incspc.incspc_type = 20 and incspc.stat_flag = 'n'
Union ALL
select 'Location' as type,
Case incspc.incspc_code
when 'a' then 'red'
when 'b' then 'blue'
when 'c' then 'green'
end as color,
item.item_sc as CI,
incspc_rmk,
incspc.inc_serious,
incspc.inc_prior,
incspc.display_rmk
FROM incspc inner join bldng_room on bldng_room.bldng_room_id = incspc.incspc_item
WHERE incspc.incspc_id >0 and incspc.incspc_type = 40 and incspc.stat_flag = 'n'
Union ALL
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply