By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,073 Members | 1,218 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,073 IT Pros & Developers. It's quick & easy.

SQL DECODE FUNCTION

P: n/a
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_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

Mar 3 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
-----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_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.

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

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

iQA/AwUBRAihu4echKqOuFEgEQISAgCfQL8lMRg5zxyyVFhtWU1KjN xvvyIAoOTU
1ddTddi38ZdAb9xxerTcap2E
=VF8d
-----END PGP SIGNATURE-----

MP wrote:
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?

Mar 3 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.