Solved

Dividing Product Categories into Departments

Posted on 2014-04-15
13
284 Views
Last Modified: 2014-04-15
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?
0
Comment
Question by:sharris_glascol
13 Comments
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 40001904
Can you post the full query? I see no mention of "2nd floor standard"
0
 

Author Comment

by:sharris_glascol
ID: 40001909
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
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 40001922
You mean you want to replace 'Department' with '2nd Floor Standard' or 1 with '2nd Floor Standard'?
0
 

Author Comment

by:sharris_glascol
ID: 40001927
want to replace under department where it says 1 with 2nd Floor Standard
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 40001931
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

Open in new window

0
 

Author Comment

by:sharris_glascol
ID: 40001937
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.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 34

Accepted Solution

by:
Dan Craciun earned 500 total points
ID: 40001940
And you put '0', not just 0? Cause if one of the values is int it will try to convert the other too.
If both are strings it should work.

If not, post some sample data so I can play with it here.
0
 

Author Closing Comment

by:sharris_glascol
ID: 40001946
thanks
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 40001947
Glad I could help!
0
 
LVL 35

Expert Comment

by:YZlat
ID: 40001963
Did you put single quotes around 0?
0
 
LVL 37

Expert Comment

by:Bing CISM / CISSP
ID: 40001964
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.
0
 

Author Comment

by:sharris_glascol
ID: 40001966
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'
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 40001975
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

Open in new window

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now