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: 4641
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 191 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: 20897
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: variable column names
Next Topic: Inserting Image in database
Goto Forum:
  


Current Time: Wed Dec 07 03:12:49 CST 2016

Total time taken to generate the page: 0.09076 seconds