help with decode 1

[ddiamond]

I have the following expression from DB2 which supports the ansi case expression:

  CASE
    WHEN ri.mamapc='QS' THEN 'Q'
    WHEN ri.mamapc LIKE 'Z%' THEN 'F'
    WHEN UPPER(ri.mamati) LIKE '%2ND XS%' THEN '2'
    WHEN UPPER(ri.mamati) LIKE '%SECOND EXCESS%' THEN '2'
    WHEN UPPER(ri.mamati) LIKE '%SURPLUS%' THEN 'S'
    ELSE 'X' END
  AS Category,

I'm trying to rewrite this for oracle 8. I believe later versions of oracle support the case expression, but oracle 8 does not. Does anyone know how I can accomplish the same thing using the decode() function?

 

[SantaMufasa]

D,

Try this:

select mamapc, mamati
   ,decode(ri.mamapc
      ,'QS','Q'
      ,decode(instr(ri.mamapc,'Z')
          ,1,'F'
          ,decode(sign(instr(UPPER(ri.mamati),'2ND XS'))
             ,1,'2'
             ,decode(sign(instr(UPPER(ri.mamati),'SECOND EXCESS'))
                ,1,'2'
                ,decode(sign(instr(UPPER(ri.mamati),'SURPLUS'))
                   ,1,'S'
                   ,'X'))))) Category
  from ri;

MAMAPC MAMATI               C
------ -------------------- -
QS                          Q
Z                           F
       xxx2nd xsxx          2
       xxxsecond excessxx   2
       xxxsurplusxx         S
       abcd                 X
                            X
QS     xxx2nd xsxx          Q
Z      xxxsecond excessxx   F

9 rows selected.

Let us know if this satisfied your need.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[ddiamond]

Perfect! Your use of the function sign() was genius. Here's my final query:

SELECT
  cm.claim_number,
  cm.policy_number,
  ri.policy_effective_date,
  ri.contract_number,
  rm.master_number || rm.master_sequence AS reference,
  DECODE(SUBSTR(ri.ri_product,1,1),'Q','Q','Z','F',
    DECODE(SIGN(INSTR(UPPER(rm.description),'2ND XS')),1,'2',
    DECODE(SIGN(INSTR(UPPER(rm.description),'SECOND EXCESS')),1,'2',
    DECODE(SIGN(INSTR(UPPER(rm.description),'SURPLUS')),1,'S',
    'X')))) AS Category,
  ri.ri_product,
  rm.description
FROM 
  migrate_gre.claim_master cm,
  migrate_gre.policy_ri_attachment ri,
  migrate_gre.reinsurance_master rm
WHERE cm.policy_number = ri.policy_number
  AND ri.contract_number = rm.contract
;