need to convert decode to case

pardeshirahul
pardeshirahul used Ask the Experts™
on
I have a query
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 gcc.segment5 INAND (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'))

                      ('10011',
                       '10012',
                       '10013',
                       '10014',
                       '10015',
                       '10016',
                       '10017',
                       '10018',
                       '10019',
                       '10021',
                       '10023',
                       '10024',
                       '10037',
                       '10036',
                       '10038',
                       '10039')
      ORDER BY glh.je_source;

this part i have to change
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'))

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

so to do it in decode
or do i have use case statement
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012

Commented:
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

Author

Commented:
when foreign currency i need to add 'USD', 'GBP'
Most Valuable Expert 2011
Top Expert 2012

Commented:
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
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2011
Top Expert 2012

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


what does that mean?  you listed one "when" but two results.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>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.

Author

Commented:
if foreign currency then take both like in  ( 'USD', 'GBP')
else if 'LOCAL_CURRENCY' then dont consider 'USD'
take all the currencies except 'USD'
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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')
)
...

Author

Commented:
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)

Author

Commented:
but it not working am i missing something
Most Valuable Expert 2011
Top Expert 2012

Commented:
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?

Author

Commented:
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;
Most Valuable Expert 2011
Top Expert 2012
Commented:
>>> i was just pasting a small part of the query thats why there is AND

yes I know, but even within the full query you're still not doing anything with the CASE result.

you can't and in a boolean result from a case.


you'd have to do something like this...


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


or, better yet,  quit trying to use decode and case and just use the conditions as shown above by slightwv
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>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.
Most Valuable Expert 2011
Top Expert 2012

Commented:
it's not really a dynamic condition, it's simply an OR
Most Valuable Expert 2011
Top Expert 2012

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial