'replacing' values in a select statement

Question

'replacing' values in a select statement

mm-1009269

SSCommitted

Points: 1559
More actions
July 16, 2009 at 2:33 pm

#136704

Hello,
I was wondering is it possible to 'replace' a value in a column within a select statement, but not 'commit' the value? For example, I have these two statements in my code:
, CRM_Orders.PROCESS_TYPE AS [Service Order Trans Type]
, MAX(CASE WHEN CRM_Orders.PROCESS_TYPE = 'ZRPM' THEN CRM_Orders.PROCESS_TYPE ELSE NULL END) as [PM Orders]
And my resulting column looks like this:
Service Order Trans Type | PM Orders
ZSVO | NULL
ZRPM | ZRPM
ZRPM | ZRPM
ZINT | NULL
So, basically my script identifies any order as a PM Order by listing ZRPM in the PM Orders column and anything that's listed as NULL isn't a qualifying order. And this works great, but what would be really slick 😎 is if I could esentially do the same thing but replace it with a meaningful word for all the codes in a single column without altering the table, so do it with variables and stuff....
So it would look like this:
Service Order Trans Type | Order Desc
ZSVO | Order A
ZRPM | PM Order
ZRPM | PM Order
ZINT | Order B
The reason why I'd like to do this is so that my report is in a more user friendly format because not everybody knows what those codes mean.
Any help or a step in the right direction would be greatly appreciated. I don't even know if this is possible???
THANK YOU!
Michelle 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Any reason you can't create a lookup table and join to that? One column with the abbreviation, one with the friendly description.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

mm-1009269 SSCommitted Points: 1559 More actions · Answer 2

Hi GSquared! 🙂

I could.... but I was hoping to get around that because I would need approval to create a new table. And then heads might roll. I'm kinda' unique where I'm at because I'm a programmer but I'm not in IT so I need approval for those kinds of things and then it gets bureaucratic.

Thanks -Michelle

manish.singh-1128425 SSC-Addicted Points: 402 More actions · Answer 3

ZSVO | Order A
ZRPM | PM Order
ZRPM | PM Order
ZINT | Order B

Having a lookup table for the PROCESS_TYPE is the best approach to handle your problem. You should not avoid just because of the required approvals.

Even though you can replace these Process Type codes with your desired string values using case statements but i would preffer to go with the lookup table only as case statements would not be a scalable approach. If you go for case statements every time one new process type is added you need to modify your code moreover it will cause performance issues also in future as your table grows.

If still you wish to go with changing the process types in the query itself then u can use following code.

CASE WHEN CRM_Orders.PROCESS_TYPE = 'ZSVO' THEN 'Order A'

WHEN CRM_Orders.PROCESS_TYPE = 'ZRPM' THEN 'PM Order'

WHEN CRM_Orders.PROCESS_TYPE = 'ZINT' THEN 'Order B'

ELSE NULL END as [PM Orders]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

mmunson (7/16/2009)
Hi GSquared! 🙂
I could.... but I was hoping to get around that because I would need approval to create a new table. And then heads might roll. I'm kinda' unique where I'm at because I'm a programmer but I'm not in IT so I need approval for those kinds of things and then it gets bureaucratic.
Thanks -Michelle

Build it as a temp table in the proc.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 5

Jeff Moden (7/17/2009)
mmunson (7/16/2009)
Hi GSquared! 🙂
I could.... but I was hoping to get around that because I would need approval to create a new table. And then heads might roll. I'm kinda' unique where I'm at because I'm a programmer but I'm not in IT so I need approval for those kinds of things and then it gets bureaucratic.
Thanks -Michelle
Build it as a temp table in the proc.

A temp table in the proc is your second best option. Still means it'll require code modification if your list ever gets updated, but it'll be better than a rack of case statements.

Perhaps an option would be for you to have a database on the server that is your playground, where you could build tables and write code, but where it wouldn't change anything in the main database.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon