SQL DECODE FUNCTION

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MP - 03 Mar 2006 17:13 GMT

I have the following command.

SELECT A.STORE_NAME, A.STORE_NUM, A.ZIP_CODE, B.EMPLOYEE_ID, B.GENDER,
B.EMP_LEVEL, C.ITEM_CODE,
DECODE(B.ITEM_CODE,1, 'CHICK_BURGERS') ITEM_DESCR,
DECODE(B.ITEM_CODE,2, 'HAM_BURGERS') ITEM_DESCR,
DECODE(B.ITEM_CODE,3, 'PIZZAS') ITEM_DESCR
FROM
STORE_DETAIL A,
EMP_DETAIL B,
ITEM_DETAIL C
WHERE A.STORE_CODE = B.STORE_CODE AND
B.EMP_ID = C.EMP_ID

But I want the output as where ever there is 1 under Item code column,
in the next column corresponding to it I want CHICK_BURGERS under Item
description column. Similarly where ever there is 2 under Item code
column there has to be HAM_BURGERS under item description column and so
on and so forth. But right now with the above command I am getting 3
item description columns where as I want only one item description
column.
Can someone please tell me how do I do this?

Thank you.
MP

Reply to this Message

MGFoster - 03 Mar 2006 20:06 GMT

There isn't a DECODE() function in Access SQL. I don't know exactly
what DECODE() does, but from your question and the way the DECODE
expression looks, I'm guessing it can be translated to a Switch()
function, like this:

SELECT A.STORE_NAME, A.STORE_NUM, A.ZIP_CODE, B.EMPLOYEE_ID, B.GENDER,
B.EMP_LEVEL, C.ITEM_CODE,
Switch(B.ITEM_CODE=1, 'CHICK_BURGERS',
B.ITEM_CODE=2, 'HAM_BURGERS',
B.ITEM_CODE=3, 'PIZZAS',
B.ITEM_CODE NOT IN (1,2,3), 'UNKNOWN') ITEM_DESCR
FROM ... etc.

Signature

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

> I have the following command.
>
[quoted text clipped - 18 lines]
> column.
> Can someone please tell me how do I do this?

Reply to this Message