Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kheard73
Starting Member
2 Posts |
 Posted - 2011-02-15 : 17:52:11
|
| I'm trying to run a query using CASE that allows me to find keywords and place them in a single column. I have attached some of the code..I keep getting the results but they come up in separate columns and I only need one column for all...CASE WHEN DESCRIPTION LIKE '%ADMIN%' THEN 'ADMIN'END AS CATEGORY,CASE WHEN DESCRIPTION LIKE '%ORDER%' THEN 'ADMIN'END AS CATEGORY,CASE WHEN DESCRIPTION LIKE '%PARTS%' THEN 'ADMIN'END AS CATEGORY, |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-02-15 : 18:37:41
|
| It would help if you posted your whole code, but you can do many cases in one!SELECT CASE WHEN DESCRIPTION LIKE '%ADMIN%' or DESCRIPTION LIKE '%ORDER%' or DESCRIPTION LIKE '%PARTS%' THEN 'ADMIN' END as CATEGORYJimEveryday I learn something that somebody else already knew |
 |
|
|
kheard73
Starting Member
2 Posts |
Posted - 2011-02-16 : 10:54:28
|
| Jim,Sorry about that, what I'm trying to do is have the CASE WHEN statements create all of their info in one column "CATEGORY" and not create multiple columns for each CASE statement. Here is the code SELECT LABTRANS.CRAFT , LABTRANS.LABORCODE AS FST , LABTRANS.WONUM AS WO , WORKORDER.DESCRIPTION , WORKORDER.STATUS AS "WO STATUS" , WORKORDER.WORKTYPE AS "WO TYPE" , WORKORDER.EQNUM , WORKORDER.FAILURECODE , WORKORDER.LOCATION , LABTRANS.GENAPPRSERVRECEIPT AS APPROVED , LABTRANS.REGULARHRS AS HOURS , LABTRANS.LINECOST AS "LABOR COST" , LABTRANS."LT5" AS MILEAGE , LABTRANS."LT6" AS "DRIVING TIME" , LABTRANS.TRANSTYPE AS TYPE , LABTRANS.STARTDATE , LABTRANS.FINISHDATE , LABTRANS.ENTERBY , LABTRANS.ENTERDATE , LABTRANS.GLDEBITACCT AS "GL DEBIT" , LABTRANS.GLCREDITACCT AS "GL CREDIT" , WORKORDER.ACTLABCOST , WORKORDER.ACTMATCOST , WORKORDER.ACTSERVCOST, (CASE WORKTYPE WHEN 'CP' THEN 'Captial Expense' WHEN 'EX' THEN 'Corrective Maint' WHEN 'PM' THEN 'Planned Maint'END) ACTION, (CASE TRANSTYPE WHEN 'REG' THEN 'Regular' WHEN 'COT' THEN 'Call Out' WHEN 'OV1' THEN 'Overtime'END) ACTIVITY, CASE WHEN DESCRIPTION LIKE '%ADMIN%' OR DESCRIPTION LIKE '%ORDER%' OR DESCRIPTION LIKE '%PART%' THEN 'ADMIN' END AS CATEGORY (CASE GLCREDITACCT WHEN '18806-5399-6948-988-600' THEN 'PIM' WHEN '18803-5399-6948-988-600' THEN 'SE' WHEN '18805-5399-6948-988-600' THEN 'NE'WHEN '18807-5399-6948-988-600' THEN 'PNW'WHEN '18804-5399-6948-988-600' THEN 'MW'WHEN '18802-5399-6948-988-600'THEN 'CS' ELSE 'N/A' END) REGIONFROM MAXIMO.WORKORDER WORKORDER LEFT OUTER JOIN MAXIMO.LABTRANS LABTRANS ON (WORKORDER.WONUM = LABTRANS.WONUM) WHERE (LABTRANS.CRAFT LIKE '%FST%') AND (LABTRANS.STARTDATE >= to_date('2011-01-01 00:00:00', 'yyyy/mm/dd hh24:mi:ss')) AND (LABTRANS.FINISHDATE <= to_date('2011-02-01 00:00:00', 'yyyy/mm/dd hh24:mi:ss')) |
|
|
|
|
|
|
|
|
CASE STATEMENT