OraFAQ Forum: SQL & PL/SQL » Decode statement

Home » SQL & PL/SQL » SQL & PL/SQL » Decode statement

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Decode statement [message #195900]

Tue, 03 October 2006 01:51

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

orausern
Messages: 826
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

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

Report message to a moderator

Re: Decode statement [message #196081 is a reply to message #195900]

Wed, 04 October 2006 01:26

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 05 19:46:32 CST 2024