CASE Statement Syntax Question 1

[MikeDevenney]

Can anyone tell me what I'm doing wrong in the statement below? The sub-select in the first WHEN block works fine when not embedded in the CASE statement but I need to add the test for the message type (MT103 vs MT202) and return a different value for BeneficiaryCustomerAccount and some (but not all) other fields based on the result. Am I going about this the wrong way?

SELECT
foo,
bar,
(CASE
WHEN imt.imt_cde_msg_type = 'MT103' THEN Select rolbenc.ior_txt_acct_no from ls2user.tls_imt_out_role rolbenc where rolbenc.ior_rid_outgng_imt = imt.imt_rid_imt_out and rolbenc.ior_cde_swft_rtyp = 'BENCR'
WHEN imt.imt_cde_msg_type = 'MT202'
THEN '') as BeneficiaryCustomerAccount,
...
FROM
...
WHERE
...

 

[SantaMufasa]

Mike,

My initial "desk compile" of your code says that you should have an "END" statement between '' and ). Try that and let us know if that resolves your problem.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Turkbear]

Hi,
Looking at the CASE examples in the Oracle docs I do not see any where a SELECT statement can be part of the CASE structure - try another way ( which I am sure someone will post):

http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/expressions5a.htm#1033394

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Turkbear]

Hi,
OOPs, someone found a way...Not a real surprise that it was Santa






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[MikeDevenney]

Thanks for the quick replies Turk and Santa. Here's what I came up with (that seems to be working) after adding the END (thanks Santa!). I come from the other side of the fence and I'm still finding my way around in this new Oracle world...

CASE
WHEN imt.imt_cde_msg_type = 'MT103'
THEN (Select rolbenc.ior_nme_swft_id from ls2user.tls_imt_out_role rolbenc where rolbenc.ior_rid_outgng_imt = imt.imt_rid_imt_out and rolbenc.ior_cde_swft_rtyp = 'BENCR' )
WHEN imt.imt_cde_msg_type = 'MT202'
THEN ''
END as BeneficiaryCustomerName,

 

[SantaMufasa]

Turkbear, is certainly correct...I haven't seen a CASE statement that contains a SELECT statement. So, either place the sub-SELECT statement in your FROM clause as an in-line VIEW, or at least place the sub-SELECT's table in the from statement, and restrict rows to the ones you want in the WHERE clause.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[MikeDevenney]

Not sure if there is enough room to paste my entire statement but if I can then I think you'll see why I can't (at my skill level, at least) include the sub-select table in the from clause. One caveat, the data returned by this statement is consumed by an interface to a 3rd party system so the 'XXX' and empty string selects are necessary.

SELECT
   imt.imt_rid_imt_out as RID,
   'XXX' as CompanyID,                                      
   imt.imt_cde_msg_type as TransactionType,
   'XXX' as LedgerID,                                       
   cas.cfl_xid_account as DebitAccount,                     
   fac.fac_cde_currency as BaseCurrency,          
   '' as OrderingCustomerAccount,                           
   '' as OrderingCustomerName,                              
   '' as OrderingCustomerAddress1,
   '' as OrderingCustomerAddress2,
   '' as OrderingCustomerAddress3,
   'Wimington Trust' as OrderingInstitutionName,
   '1100 North Market Street' as OrderingInstitutionAddress1,
   'Wilmington,  DE  19801' as OrderingInstitutionAddress2,
   '' as OrderingInstitutionAddress3,
   imt.imt_cde_currency as TransferCurrencyCode,
   imt.imt_amt_out_tot as TransferAmount,
   imt.imt_dte_value_date as ValueDate,
   imt.imt_rid_imt_out as CustomerReference,
   imt.imt_rid_cashflow as BeneInstitutionReference,
   '' as PayeeCountry,
   'SHA' as Charges,
   CASE 
    WHEN imt.imt_cde_msg_type = 'MT103' 
      THEN (Select rolbenc.ior_txt_acct_no from ls2user.tls_imt_out_role rolbenc where rolbenc.ior_rid_outgng_imt = imt.imt_rid_imt_out and rolbenc.ior_cde_swft_rtyp = 'BENCR')
    WHEN imt.imt_cde_msg_type = 'MT202' 
      THEN '' 
    END as BeneficiaryCustomerAccount,         
   CASE 
    WHEN imt.imt_cde_msg_type = 'MT103' 
      THEN (Select rolbenc.ior_txt_acct_no from ls2user.tls_imt_out_role rolbenc where rolbenc.ior_rid_outgng_imt = imt.imt_rid_imt_out and rolbenc.ior_cde_swft_rtyp = 'BENCR' ) 
    WHEN imt.imt_cde_msg_type = 'MT202' 
      THEN '' 
    END as BeneficiaryCustomerIBAN,
   CASE 
    WHEN imt.imt_cde_msg_type = 'MT103' 
      THEN (Select rolbenc.ior_nme_swft_id  from ls2user.tls_imt_out_role rolbenc where rolbenc.ior_rid_outgng_imt = imt.imt_rid_imt_out and rolbenc.ior_cde_swft_rtyp = 'BENCR' ) 
    WHEN imt.imt_cde_msg_type = 'MT202' 
      THEN '' 
    END as BeneficiaryCustomerName,
   CASE 
    WHEN imt.imt_cde_msg_type = 'MT103' 
      THEN (Select rolbenc.ior_txt_desc from ls2user.tls_imt_out_role rolbenc where rolbenc.ior_rid_outgng_imt = imt.imt_rid_imt_out and rolbenc.ior_cde_swft_rtyp = 'BENCR' ) 
    WHEN imt.imt_cde_msg_type = 'MT202' 
      THEN '' 
    END as BeneficiaryCustAddress,           
   '' as BeneficiaryCustAddress1,
   '' as BeneficiaryCustAddress2,
   '' as BeneficiaryCustAddress3,
   CASE
    WHEN (Select length(trim(rolbeni.ior_cde_swft_id)) from ls2user.tls_imt_out_role rolbeni where rolbeni.ior_rid_outgng_imt = imt.imt_rid_imt_out and rolbeni.ior_cde_swft_rtyp = 'BENIN') > 5
      THEN 'SWIFT BIC'
    ELSE NULL
   END as BeneInstitutionRouteType,
   (Select rolbeni.ior_cde_swft_id from ls2user.tls_imt_out_role rolbeni where rolbeni.ior_rid_outgng_imt = imt.imt_rid_imt_out and rolbeni.ior_cde_swft_rtyp = 'BENIN' ) as BeneInstitutionBankID,
   (Select rolbeni.ior_txt_acct_no from ls2user.tls_imt_out_role rolbeni where rolbeni.ior_rid_outgng_imt = imt.imt_rid_imt_out and rolbeni.ior_cde_swft_rtyp = 'BENIN' ) as BeneInstitutionAccount,
   (Select rolbeni.ior_txt_acct_no from ls2user.tls_imt_out_role rolbeni where rolbeni.ior_rid_outgng_imt = imt.imt_rid_imt_out and rolbeni.ior_cde_swft_rtyp = 'BENIN' ) as BeneInstitutionIBAN,
   (Select rolbeni.ior_nme_swft_id from ls2user.tls_imt_out_role rolbeni where rolbeni.ior_rid_outgng_imt = imt.imt_rid_imt_out and rolbeni.ior_cde_swft_rtyp = 'BENIN' ) as BeneInstitutionName,
   (Select rolbeni.ior_txt_desc from ls2user.tls_imt_out_role rolbeni where rolbeni.ior_rid_outgng_imt = imt.imt_rid_imt_out and rolbeni.ior_cde_swft_rtyp = 'BENIN' ) as BeneficiaryInstAddress,           -- will have to be split into 3 lines in interface code
   '' as BeneficiaryInstAddress1,
   '' as BeneficiaryInstAddress2,
   '' as BeneficiaryInstAddress3,
   CASE
    WHEN (Select length(trim(rolacwi.ior_cde_swft_id)) from ls2user.tls_imt_out_role rolacwi where rolacwi.ior_rid_outgng_imt = imt.imt_rid_imt_out and rolacwi.ior_cde_swft_rtyp = 'ACWIN') > 5
      THEN 'SWIFT BIC'
    ELSE NULL
   END as AWInstitutionRouteType,
   (Select rolacwi.ior_cde_swft_id from ls2user.tls_imt_out_role rolacwi where rolacwi.ior_rid_outgng_imt = imt.imt_rid_imt_out and rolacwi.ior_cde_swft_rtyp = 'ACWIN' ) as AWInstitutionBankID,         
   CASE 
    WHEN imt.imt_cde_msg_type = 'MT202' 
      THEN (Select rolacwi.ior_txt_acct_no  from ls2user.tls_imt_out_role rolacwi where rolacwi.ior_rid_outgng_imt = imt.imt_rid_imt_out and rolacwi.ior_cde_swft_rtyp = 'ACWIN' ) 
    WHEN imt.imt_cde_msg_type = 'MT103'
      THEN ''
   END as AWInstitutionIBAN,
   CASE 
    WHEN imt.imt_cde_msg_type = 'MT202' 
      THEN (Select rolacwi.ior_nme_swft_id from ls2user.tls_imt_out_role rolacwi where rolacwi.ior_rid_outgng_imt = imt.imt_rid_imt_out and rolacwi.ior_cde_swft_rtyp = 'ACWIN' ) 
    WHEN imt.imt_cde_msg_type = 'MT103'
      THEN ''
   END as AWInstitutionName,    
   CASE 
    WHEN imt.imt_cde_msg_type = 'MT202' 
      THEN (Select rolacwi.ior_txt_desc from ls2user.tls_imt_out_role rolacwi where rolacwi.ior_rid_outgng_imt = imt.imt_rid_imt_out and rolacwi.ior_cde_swft_rtyp = 'ACWIN' ) 
    WHEN imt.imt_cde_msg_type = 'MT103'
      THEN ''
   END as AWInstitutionAddress,           
   '' as AWInstitutionAddress1,
   '' as AWInstitutionAddress2,
   '' as AWInstitutionAddress3,
   '' as IntermedInstRouteType,
   '' as IntermedInstBankID,
   '' as IntermedInstIBAN,
   '' as IntermedInstName,
   '' as IntermedInstAddr1,
   '' as IntermedInstAddr2,
   '' as IntermedInstAddr3,
   '' as BeneficiaryRef1, 
   '' as BeneficiaryRef2,
   '' as BeneficiaryRef3,
   '' as BeneficiaryRef4,
   '/BNF/' || imt.imt_txt_sdr_rvr_tx as BankToBank1,
   '' as BankToBank2, 
   '' as BankToBank3, 
   '' as BankToBank4, 
   '' as BankToBank5, 
   '' as BankToBank6          
FROM
  ls2user.vls_imt_out imt 
  INNER JOIN ls2user.vls_facility fac 
    on imt.imt_pid_facility = fac.fac_pid_facility
  INNER JOIN ls2user.vls_cashflow cas 
    ON imt.imt_rid_cashflow = cas.cfl_rid_

 

[SantaMufasa]

The problem, Mike, is that Oracle built the CASE clause to return a single value, similar to the way that a function returns a single value. The reason why Oracle disallows a SELECT statement to be a sub-clause in a CASE clause is because multiple expressions can return from a SELECT statement, which the CASE clause is not prepared to handle.

So, you must reconstruct your overall SELECT statement to not rely upon SELECT statements in your CASE clauses.

You can, however (it seems to me), relocate your sub-SELECT statements to your main FROM clause, and then "cherry pick" from your relocated SELECT statements the expressions you want to mention as CASE expressions.

If I'm not making myself clear, that is entirely possible...just let me know that such is the situation, and I'll attempt to re-word for clarity's sake.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”