sharris_glascol
asked on
Dividing Product Categories into Departments
I am trying to divide product Categories into different departments. For Example
prod_cat_desc ='100b' which should be department 2nd Floor Standard. Here is the code I have but it doesn't like the name 2nd floor Standard..
case when prod_cat_desc = '100b' or prod_cat_desc ='100c' or prod_cat_desc ='100d' or prod_cat_desc ='100e' or prod_cat_desc ='100f' or prod_cat_desc ='100m' then 1 else 0 end as Department
What am I doing wrong?
prod_cat_desc ='100b' which should be department 2nd Floor Standard. Here is the code I have but it doesn't like the name 2nd floor Standard..
case when prod_cat_desc = '100b' or prod_cat_desc ='100c' or prod_cat_desc ='100d' or prod_cat_desc ='100e' or prod_cat_desc ='100f' or prod_cat_desc ='100m' then 1 else 0 end as Department
What am I doing wrong?
Can you post the full query? I see no mention of "2nd floor standard"
ASKER
2nd floor standard is the name I want to give to all of those items if Prod_cat = each of those..
select ord_no,
line_no,
item_no,
CONVERT(dateTime, CONVERT(char(10), promise_dt, 101)) AS 'Promised Date',
CONVERT(dateTime, CONVERT(char(10), shipped_dt, 101)) AS 'Shipped Date',
imc.prod_cat_desc,
case when promise_dt = shipped_dt then 1 else 0 end as ontime,
case when promise_dt > shipped_dt then 1 else 0 end as early,
case when shipped_dt > promise_dt then 1 else 0 end as late,
case when prod_cat_desc = '100b' or prod_cat_desc ='100c' or prod_cat_desc ='100d' or prod_cat_desc ='100e' or prod_cat_desc ='100f' or prod_cat_desc ='100m' then 1 else 0 end as Department
from oelinhst_sql
join imcatfil_sql AS imc ON oelinhst_sql.prod_cat = imc.prod_cat
select ord_no,
line_no,
item_no,
CONVERT(dateTime, CONVERT(char(10), promise_dt, 101)) AS 'Promised Date',
CONVERT(dateTime, CONVERT(char(10), shipped_dt, 101)) AS 'Shipped Date',
imc.prod_cat_desc,
case when promise_dt = shipped_dt then 1 else 0 end as ontime,
case when promise_dt > shipped_dt then 1 else 0 end as early,
case when shipped_dt > promise_dt then 1 else 0 end as late,
case when prod_cat_desc = '100b' or prod_cat_desc ='100c' or prod_cat_desc ='100d' or prod_cat_desc ='100e' or prod_cat_desc ='100f' or prod_cat_desc ='100m' then 1 else 0 end as Department
from oelinhst_sql
join imcatfil_sql AS imc ON oelinhst_sql.prod_cat = imc.prod_cat
You mean you want to replace 'Department' with '2nd Floor Standard' or 1 with '2nd Floor Standard'?
ASKER
want to replace under department where it says 1 with 2nd Floor Standard
Have you tried:
CASE
WHEN prod_cat_desc = '100b'
OR prod_cat_desc = '100c'
OR prod_cat_desc = '100d'
OR prod_cat_desc = '100e'
OR prod_cat_desc = '100f'
OR prod_cat_desc = '100m'
THEN '2nd Floor Standard'
ELSE '0'
END
AS Department
ASKER
yes getting this error message:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '2nd Floor Standard' to data type int.
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '2nd Floor Standard' to data type int.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
Glad I could help!
Did you put single quotes around 0?
better post the whole SQL statement rather than the above field assigment.
BTW, the comparison on prod_cat_desc is a bit complex and hard coded. you may consider putting all possible values of prod_cat_desc in to a table, then use a SELECT statement to determine whether the prod_cat_desc is the scope. using this way, you no need to modify source code if the candidate values are changed.
BTW, the comparison on prod_cat_desc is a bit complex and hard coded. you may consider putting all possible values of prod_cat_desc in to a table, then use a SELECT statement to determine whether the prod_cat_desc is the scope. using this way, you no need to modify source code if the candidate values are changed.
ASKER
yes what if I want to do something similar after the else 0??
case when prod_cat_desc = '100b' or prod_cat_desc ='100c' or prod_cat_desc ='100d' or prod_cat_desc ='100e' or prod_cat_desc ='100f' or prod_cat_desc ='100m' then '2nd Floor Standard' else case when prod_cat_desc = '099a' or prod_cat_desc = '104a' or prod_cat_desc = '103a' or prod_cat_desc = '105a' or prod_cat_desc = '107a' or prod_cat_desc = '109a' or prod_cat_desc = '108a' then 'EM Standard' else '0' end AS Department
now I am getting an error the AS is
Incorrect syntax near the keyword 'AS'
case when prod_cat_desc = '100b' or prod_cat_desc ='100c' or prod_cat_desc ='100d' or prod_cat_desc ='100e' or prod_cat_desc ='100f' or prod_cat_desc ='100m' then '2nd Floor Standard' else case when prod_cat_desc = '099a' or prod_cat_desc = '104a' or prod_cat_desc = '103a' or prod_cat_desc = '105a' or prod_cat_desc = '107a' or prod_cat_desc = '109a' or prod_cat_desc = '108a' then 'EM Standard' else '0' end AS Department
now I am getting an error the AS is
Incorrect syntax near the keyword 'AS'
You're missing an END
CASE
WHEN prod_cat_desc = '100b' OR prod_cat_desc ='100c' OR prod_cat_desc ='100d' OR prod_cat_desc ='100e' OR prod_cat_desc ='100f' OR prod_cat_desc ='100m'
THEN '2nd Floor Standard'
ELSE
CASE WHEN prod_cat_desc = '099a' OR prod_cat_desc = '104a' OR prod_cat_desc = '103a' OR prod_cat_desc = '105a' OR prod_cat_desc = '107a' OR prod_cat_desc = '109a' OR prod_cat_desc = '108a'
THEN 'EM Standard'
ELSE '0'
END
END
AS Department