CODEWITHSUNDEEP
oracle-databasefunctiondecode
Ragav
Ragav

Reputation: 229

Parameterize the Decode Function

In a oracle i'm using a Decode Statement as below. For security reasons i don't want the code to have the hardcoded values and i plan to create a new lookup table.

  Select CONCAT( Decode(A.COUNTRY_INITIAL,
                        'A','America',
                        'B','Brazil',
                        'F','FINLAND',
                        NULL),
                 Decode(A.ADD_VALUE,
                        'M','YES',
                        NULL))
  from  (
      Select SUBSTR(COUNTRY_BASE, -1,1) as COUNTRY_INITIAL,
             SUBSTR(IS_VALUED, -1,1) as ADD_VALUE
      from TBL1
  )A

Refernece Table
*******************
Clmn1   Clmn2   Clmn3
--------------------------
cntry1  A       America
cntry2  B       Brazil
cntry3  F       Finland
Value1  M       YES

Could you please let me know how i can incorporate this in the decode logic. Also fyi im using this CODE SNIPPET in a Oracle Function.

Upvotes: 0

Views: 101

Answers (3)

Noel
Noel

Reputation: 10525

If your are using separate table to store the values, you don't need decode function. You can simply join the two tables.

select a.country_base,
       a.is_valued,
       b.clmn3,
       c.clmn3
  from tbl1 a left outer join reference_table b
              on (substr(a.country_base, -1, 1) = b.clmn2
              and b.clmn1 like 'cntry%'  --extra clause needed if there are same country and value codes
              )
              left outer join reference_table c
              on (substr(a.is_valued, -1, 1) = c.clmn2
              and c.clmn1 like 'Value%'  --extra clause needed if there are same country and value codes
              );

Upvotes: 0

Chamal
Chamal

Reputation: 1449

If you have these details in a table you can simply use a join to get desired output.

select t1.COUNTRY_BASE,ref.Clmn3,ref1.Clmn3
frorm TBL1 t1
left outer join reftable ref
on SUBSTR(t1.COUNTRY_BASE, -1,1)=ref.Clmn2  
left outer join reftable ref1
on SUBSTR(t1.IS_VALUED, -1,1)=ref.Clmn2;

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231661

If you're going to store the lookup information in a table, you wouldn't use a DECODE. You'd join the two tables

SELECT ref.clmn3
  FROM tbl1 t1
       LEFT OUTER JOIN <<reference table>> ref
         ON( substr(t1.country_base, -1, 1) = ref.clmn2 )

Since your DECODE has a NULL, I'm guessing that you are expecting that some rows of tbl1 will not have a matching row in the reference table so I'm guessing that you want a LEFT OUTER JOIN rather than an INNER JOIN.

Upvotes: 1

Related Questions