SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 GROUP BY on DECODE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

motoflyboy
Starting Member

Australia
2 Posts

Posted - 05/14/2007 :  21:07:11  Show Profile  Reply with Quote
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.

Edited by - motoflyboy on 05/14/2007 23:15:39

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 05/14/2007 :  23:32:03  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
Go to Top of Page

motoflyboy
Starting Member

Australia
2 Posts

Posted - 05/14/2007 :  23:56:40  Show Profile  Reply with Quote
Pardon me, please pretend I never darkened your door.....

Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 05/15/2007 :  01:14:52  Show Profile  Visit jezemine's Homepage  Reply with Quote
no worries! try:

http://www.dbforums.com/forumdisplay.php?f=4
http://www.orafaq.com
http://forums.oracle.com



www.elsasoft.org
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 05/15/2007 :  06:17:13  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Make use of a derived table.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000