If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > GROUP BY on DECODE

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #2 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,986
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 '' ?
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,986
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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On