OraFAQ Forum: Reports & Discoverer

Home » Developer & Programmer » Reports & Discoverer » case statement

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

case statement [message #296580]

Mon, 28 January 2008 05:41

roni_a180
Messages: 45
Registered: October 2007

Member

hi i write the following code

select case when t.FE_TRANS_CAT_CODE = '108' then (select sum(t.AMOUNT_CCY) from FE_IMPORT_LC il,
fe_currency c,
fe_transactions t,
fe_trans_category tc
where
DR_CR='C' AND
FE_TRANS_CAT_CODE in('108','110') and -- 110
il.IMP_CURR_CODE = c.CUR_CODE and
t.REF_SL_NO =il.IMP_LC_SL_NO
and t.REF_AMEND_NO =il.IMP_AMEND_NO
and il.SUB_MODULE_CODE = t.SUB_MODULE_CODE
and il.PRODUCT_CODE = t.PRODUCT_CODE
and il.OWN_BRANCH_CODE=t.LOGIN_BRANCH_CODE
and il.OPERATION_CODE = t.OPERATION_CODE
and t.FE_TRANS_CAT_CODE = tc.TRANS_CATGORY_CODE
and il.IMP_AMEND_NO = (select max(to_number(IMP_AMEND_NO)) from fe_import_lc
where IMP_LC_SL_NO = il.IMP_LC_SL_NO)
and il.IMP_LC_OPEN_DATE between :vsdate and :vedate
and il.OWN_BRANCH_CODE=:P_BRANCH_CODE
AND il.TRANSMIT_LC_NO=:TRANSMIT_LC_NO) else t.AMOUNT_CCY
end amt_ccy
from FE_IMPORT_LC il,
fe_currency c,
fe_transactions t,
fe_trans_category tc
where
DR_CR='C' AND
FE_TRANS_CAT_CODE not in('101') and -- 110
il.IMP_CURR_CODE = c.CUR_CODE and
t.REF_SL_NO =il.IMP_LC_SL_NO
and t.REF_AMEND_NO =il.IMP_AMEND_NO
and il.SUB_MODULE_CODE = t.SUB_MODULE_CODE
and il.PRODUCT_CODE = t.PRODUCT_CODE
and il.OWN_BRANCH_CODE=t.LOGIN_BRANCH_CODE
and il.OPERATION_CODE = t.OPERATION_CODE
and t.FE_TRANS_CAT_CODE = tc.TRANS_CATGORY_CODE
and il.IMP_AMEND_NO = (select max(to_number(IMP_AMEND_NO)) from fe_import_lc
where IMP_LC_SL_NO = il.IMP_LC_SL_NO)
and il.IMP_LC_OPEN_DATE between :vsdate and :vedate
and il.OWN_BRANCH_CODE=:P_BRANCH_CODE
AND il.TRANSMIT_LC_NO like decode(nvl(:P_REF_NO,'all'),'all','%',:P_REF_NO)
--&P_W
order by t.TRANS_DATE,il.TRANSMIT_LC_NO

-------------------------------------
-------------------------------------

this statement working in sql editor, but this statement is not working in reporting function statement(Oracle Report).i can not compile this sql .

help me

Report message to a moderator

Re: case statement [message #296589 is a reply to message #296580]

Mon, 28 January 2008 06:15

Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Report Builder's PL/SQL engine may not support CASE expression (which is, on the other hand, supported in (PL/)SQL of the database you use).

Try to rewrite the query using the DECODE instead.

Of course, you understand that this is just a blind shot. "It is not working" or "It won't compile" means almost nothing. Is there any error message? I guess there is. Which one?

Also, when do you intend to properly format code you post on this forum? The one you posted is hardly readable. We wrote the Guide, it contains the "How to format your posts" section, so - if you'd like me to answer your next question, learn how to write an acceptable message (from my point of view, that is).

Report message to a moderator

Re: case statement [message #296822 is a reply to message #296589]

Tue, 29 January 2008 03:15

skooman
Messages: 913
Registered: March 2005
Location: Netherlands

Senior Member

Furthermore, when formatted it shows that your statement seems to be pretty weird:

SELECT CASE
         WHEN t.fe_trans_cat_code = '108' THEN
          (SELECT SUM(t.amount_ccy)
           FROM   fe_import_lc      il
                 ,fe_currency       c
                 ,fe_transactions   t
                 ,fe_trans_category tc
           WHERE  dr_cr = 'C'
           AND    fe_trans_cat_code IN ('108', '110')
           AND    -- 110 
                  il.imp_curr_code = c.cur_code
           AND    t.ref_sl_no = il.imp_lc_sl_no
           AND    t.ref_amend_no = il.imp_amend_no
           AND    il.sub_module_code = t.sub_module_code
           AND    il.product_code = t.product_code
           AND    il.own_branch_code = t.login_branch_code
           AND    il.operation_code = t.operation_code
           AND    t.fe_trans_cat_code = tc.trans_catgory_code
           AND    il.imp_amend_no =
                  (SELECT MAX(to_number(imp_amend_no))
                    FROM   fe_import_lc
                    WHERE  imp_lc_sl_no = il.imp_lc_sl_no)
           AND    il.imp_lc_open_date BETWEEN :vsdate AND :vedate
           AND    il.own_branch_code = :p_branch_code
           AND    il.transmit_lc_no = :transmit_lc_no)
         ELSE
          t.amount_ccy
       END amt_ccy
FROM   fe_import_lc      il
      ,fe_currency       c
      ,fe_transactions   t
      ,fe_trans_category tc
WHERE  dr_cr = 'C'
AND    fe_trans_cat_code NOT IN ('101')
AND    -- 110 
       il.imp_curr_code = c.cur_code
AND    t.ref_sl_no = il.imp_lc_sl_no
AND    t.ref_amend_no = il.imp_amend_no
AND    il.sub_module_code = t.sub_module_code
AND    il.product_code = t.product_code
AND    il.own_branch_code = t.login_branch_code
AND    il.operation_code = t.operation_code
AND    t.fe_trans_cat_code = tc.trans_catgory_code
AND    il.imp_amend_no =
       (SELECT MAX(to_number(imp_amend_no))
         FROM   fe_import_lc
         WHERE  imp_lc_sl_no = il.imp_lc_sl_no)
AND    il.imp_lc_open_date BETWEEN :vsdate AND :vedate
AND    il.own_branch_code = :p_branch_code
AND    il.transmit_lc_no LIKE decode(nvl(:p_ref_no
                                        ,'all')
                                    ,'all'
                                    ,'%'
                                    ,:p_ref_no)
--&P_W 
ORDER  BY t.trans_date
         ,il.transmit_lc_no

It looks as if you're doing:
if X then (select statement) else Y from (almost exactly same statement)
What are you trying to accomplish?

Report message to a moderator

Previous Topic:	funny fonts in oracle reports builder
Next Topic:	best practice in creating discoverer business area's

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jul 02 09:00:59 CDT 2024