1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how to group a query that is using decode function

Discussion in 'SQL PL/SQL' started by RIAZ, Aug 3, 2009.

  1. RIAZ

    RIAZ Forum Advisor

    Messages:
    54
    Likes Received:
    0
    Trophy Points:
    130
    how to group the query that is using decode() function.


    SELECT
    A ,sum(B) ,decode(....) AS C
    FROM
    T_ABC
    WHERE
    D = 'ABC'
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    use the same decode function in the group by clause.
     
  3. RIAZ

    RIAZ Forum Advisor

    Messages:
    54
    Likes Received:
    0
    Trophy Points:
    130
    Following query is not working :(

    Code (SQL):
    --------------------------------------------------------------------------------
    SELECT   gjh.period_name, gjh.je_source, gjl.description
    --,gjl.REFERENCE_3
             ,
             DECODE (gjl.description,
                     'Expense', (SELECT item_description
                                   FROM rcv_shipment_lines rsl,
                                        rcv_transactions rt
                                  WHERE rt.shipment_line_id = rsl.shipment_line_id
                                    AND rt.transaction_id = gjl.reference_3),
                     '-'
                    ) AS item_description,
             SUM (SUM (gjl.accounted_dr) - SUM (gjl.accounted_cr)) AS balance
    --,gjl.ACCOUNTED_DR
    --,gjl.ACCOUNTED_CR
    FROM     gl_code_combinations gcc, gl_je_headers gjh, gl_je_lines gjl
       WHERE gcc.segment4 = '42040002'
         AND gjl.code_combination_id = gcc.code_combination_id
         AND gjl.je_header_id = gjh.je_header_id
         AND TRUNC (gjl.effective_date) <= :p_date
         AND gjh.STATUS = 'P'
    GROUP BY gjh.period_name,
             gjh.je_source,
             gjl.description
    --,gjl.REFERENCE_3
    ,
             DECODE (gjl.description,
                     'Expense', (SELECT item_description
                                   FROM rcv_shipment_lines rsl,
                                        rcv_transactions rt
                                  WHERE rt.shipment_line_id = rsl.shipment_line_id
                                    AND rt.transaction_id = gjl.reference_3),
                     '-'
                    )
    ---------------------------------------------------------------------
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Subquery will not be alowed in the in the group by clause.

    Either use it along with the main query or apply group by over the Group by output.
     
  5. RIAZ

    RIAZ Forum Advisor

    Messages:
    54
    Likes Received:
    0
    Trophy Points:
    130
    Would you please guide me how to apply group by over the Group by output ???
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    There is another method. fro this.

    Code (SQL):

    SQL> SELECT DEPTNO ,SUM(SAL)
      2  FROM EMP
      3  GROUP BY DEPTNO;

        DEPTNO   SUM(SAL)
    ---------- ----------
            30       9400
            20      10875
            10       8750

    SQL> SELECT DEPTNO ,
      2         DECODE(E.DEPTNO ,10,'HR',(SELECT DNAME FROM DEPT D WHERE D.DEPTNO = E.DEPTNO)),
      3          SUM(SAL)
      4  FROM EMP E
      5  GROUP BY DEPTNO ,
      6        DECODE(E.DEPTNO ,10,'HR',(SELECT DNAME FROM DEPT D WHERE D.DEPTNO = E.DEPTNO));
          DECODE(E.DEPTNO ,10,'HR',(SELECT DNAME FROM DEPT D WHERE D.DEPTNO = E.DEPTNO))
                                    *
    ERROR at line 6:
    ORA-22818: subquery expressions NOT allowed here


    SQL> SELECT DEPTNO ,
      2          DECODE(E.DEPTNO ,10,'HUMAN RESOURCE',(SELECT MAX(DNAME) FROM DEPT D WHERE D.DEPTNO = E.DEPTNO)),
      3          SUM(SAL)
      4  FROM EMP E
      5  GROUP BY DEPTNO;

        DEPTNO DECODE(E.DEPTN   SUM(SAL)
    ---------- -------------- ----------
            30 SALES                9400
            20 RESEARCH            10875
            10 HUMAN RESOURCE       8750

    SQL>

     
    Here , Note the you dont need to mention DECODE in the group by clause...
     
  7. RIAZ

    RIAZ Forum Advisor

    Messages:
    54
    Likes Received:
    0
    Trophy Points:
    130
    nops :( it is not working.
     
  8. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Show us what you tried and prove that it is not working .. . ..
     
  9. RIAZ

    RIAZ Forum Advisor

    Messages:
    54
    Likes Received:
    0
    Trophy Points:
    130
    Code (SQL):
    SELECT   gjh.period_name, gjl.description,
             SUM ((gjl.accounted_dr) - (gjl.accounted_cr)) AS bal,
             DECODE (gjl.description,
                     'Expense', (SELECT item_description
                                   FROM rcv_shipment_lines rsl,
                                        rcv_transactions rt
                                  WHERE rt.shipment_line_id = rsl.shipment_line_id
                                    AND rt.transaction_id = gjl.reference_3),
                     '-'
                    ) AS item_description
        FROM gl_code_combinations gcc, gl_je_headers gjh, gl_je_lines gjl
       WHERE gcc.segment4 = '42040002'
         AND gjl.code_combination_id = gcc.code_combination_id
         AND gjl.je_header_id = gjh.je_header_id
         AND TRUNC (gjl.effective_date) <= :p_date
         AND gjh.STATUS = 'P'
    GROUP BY gjh.period_name, gjl.description
     
  10. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Are you getting the error

    Then its the problem with

    Code (SQL):
    SELECT ITEM_DESCRIPTION
    FROM rcv_shipment_lines rsl
    ,rcv_transactions rt
    WHERE rt.SHIPMENT_LINE_ID = rsl.SHIPMENT_LINE_ID
    AND rt.TRANSACTION_ID = gjl.REFERENCE_3
    This query return mulptiple records.
    Check the query. If so change the query so that it returns only One record.