SQL DECODE FUNCTION

[MP]

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_C ODE,1, 'CHICK_BURGERS' ) ITEM_DESCR,
DECODE(B.ITEM_C ODE,2, 'HAM_BURGERS') ITEM_DESCR,
DECODE(B.ITEM_C ODE,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

[MGFoster]

Re: SQL DECODE FUNCTION

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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_C ODE=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.

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

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRAihu4echKq OuFEgEQISAgCfQL 8lMRg5zxyyVFhtW U1KjNxvvyIAoOTU
1ddTddi38ZdAb9x xerTcap2E
=VF8d
-----END PGP SIGNATURE-----

MP wrote:[color=blue]
> 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_C ODE,1, 'CHICK_BURGERS' ) ITEM_DESCR,
> DECODE(B.ITEM_C ODE,2, 'HAM_BURGERS') ITEM_DESCR,
> DECODE(B.ITEM_C ODE,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?[/color]