GROUP BY on DECODE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

GROUP BY on DECODE

Author

Topic

motoflyboy
Starting Member

2 Posts

Posted - 2007-05-14 : 21:07:11

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 SQL server, I am trying to migrate some access queries to work with web services and hence Xcelsius.

Cheers.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-14 : 23:32:03

there is no DECODE() in sql server. also the string concat operator is + in t-sql, not ||.

are you sure you aren't working in oracle? you might do better on an oracle forum. this forum is for sql server only.

www.elsasoft.org