• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1500
  • Last Modified:

Using the Decode PL/SQL

I would like to use the Decode function in my query to do the followingi

if rec_itl_medical.expiration_date < vSysDate then
          vStatusReason := 'EXPIRED';
           vActive := 'INACTIVE';
   else
        vStatusReason := NULL;
        if rec_itl_medical.qualified = 'Y' then
               vActive := 'ACTIVE';
         else  
             vActive := 'INACTIVE';  
          end if;  
    end if;
0
cookiejar
Asked:
cookiejar
2 Solutions
 
sujith80Commented:
Something like this should do
select  case when rec_itl_medical.expiration_date < vSysDate then 'EXPIRED' 
        else null end AS vStatusReason,
        case when rec_itl_medical.expiration_date < vSysDate then 'INACTIVE'
        else case when rec_itl_medical.qualified = 'Y' then 'ACTIVE'
             else 'INACTIVE' end
        end AS vActive 
from .....

Open in new window

0
 
MarkusIdCommented:

SELECT decode(sign(vSysDate - rec_itl_medical.expiration_date),
         1, 'EXPIRED',
            NULL),
       decode(sign(vSysDate - rec_itl_medical.expiration_date),
         1, 'INACTIVE',
            decode(nvl(rec_itl_medical.qualified, 'N'),
              'Y', 'ACTIVE',
                   'INACTIVE'))
  INTO vStatusReason, vActive
  FROM DUAL;

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now