Home » SQL & PL/SQL » SQL & PL/SQL » covert case into decode
covert case into decode [message #210807] Fri, 22 December 2006 08:07 Go to next message
shailesh.jain14
Messages: 6
Registered: December 2006
Location: pune
Junior Member
hello ,,can anyone convert this case statement of power builder into pl/sql decode
convert case statement into decode


CHOOSE CASE as_source_entry
CASE 'T'
CHOOSE CASE as_event_code
CASE 'DATAENTRY', 'PAIDLIQ', 'RETURNLIQ', 'DISPATCH', 'CERTLIQ', 'TECHLIQ', 'LOSTLIQ', 'FUNDTXFER'
CHOOSE CASE as_txn_ref_type
CASE 'D'
SELECT "DEPOSIT_HEADER_TXN"."INPUT_DEPOSIT_NMBR"
INTO :ls_ref_nmbr
FROM "DEPOSIT_HEADER_TXN"
WHERE "DEPOSIT_HEADER_TXN"."DEPOSIT_NMBR" = :as_txn_ref_nmbr Using SQLCA;
CASE 'I'
SELECT "DEPOSIT_DETAIL_TXN"."INSTRUMENT_NMBR"
INTO :ls_ref_nmbr
FROM "DEPOSIT_DETAIL_TXN"
WHERE ( "DEPOSIT_DETAIL_TXN"."DEPOSIT_NMBR" = substr(:as_txn_ref_nmbr,1,12) ) AND
( "DEPOSIT_DETAIL_TXN"."INTERNAL_TRANSACTION_NMBR" = substr(:as_txn_ref_nmbr,13,3) ) ;
END CHOOSE
CASE 'PDC_DATAEN', 'PDC_CANCEL', 'PDC_OUTWRD', 'PDCPERFUND', 'PDCONHOLD', 'PDCFEREV','PDCFEFUND','PDCDISCONT','PDCDISCEND'
CHOOSE CASE as_txn_ref_type
CASE 'D'
SELECT "PDC_DEPOSIT_HEADER_TXN"."INPUT_DEPOSIT_NMBR"
INTO :ls_ref_nmbr
FROM "PDC_DEPOSIT_HEADER_TXN"
WHERE "PDC_DEPOSIT_HEADER_TXN"."DEPOSIT_NMBR" = :as_txn_ref_nmbr Using SQLCA;
CASE 'I'
SELECT "PDC_DEPOSIT_DETAIL_TXN"."INSTRUMENT_NMBR"
INTO :ls_ref_nmbr
FROM "PDC_DEPOSIT_DETAIL_TXN"
WHERE ( "PDC_DEPOSIT_DETAIL_TXN"."DEPOSIT_NMBR" = substr(:as_txn_ref_nmbr,1,12) ) AND
( "PDC_DEPOSIT_DETAIL_TXN"."INTERNAL_TRANSACTION_NMBR" = substr(:as_txn_ref_nmbr,13,3) ) ;
END CHOOSE
CASE 'PAYMENT'
CHOOSE CASE as_txn_ref_type
CASE 'P'
SELECT "PAYMENT_HEADER_TXN"."PAYORDER_NMBR"
INTO :ls_ref_nmbr
FROM "PAYMENT_HEADER_TXN"
WHERE "PAYMENT_HEADER_TXN"."PAYMENT_NMBR" = :as_txn_ref_nmbr ;
END CHOOSE

CASE 'PAYOUT'
CHOOSE CASE as_txn_ref_type
CASE 'Y'
SELECT "PAYOUT_DETAIL_TXN"."PAYORDER_NMBR"
INTO :ls_ref_nmbr
FROM "PAYOUT_DETAIL_TXN"
WHERE "PAYOUT_DETAIL_TXN"."PAYORDER_NMBR" = :as_txn_ref_nmbr AND
"PAYOUT_DETAIL_TXN"."CLIENT_CODE" = :as_entity_code ;
END CHOOSE
END CHOOSE
CASE 'C'
CHOOSE CASE as_event_code
CASE 'DATAENTRY', 'PAIDLIQ', 'RETURNLIQ', 'DISPATCH', 'CERTLIQ', 'TECHLIQ', 'LOSTLIQ', 'FUNDTXFER', 'PICKUP'
CHOOSE CASE as_txn_ref_type
CASE 'D'
SELECT "DEPOSIT_HEADER_TXN"."INPUT_DEPOSIT_NMBR"
INTO :ls_ref_nmbr
FROM "CHARGE_COMPUTE_AUDIT_TXN", "DEPOSIT_HEADER_TXN"
WHERE ( "CHARGE_COMPUTE_AUDIT_TXN"."TRANSACTION_NMBR" = "DEPOSIT_HEADER_TXN"."DEPOSIT_NMBR" ) and
( ( "CHARGE_COMPUTE_AUDIT_TXN"."POSTING_JOURNAL_NMBR" = :as_txn_ref_nmbr ) ) ;
CASE 'I'
SELECT "DEPOSIT_DETAIL_TXN"."INSTRUMENT_NMBR"
INTO :ls_ref_nmbr
FROM "CHARGE_COMPUTE_AUDIT_TXN", "DEPOSIT_DETAIL_TXN"
WHERE ( "CHARGE_COMPUTE_AUDIT_TXN"."TRANSACTION_NMBR" = "DEPOSIT_DETAIL_TXN"."DEPOSIT_NMBR" ) and
( "CHARGE_COMPUTE_AUDIT_TXN"."UNIT_NMBR" = "DEPOSIT_DETAIL_TXN"."INTERNAL_TRANSACTION_NMBR" ) and
( "CHARGE_COMPUTE_AUDIT_TXN"."POSTING_JOURNAL_NMBR" = substr(:as_txn_ref_nmbr,1,12)) and
( "CHARGE_COMPUTE_AUDIT_TXN"."UNIT_NMBR" = substr(:as_txn_ref_nmbr,13,3) ) ;
END CHOOSE
CASE 'PDC_DATAEN', 'PDC_CANCEL', 'PDC_OUTWRD', 'PDCPERFUND', 'PDCONHOLD', 'PDCFEREV','PDCFEFUND','PDCDISCONT','PDCDISCEND'
CHOOSE CASE as_txn_ref_type
CASE 'D'
SELECT "PDC_DEPOSIT_HEADER_TXN"."INPUT_DEPOSIT_NMBR"
INTO :ls_ref_nmbr
FROM "CHARGE_COMPUTE_AUDIT_TXN", "PDC_DEPOSIT_HEADER_TXN"
WHERE ( "CHARGE_COMPUTE_AUDIT_TXN"."TRANSACTION_NMBR" = "PDC_DEPOSIT_HEADER_TXN"."DEPOSIT_NMBR" ) and
( ( "CHARGE_COMPUTE_AUDIT_TXN"."POSTING_JOURNAL_NMBR" = :as_txn_ref_nmbr ) ) ;
CASE 'I'
SELECT "PDC_DEPOSIT_DETAIL_TXN"."INSTRUMENT_NMBR"
INTO :ls_ref_nmbr
FROM "CHARGE_COMPUTE_AUDIT_TXN", "PDC_DEPOSIT_DETAIL_TXN"
WHERE ( "CHARGE_COMPUTE_AUDIT_TXN"."TRANSACTION_NMBR" = "PDC_DEPOSIT_DETAIL_TXN"."DEPOSIT_NMBR" ) and
( "CHARGE_COMPUTE_AUDIT_TXN"."UNIT_NMBR" = "PDC_DEPOSIT_DETAIL_TXN"."INTERNAL_TRANSACTION_NMBR" ) and
( "CHARGE_COMPUTE_AUDIT_TXN"."POSTING_JOURNAL_NMBR" = substr(:as_txn_ref_nmbr,1,12)) and
( "CHARGE_COMPUTE_AUDIT_TXN"."UNIT_NMBR" = substr(:as_txn_ref_nmbr,13,3) ) ;
END CHOOSE
END CHOOSE


thanks & regards
shailesh jain
Re: covert case into decode [message #210828 is a reply to message #210807] Fri, 22 December 2006 09:02 Go to previous messageGo to next message
joy_division
Messages: 4768
Registered: February 2005
Location: East Coast USA
Senior Member
Why?
If you must change it for education purposes, you can write a whole bunch of IF / THEN statements.
Without CODE tags, your posted message is unreadable.
Re: covert case into decode [message #210907 is a reply to message #210828] Sat, 23 December 2006 02:55 Go to previous messageGo to next message
shailesh.jain14
Messages: 6
Registered: December 2006
Location: pune
Junior Member
i want to convert the given code into decode
the given code is in power builder
plz convert into decode

thanks regards
shailesh jain
  • Attachment: 1
    (Size: 3.78KB, Downloaded 196 times)
Re: covert case into decode [message #210915 is a reply to message #210907] Sat, 23 December 2006 03:45 Go to previous messageGo to next message
Littlefoot
Messages: 21151
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I didn't read your code; but, are you sure you want to convert CASE to DECODE? CASE is much more clear to read and easier to understand and maintain. If complicated, DECODE is awfully difficult to write, read and debug.
Re: covert case into decode [message #211005 is a reply to message #210807] Mon, 25 December 2006 01:08 Go to previous message
naiksushma
Messages: 15
Registered: December 2006
Junior Member
check this small part if works out


DECODE(as_source_entry,'T',DECODE(as_event_code,'DATAENTRY', 'PAIDLIQ', 'RETURNLIQ', 'DISPATCH', 'CERTLIQ', 'TECHLIQ', 'LOSTLIQ', 'FUNDTXFER',DECODE(as_txn_ref_type,'D',SELECT "DEPOSIT_HEADER_TXN"."INPUT_DEPOSIT_NMBR"
INTO :ls_ref_nmbr
FROM "DEPOSIT_HEADER_TXN"
WHERE "DEPOSIT_HEADER_TXN"."DEPOSIT_NMBR" = :as_txn_ref_nmbr Using SQLCA,'I',
SELECT "DEPOSIT_DETAIL_TXN"."INSTRUMENT_NMBR"
INTO :ls_ref_nmbr
FROM "DEPOSIT_DETAIL_TXN"
WHERE ( "DEPOSIT_DETAIL_TXN"."DEPOSIT_NMBR" = substr(:as_txn_ref_nmbr,1,12) ) AND
( "DEPOSIT_DETAIL_TXN"."INTERNAL_TRANSACTION_NMBR" = substr(:as_txn_ref_nmbr,13,3) ) ;
Previous Topic: insert into
Next Topic: How is it possible?!
Goto Forum:
  


Current Time: Thu Aug 24 04:01:03 CDT 2017

Total time taken to generate the page: 0.01889 seconds