need to convert decode to case

why do you want to change it?

you can, but there shouldn't be a need that I can see

 DECODE (p_currency, 'FOREIGN CURRENCY', 'USD')

would be

case  p_currency
   when 'FOREIGN CURRENCY' then 'USD'
end

or

case when p_currency = 'FOREIGN CURRENCY' then 'USD' end

when foreign currency i need to add 'USD', 'GBP'

more generically

decode(value,
acheck,aresult,
bcheck,bresult,
ccheck,cresult,
defaultresult)

is

case value
  when acheck then aresult
  when bcheck then bresult
  when ccheck then cresult
  else defaultresult
end

or

case
  when value = acheck then aresult
  when value = bcheck then bresult
  when value = ccheck then cresult
  else defaultresult
end


if you don't specify a default result in the decode or case then any value that doesn't find a match will result in NULL being returned

>>> when foreign currency i need to add 'USD', 'GBP'


what does that mean?  you listed one "when" but two results.

>>if foreign curreny then consider 'USD' and 'GBP, instead of just 'USD'

I do not fully inderstand what this means but to guess:

glh.currency_code in (case when p_currency = 'FOREIGN CURRENCY' then 'USD' end,
case when p_currency = 'FOREIGN CURRENCY' then 'GBP' end)


Case or decode will work here.

if foreign currency then take both like in  ( 'USD', 'GBP')
else if 'LOCAL_CURRENCY' then dont consider 'USD'
take all the currencies except 'USD'

Why use decode at all?

WHERE
...
(
(p_currency ='FOREIGN CURRENCY' and glh.currency_code in ('USD','GBP'))
or
(p_currency ='LOCAL CURRENCY' and glh.currency_code != 'USD')
)
...

AND (CASE
                       WHEN p_currency = 'FOREIGN CURRENCY'
                       THEN  glh.currency_code in (  'USD', 'GBP')
                       WHEN p_currency = 'LOCAL CURRENCY'
                       THEN
                          glh.currency_code != 'USD'
                       ELSE
                          NULL
                    END)

but it not working am i missing something

your AND doesn't check anything.

a CASE returns a value.  you're not doing anything with that value.

have you tried not using decode or case and just use simple and/or as slightwv suggested?

AND (CASE
                       WHEN p_currency = 'FOREIGN CURRENCY'
                       THEN  glh.currency_code in (  'USD', 'GBP')
                       WHEN p_currency = 'LOCAL CURRENCY'
                       THEN
                          glh.currency_code != 'USD'
                       ELSE
                          NULL
                    END)

i was just pasting a small part of the query thats why there is AND

the full query is

 SELECT RPAD (' ', 2) "FILLER1",
               RPAD (gcc.segment5, 6, ' ') "BANK_CODE",
               --RPAD(,6) "BANK_CODE",
               RPAD (' ', 4) "FILLER2",
               RPAD (SUBSTR (TRIM (glh.name), -2), 2) "FINANCIAL_CODE",
               RPAD (' ', 4) "FILLER3",
               SUBSTR (
                  RPAD (
                     (   glh.name
                      || '-'
                      || gll.je_line_num
                      || DECODE (gll.description, NULL, NULL, '-')
                      || gll.description),
                     40,
                     ' '),
                  1,
                  40)
                  "WORDING",
               RPAD (' ', 2) "FILLER4",
               RPAD (NVL (gll.reference_4, ' '), 7, ' ') "CHECK_NUM",
               RPAD (NVL (gll.reference_4, ' '), 12, ' ') "NUM",
               NVL (gll.reference_4, ' ') "NUM1",
               gll.reference_2 "CASH_RECEIPT_ID",
               RPAD (' ', 16) "FILLER5",
               --(LPAD(TO_CHAR(DECODE,GLH.CURRENCY_CODE,'USD',(DECODE(GLL.ENTERED_DR,NULL,GLL.ENTERED_CR,GLL.ENETERED_DR),(DECODE(GLL.ACCOUNTED_DR,NULL,GLL.ACCOUNTED_CR,GLL.ACCOUNTED_DR)),'999999999.00'),20,' ') "AMOUNT",
               DECODE (
                  glh.currency_code,
                  'USD', LPAD (
                            TO_CHAR (
                               DECODE (gll.entered_dr,
                                       NULL, gll.entered_cr,
                                       gll.entered_dr),
                               '999999999.00'),
                            20,
                            ' '),
                  LPAD (
                     TO_CHAR (
                        DECODE (gll.accounted_dr,
                                NULL, gll.accounted_cr,
                                gll.accounted_dr),
                        '999999999.00'),
                     20,
                     ' '))
                  "AMOUNT",
               RPAD (' ', 2) "FILLER6",
               RPAD (TO_CHAR (gll.effective_date, 'DDMMYYYY'), 8, ' ') "DATE",
               RPAD (' ', 3) "FILLER7",
               RPAD (DECODE (gll.accounted_dr, NULL, 'C', 'D'), 1, ' ')
                  "DEBIT_CREDIT",
               glh.je_header_id "HEADER_ID",
               gll.je_line_num "LINE_ID",
               glh.je_source "SOURCE",
               glh.je_category "CATEGORY",
               gll.gl_sl_link_id "GL_SL_LINK_ID",
               gll.reference_3 "REFERENCE_3"
          FROM apps.gl_je_headers glh,
               apps.gl_je_lines gll,
               apps.gl_code_combinations gcc
         WHERE     1 = 1
               AND glh.je_header_id = gll.je_header_id
               --AND GLH.JE_HEADER_ID = 2021
               AND gcc.code_combination_id = gll.code_combination_id
               AND gll.set_of_books_id = p_sob_id
               AND gll.period_name = p_period
               AND gcc.segment5 = NVL (p_acct, gcc.segment5)
               -- AND (GLH.CURRENCY_CODE = DECODE(P_CURRENCY,'LOCAL CURRENCY','EUR')
               --OR (GLH.CURRENCY_CODE = DECODE(P_CURRENCY,'FOREIGN CURRENCY',GLH.CURRENCY_CODE) AND GLH.CURRENCY_CODE<>'EUR'))
               /*AND (glh.currency_code =
                       DECODE (p_currency, 'FOREIGN CURRENCY', 'USD')
                    OR (glh.currency_code =
                           DECODE (p_currency,
                                   'LOCAL CURRENCY', glh.currency_code)
                        AND glh.currency_code <> 'USD'))*/
               AND (CASE
                       WHEN p_currency = 'FOREIGN CURRENCY'
                       THEN  glh.currency_code in (  'USD', 'GBP')
                       WHEN p_currency = 'LOCAL CURRENCY'
                       THEN
                          glh.currency_code != 'USD'
                       ELSE
                          NULL
                    END)
               AND gcc.segment5 IN
                      ('10011',
                       '10012',
                       '10013',
                       '10014',
                       '10015',
                       '10016',
                       '10017',
                       '10018',
                       '10019',
                       '10021',
                       '10023',
                       '10024',
                       '10037',
                       '10036',
                       '10038',
                       '10039')
      ORDER BY glh.je_source;

>>WHEN p_currency = 'FOREIGN CURRENCY'
>>                       THEN  glh.currency_code in (  'USD', 'GBP')

You cannot build a dynamic where clause this way.

If you wish to do dynamic SQL, you need to build a string that contains the SQL, then execute the query.

it's not really a dynamic condition, it's simply an OR

pardeshirahul,

I appreciate the points, but my answer in http:#a38008585, while correct, was intended to show you how convoluted the CASE option was if you insisted on pursuing that.

The better solution, as stated before, is to NOT to try to use decode or case as you are doing, but instead, to simply use AND and OR as slightwv showed