Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Posts
    24

    Unanswered: decode in group by

    What is the best practice in a situation like this:

    I have a report query like this:
    SELECT
    decode(Pvt.Afflt_Wrtng_Cmpny_Cd,'-1',PC.WRTNG_CMPNY_CD,'-2',PC.WRTNG_CMPNY_CD,null,PC.WRTNG_CMPNY_CD,Pvt.Af flt_Wrtng_Cmpny_Cd) AS "Affiliate Writing Company Code",
    decode (PVT.Prdct_Invstmnt_Typ_Cd,'-1','VAR','-2','VAR',NULL,'VAR',PVT.Prdct_Invstmnt_Typ_Cd) AS "Product Investment Type code"
    AND sum(PC.Cmmssn_Amt) AS "Commission Amount"

    FROM
    Partner_Commissions PC,
    Product_Version_Type PVT
    WHERE
    PC.PRDCT_VRSN_TYP_KEY=PVT.PRDCT_VRSN_TYP_KEY
    AND PC.CMMSSN_STTS_KEY=CS.CMMSSN_STTS_KEY

    GROUP BY
    decode(Pvt.Afflt_Wrtng_Cmpny_Cd,'-1',PC.WRTNG_CMPNY_CD,'-2',PC.WRTNG_CMPNY_CD,null,PC.WRTNG_CMPNY_CD,Pvt.Af flt_Wrtng_Cmpny_Cd),
    decode (PVT.Prdct_Invstmnt_Typ_Cd,'-1','VAR','-2','VAR',NULL,'VAR',PVT.Prdct_Invstmnt_Typ_Cd)

    I am not sure is it good to have 'decode' in group by clause for performance reasons.

    I would really appreciate someone's advise on this.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It seems that, in a situation like this, you won't get anything as a result, but a compile error.

    This code is hard to read; it would be nice to enclose it into the CODE tags.

    Furthermore, there's AND keyword in the SELECT column list. WHERE clause contains table alias ("cs"), but there's no such table in the FROM clause.

    In order to know whether such a code (or any code) needs improvements, run it through TKPROF and you'll know. Also, check the EXPLAIN PLAN generated for the query.

    However, if the code requires such a construct (DECODE in a GROUP BY) and you can't avoid it, I guess there's nothing much you can do.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Your code

    Code:
    SELECT decode(Pvt.Afflt_Wrtng_Cmpny_Cd,'-1',PC.WRTNG_CMPNY_CD,
                                           '-2',PC.WRTNG_CMPNY_CD,
                                           null,PC.WRTNG_CMPNY_CD,
                                           Pvt.Af flt_Wrtng_Cmpny_Cd) AS "Affiliate Writing Company Code",
           decode (PVT.Prdct_Invstmnt_Typ_Cd,'-1','VAR',
                                             '-2','VAR',
                                             NULL,'VAR',
                                             PVT.Prdct_Invstmnt_Typ_Cd) AS "Product Investment Type code",
           sum(PC.Cmmssn_Amt) AS "Commission Amount"
    FROM   Partner_Commissions PC,
           Product_Version_Type PVT
    WHERE PC.PRDCT_VRSN_TYP_KEY=PVT.PRDCT_VRSN_TYP_KEY
    AND PC.CMMSSN_STTS_KEY=CS.CMMSSN_STTS_KEY
    GROUP BY decode(Pvt.Afflt_Wrtng_Cmpny_Cd,'-1',PC.WRTNG_CMPNY_CD,
                                           '-2',PC.WRTNG_CMPNY_CD,
                                           null,PC.WRTNG_CMPNY_CD,
                                           Pvt.Af flt_Wrtng_Cmpny_Cd),
           decode (PVT.Prdct_Invstmnt_Typ_Cd,'-1','VAR',
                                             '-2','VAR',
                                             NULL,'VAR',
                                             PVT.Prdct_Invstmnt_Typ_Cd);
    will be fine. Having the decode in the group by doesn't fire twice, it just tells oracle to group by the RESULTS of the decode and is a necessary thing when you are using grouping functions like sum. However, you have a major problem. In your where clause, you are referencing a table alias called "CS". Where is the table in the FROM section?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •