Home » SQL & PL/SQL » SQL & PL/SQL » MAX(DECODE (Oracle 8i, Windows ver 6.1)
MAX(DECODE [message #676719] Mon, 08 July 2019 21:56 Go to next message
kumarvk
Messages: 214
Registered: July 2004
Senior Member
Dear Experts,

I am using max(decode like this
MAX(decode(X.CHRG_CODE,'RTAX', X.CHRG_AMT,0)) RTAX,
max(decode(X.CHRG_CODE,'RFEE', X.CHRG_AMT,0)) RFEE,
Apart from RTAX, and RFEE I want to sum other chrg_codes and place it in others. How to do?

Need you expert comments on this.
Re: MAX(DECODE [message #676720 is a reply to message #676719] Tue, 09 July 2019 00:27 Go to previous messageGo to next message
Michel Cadot
Messages: 66692
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As you already know: test case, format, clear specification, feedback... in short OraFAQ Forum Guide.

[Updated on: Tue, 09 July 2019 00:28]

Report message to a moderator

Re: MAX(DECODE [message #676721 is a reply to message #676720] Tue, 09 July 2019 01:32 Go to previous messageGo to next message
kumarvk
Messages: 214
Registered: July 2004
Senior Member
/* Formatted on 09-Jul-19 02:32:22 PM (QP5 v5.115.810.9015) */
SELECT DISTINCT
DECODE (a.eng_co_name,
'', a.eng_last_name || ' ' || a.eng_first_name,
a.eng_co_name)
customer_name,
a.cont_no,
a.cont_date,
MAX (DECODE (X.CHRG_CODE, 'RTAX', X.CHRG_AMT, 0)) RTAX,
MAX (DECODE (X.CHRG_CODE, 'RFEE', X.CHRG_AMT, 0)) RFEE
FROM css_cont_hdr a, CSS_CONT_CHRG X
WHERE a.ref_no = x.cthd_ref_no
GROUP BY a.cont_no,
a.cont_date,
DECODE (a.eng_co_name,
'', a.eng_last_name || ' ' || a.eng_first_name,
a.eng_co_name)
Re: MAX(DECODE [message #676722 is a reply to message #676721] Tue, 09 July 2019 01:32 Go to previous messageGo to next message
kumarvk
Messages: 214
Registered: July 2004
Senior Member
/* Formatted on 09-Jul-19 02:32:22 PM (QP5 v5.115.810.9015) */
  SELECT   DISTINCT
           DECODE (a.eng_co_name,
                   '', a.eng_last_name || ' ' || a.eng_first_name,
                   a.eng_co_name)
              customer_name,
           a.cont_no,
           a.cont_date,
           MAX (DECODE (X.CHRG_CODE, 'RTAX', X.CHRG_AMT, 0)) RTAX,
           MAX (DECODE (X.CHRG_CODE, 'RFEE', X.CHRG_AMT, 0)) RFEE
    FROM   css_cont_hdr a, CSS_CONT_CHRG X
   WHERE   a.ref_no = x.cthd_ref_no
GROUP BY   a.cont_no,
           a.cont_date,
           DECODE (a.eng_co_name,
                   '', a.eng_last_name || ' ' || a.eng_first_name,
                   a.eng_co_name)
Re: MAX(DECODE [message #676723 is a reply to message #676722] Tue, 09 July 2019 02:03 Go to previous messageGo to next message
Michel Cadot
Messages: 66692
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL>   SELECT   DISTINCT
  2             DECODE (a.eng_co_name,
  3                     '', a.eng_last_name || ' ' || a.eng_first_name,
  4                     a.eng_co_name)
  5                customer_name,
  6             a.cont_no,
  7             a.cont_date,
  8             MAX (DECODE (X.CHRG_CODE, 'RTAX', X.CHRG_AMT, 0)) RTAX,
  9             MAX (DECODE (X.CHRG_CODE, 'RFEE', X.CHRG_AMT, 0)) RFEE
 10      FROM   css_cont_hdr a, CSS_CONT_CHRG X
 11     WHERE   a.ref_no = x.cthd_ref_no
 12  GROUP BY   a.cont_no,
 13             a.cont_date,
 14             DECODE (a.eng_co_name,
 15                     '', a.eng_last_name || ' ' || a.eng_first_name,
 16                     a.eng_co_name)
 17  /
    FROM   css_cont_hdr a, CSS_CONT_CHRG X
                           *
ERROR at line 10:
ORA-00942: table or view does not exist
Re: MAX(DECODE [message #676724 is a reply to message #676723] Tue, 09 July 2019 04:05 Go to previous messageGo to next message
cookiemonster
Messages: 13726
Registered: September 2008
Location: Rainy Manchester
Senior Member
Your problem is likely very simple if you use CASE instead of DECODE.
Re: MAX(DECODE [message #676744 is a reply to message #676719] Thu, 11 July 2019 03:02 Go to previous messageGo to next message
GeorgeHenry
Messages: 1
Registered: April 2019
Junior Member
Is this what you want?

SUM(case when (X.CHRG_CODE != 'RTAX') && (X.CHRG_CODE != 'RFEE') then X.CHRG_AMT else 0 end) OTHERS
Re: MAX(DECODE [message #676747 is a reply to message #676744] Thu, 11 July 2019 06:03 Go to previous message
cookiemonster
Messages: 13726
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's not valid syntax
Previous Topic: Trying to Report on Date Periods within a table
Next Topic: Different Values of RAWTOHEX in Oracle 12c and 11g
Goto Forum:
  


Current Time: Thu Nov 21 23:12:08 CST 2019