Home » SQL & PL/SQL » SQL & PL/SQL » Decode statement
Decode statement [message #195900] Tue, 03 October 2006 01:51 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I have the following decode statement. Though I have used SUM(d.qty) but this is giving the error "not a single-group group function".
Please advice.

 SELECT DECODE(
               (SUM(sku.oh)-
                   NVL(
               (SELECT SUM(d.qty) FROM stsc.depdmdorder d WHERE d.parentordertype=6),
                      0))
                ,-1,SUM(sku.oh),
                    NVL(
           (SELECT SUM(d.qty) FROM stsc.depdmdorder d WHERE d.parentordertype=6)
                        ,0)
              )
 FROM stsc.sku sku;


If I replace the inner query with its value run idividually, it gives result.
  1   SELECT DECODE(
  2                 (SUM(sku.oh)-
  3                     NVL(
  4                 (38203419.1),
  5                        0))
  6                  ,-1,SUM(sku.oh),
  7                      NVL(
  8             (38203419.1)
  9                          ,0)
 10                )
 11*  FROM stsc.sku sku
SQL> /

DECODE((SUM(SKU.OH)-NVL((38203419.1),0)),-1,SUM(SKU.OH),NVL((38203419.1),0))
----------------------------------------------------------------------------
                                                                  38203419.1

But when I keep the inner query, it does give the error message "not a single-group group function".

Thanks,
Mona

[Updated on: Tue, 03 October 2006 03:29]

Report message to a moderator

Re: Decode statement [message #195933 is a reply to message #195900] Tue, 03 October 2006 03:48 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
I guess that is the expected behavior: in referring the group by functions, the rule is that in the SELECT clause you can refer to columns on which you are grouping, but NO OTHER COLUMNS...where in ur query you do refer to them and that causes error:


SUM(sku.oh)-
                   NVL(
               (SELECT SUM(d.qty) FROM stsc.depdmdorder d WHERE d.parentordertype=6)



and when u replace that inner query with a constant, you get in agreement with the rule, hence the error disappears..

so if u have the requirement that you must have the inner query, then you may have to recode your query..perhaps by having an inline view etc..



[Updated on: Tue, 03 October 2006 03:49]

Report message to a moderator

Re: Decode statement [message #195940 is a reply to message #195933] Tue, 03 October 2006 04:06 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Here the column I am referring is using the function SUM. So I do not need to do the grouping. This is returning only one row.

Thanks,
Mona
Re: Decode statement [message #196081 is a reply to message #195900] Wed, 04 October 2006 01:26 Go to previous message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Hi Mona

You sent me a private message, asking me to look into this, and I have the time, so ok.

What are You trying to achieve here?

You have the same select twice, that cannot be optimal.
Why do You have the inner select there, when it does not have any relation to the outer select?

Maybe this does not work on Your version (do not know what it is), but maybe something like this:

WITH a AS (SELECT NVL(SUM(d.qty),0) AS qty FROM stsc.depdmdorder d WHERE d.parentordertype=6)
, b AS (SELECT SUM(sku.oh) AS oh FROM stsc.sku sku)
SELECT DECODE( oh-qty ,-1, oh , qty )
FROM a,b


I hope I got the logic right?

Br

Kim Anthonisen
Previous Topic: creating backup copy of view
Next Topic: Materialized view with userenv('lang')
Goto Forum:
  


Current Time: Sat Dec 03 00:55:14 CST 2016

Total time taken to generate the page: 0.06177 seconds