Need to get all Currency codes for a given Merchant ID from a DB2 table ...

Toad for IBM DB2
1 
I have a DB2 table TBL1. It has Merchant ID(MRCHNT_ID) and Currency_Code and few other columns. My requirement is for every Merchant Id, I need its list of Currency codes. Ex:

MRCHNT     CURRENCY
  ID         CODE   
---------  --------
001812310  036      
001812310  124      
001812310  554      
001812310  826      
001812310  840      
001812310  978

So for this I need to get the resultant as:

MRCHNT     CURRENCY CURRENCY  CURRENCY
  ID         CODE1   CODE2      CODE3
---------  -------- -------- -------- and etc..
001812310  036      124         554

I am getting this below resultant:

 MRCHNT                                                         
   ID       COL1  COL2  COL3  COL4  COL5  COL6  COL7  COL8  COL9
 ---------  ----  ----  ----  ----  ----  ----  ----  ----  ----
 001812310                                                  USD
 001812310                                            GBP       
 001812310                                NZD                   
 001812310              EUR                                     
 001812310        CAD                                           
 001812310  AUD

When I run this query:

SELECT DISTINCT(MRCHNT_ID),
CAST(DECODE(A.CURRENCY_CODE,'036','AUD',' ')AS CHAR(3)),
CAST(DECODE(A.CURRENCY_CODE,'124','CAD',' ')AS CHAR(3)),
CAST(DECODE(A.CURRENCY_CODE,'978','EUR',' ')AS CHAR(3)),
CAST(DECODE(A.CURRENCY_CODE,'344','HKD',' ')AS CHAR(3)),
CAST(DECODE(A.CURRENCY_CODE,'392','JPY',' ')AS CHAR(3)),
CAST(DECODE(A.CURRENCY_CODE,'554','NZD',' ')AS CHAR(3)),
CAST(DECODE(A.CURRENCY_CODE,'702','SGD',' ')AS CHAR(3)),
CAST(DECODE(A.CURRENCY_CODE,'826','GBP',' ')AS CHAR(3)),
CAST(DECODE(A.CURRENCY_CODE,'840','USD',' ')AS CHAR(3))     
FROM FX.FX_ACCOUNT A
WHERE A.MRCHNT_ID = 001812310
GROUP BY CURRENCY_CODE, MRCHNT_ID
WITH UR;
Need your expertise advice to get the resultant in the below format

MRCHNT     CURRENCY CURRENCY  CURRENCY
  ID         CODE1   CODE2      CODE3
---------  -------- -------- -------- and etc..
001812310  036      124         554

Thanks in advance !!!
2

Hi Mohan;

Please try the following -

Table entries:

MRCHNT_ID
CURRENCY_CODE
ABCD
001
ABCD
002
ABCD
003
ABCE
002
ABCE
003
ABCE
004
select MRCHNT_ID,
MAX(DECODE(CURRENCY_CODE, ‘001’, ‘USD’)) AS C1,
MAX(DECODE(CURRENCY_CODE, ‘002’, ‘CAD’)) AS C2,
MAX(DECODE(CURRENCY_CODE, ‘003’, ‘EUR’)) AS C3,
MAX(DECODE(CURRENCY_CODE, ‘004’, ‘GBP’)) AS C4
from FX.FX_ACCOUNT a
group by MRCHNT_ID;

Of course you can change the DECODE to represent CURRENCY_CODE vs currency acronym, and add as many currencies as you want.

3

Dear yahya.nattagh,

Bulls Eye !!! It worked :slight_smile:

Thank you so much !!!

Regards,

Mohan Prasath