Avatar of pardeshirahul
pardeshirahul
 asked on

need to convert decode to case

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
Oracle Database

Avatar of undefined
Last Comment
Sean Stuber

8/22/2022 - Mon
Sean Stuber

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
pardeshirahul

ASKER
when foreign currency i need to add 'USD', 'GBP'
Sean Stuber

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
Sean Stuber

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


what does that mean?  you listed one "when" but two results.
slightwv (䄆 Netminder)

>>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.
pardeshirahul

ASKER
if foreign currency then take both like in  ( 'USD', 'GBP')
else if 'LOCAL_CURRENCY' then dont consider 'USD'
take all the currencies except 'USD'
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
slightwv (䄆 Netminder)

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')
)
...
pardeshirahul

ASKER
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)
pardeshirahul

ASKER
but it not working am i missing something
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Sean Stuber

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?
pardeshirahul

ASKER
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;
ASKER CERTIFIED SOLUTION
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
slightwv (䄆 Netminder)

>>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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Sean Stuber

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

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