Results 1 to 6 of 6
  1. #1
    Join Date
    May 2007
    Location
    Australia
    Posts
    4

    GROUP BY on DECODE

    Hi,I have two questions, firstly..
    I am hoping there is a way to GROUP BY on a concatenated output like the following decode etc..

    SELECT
    DECODE(PRIM_REASN,'','Reasons not entered',PRIM_REASN)
    || ' '
    ||SECDY_REASN
    || ' '
    || TERT_REASN) Downtime_Reasons,
    EVENT_DUR/60 Minutes

    FROM
    DOWNTIME_WEB
    WHERE
    DOWNTIME_WEB.TIMESTAMP>= (SELECT PRODN_SHIFT.START_DATIME
    FROM PRODN_SHIFT
    WHERE
    PRODN_SHIFT.START_DATIME <=sysdate
    AND PRODN_SHIFT.END_DATIME > sysdate)
    AND
    TRIM(DOWNTIME_WEB.STATUS_TEXT) = 'STOPPED'
    AND
    TRIM(DOWNTIME_WEB.WORK_CTR_NAME) = 'PACK2'
    ORDER BY DOWNTIME_WEB.EVENT_DUR

    I have tried to put the DECODE etc into the GROUP BY statement but I get an error - " not a valid GROUP BY clause"

    Secondly, is there a way of modifying value in field A based on the value in field B

    Erroneous example follows - in an effort to explain what I want to do (from above):

    DECODE(PRIM_REASN,'',(DECODE(EVENT_DUR,<200,"Low", >200,"High"),PRIM_REASN)
    ie:If PRIM_REASN is null I want EVENT_DUR to be tested to determine the new value for PRIM_REASN.
    Except that tthe above won't work and I don't know what the right function/argument should be..

    This is my first foray into ORACLE code, I am trying to migrate some access queries to work with web services and hence Xcelsius.


    Cheers.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,013
    When posting a question, it is a good habit to include Oracle database version you are working on.

    First of all, why do you need a GROUP BY clause anyway?

    As you didn't post the query which doesn't work, I'm just guessing; however, this would compile:
    Code:
    SELECT      DECODE (prim_reasn,
                        '', 'Reasons not entered',
                        prim_reasn
                       )
             || ' '
             || secdy_reasn
             || ' '
             || tert_reasn downtime_reasons,
             event_dur / 60 minutes
        FROM downtime_web
       WHERE downtime_web.TIMESTAMP >=
                (SELECT prodn_shift.start_datime
                   FROM prodn_shift
                  WHERE prodn_shift.start_datime <= SYSDATE
                    AND prodn_shift.end_datime > SYSDATE)
         AND TRIM (downtime_web.status_text) = 'STOPPED'
         AND TRIM (downtime_web.work_ctr_name) = 'PACK2'
    GROUP BY    DECODE (prim_reasn, '', 'Reasons not entered', prim_reasn)
             || ' '
             || secdy_reasn
             || ' '
             || tert_reasn,
             event_dur / 60
    ORDER BY downtime_web.event_dur
    As I don't have your schema, here's a working simple example (just to show you that - if correctly written - it really works):
    Code:
    SQL> SELECT   ename || ' ' || job
      2      FROM EMP
      3     WHERE deptno = 10
      4  GROUP BY ename || ' ' || job;
    
    ENAME||''||JOB
    --------------------
    KING PRESIDENT
    CLARK MANAGER
    MILLER CLERK
    As of your second question, you'll need to use the SIGN function, such as
    Code:
    SQL> SELECT ename, sal, DECODE(SIGN(sal - 2000), -1, 'Low', 'High') low_high
      2  FROM EMP;
    or, if your database version supports it, CASE:
    Code:
    SELECT ename, sal, CASE 
                         WHEN sal < 2000 THEN 'Low'
                         ELSE 'High'
                       END low_high
    FROM EMP;
    Both queries will give the same result:
    Code:
    ENAME             SAL LOW_
    ---------- ---------- ----
    SMITH             800 Low
    ALLEN            1600 Low
    WARD             1250 Low
    JONES            2975 High
    MARTIN           1250 Low
    BLAKE            2850 High
    Your query might look like this:
    Code:
    SELECT DECODE (prim_reasn,
                   '', DECODE (SIGN (event_dur - 200), -1, 'Low', 'High'),
                   prim_reasn
                  )
      FROM your_table;
    I'll leave the CASE to you.

    BTW, is it really DECODE(prim_reasn, '' ...? Should it, perhaps, be a NULL instead of '' ?

  3. #3
    Join Date
    May 2007
    Location
    Australia
    Posts
    4

    Talking

    Littlefoot you are a champ - thanks.
    I added the SIGN function, it was the perfect solution for part two of my problem...
    I have now tried to do the GROUP BY portion of this but get an error:

    'ORA-933 SQL not properly ended'


    SELECT
    (DECODE(PRIM_REASN,'',DECODE(SIGN(EVENT_DUR - 180),-1,'STOPS < 3 minutes','Reasons not entered'),PRIM_REASN)
    || ' '
    ||SECDY_REASN
    || ' '
    || TERT_REASN) Downtime_Reasons,
    EVENT_DUR/60 Minutes

    FROM
    DOWNTIME_WEB

    WHERE
    DOWNTIME_WEB.TIMESTAMP>= (SELECT PRODN_SHIFT.START_DATIME
    FROM PRODN_SHIFT
    WHERE
    PRODN_SHIFT.START_DATIME <=sysdate
    AND PRODN_SHIFT.END_DATIME > sysdate)
    AND
    TRIM(DOWNTIME_WEB.STATUS_TEXT) = 'STOPPED'
    AND
    TRIM(DOWNTIME_WEB.WORK_CTR_NAME) = 'PACK2'

    GROUP BY DECODE(PRIM_REASN,'',DECODE(SIGN(EVENT_DUR - 180),-1,'STOPS < 3 minutes','Reasons not entered'),PRIM_REASN)
    || ' '
    ||SECDY_REASN
    || ' '
    || TERT_REASN) ,
    SUM(EVENT_DUR/60 Minutes)

    ORDER BY DOWNTIME_WEB.EVENT_DUR


    Intention is to have one record for each unique value returned by
    (DECODE(PRIM_REASN,'',DECODE(SIGN(EVENT_DUR - 180),-1,'STOPS < 3 minutes','Reasons not entered'),PRIM_REASN)
    || ' '
    ||SECDY_REASN
    || ' '
    || TERT_REASN)

    and to sum up the relevant EVENT_DUR values.

    PS: I had NULL in the original version but the local DBA contractor said to use '' instead? It works this way so I left it - would this be hindering my GROUP BY statement?

    Cheers

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,013
    Don't GROUP BY SUM; this function requires you to group by everything else BUT SUM. Something like this:
    Code:
    SELECT   (   DECODE (prim_reasn,
                         '', DECODE (SIGN (event_dur - 180),
                                     -1, 'STOPS < 3 minutes',
                                     'Reasons not entered'
                                    ),
                         prim_reasn
                        )
              || ' '
              || secdy_reasn
              || ' '
              || tert_reasn
             ) downtime_reasons,
             SUM (event_dur / 60) minutes
        FROM downtime_web
       WHERE downtime_web.TIMESTAMP >=
                (SELECT prodn_shift.start_datime
                   FROM prodn_shift
                  WHERE prodn_shift.start_datime <= SYSDATE
                    AND prodn_shift.end_datime > SYSDATE)
         AND TRIM (downtime_web.status_text) = 'STOPPED'
         AND TRIM (downtime_web.work_ctr_name) = 'PACK2'
    GROUP BY    DECODE (prim_reasn,
                        '', DECODE (SIGN (event_dur - 180),
                                    -1, 'STOPS < 3 minutes',
                                    'Reasons not entered'
                                   ),
                        prim_reasn
                       )
             || ' '
             || secdy_reasn
             || ' '
             || tert_reasn
    ORDER BY downtime_web.event_dur

  5. #5
    Join Date
    May 2007
    Location
    Australia
    Posts
    4
    Sorry my bad - (I had tried what you suggested but I posted a 'work in progress version")
    That's exactly where I got to, but I get the error "ORA-979: not a GROUP BY expression"
    Even if I run the code you (littlefoot) pasted, I get the same error?
    Maybe a derived table? Will continue research.......thanks again for the advice.

  6. #6
    Join Date
    May 2007
    Location
    Australia
    Posts
    4
    Solved the problem - I deleted the ORDER BY clause and it worked, then I changed it to "ORDER by Minutes" and all was good.
    Thanks again for your help..

    Cheers.

Posting Permissions

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