Home » Developer & Programmer » Forms » NVL VS Group by
NVL VS Group by [message #686772] Sun, 01 January 2023 07:03 Go to next message
compuscience
Messages: 97
Registered: September 2012
Member
A RESULT OF 'NO ROWS SELECTED' APPEAR WHEN USING THE FOLLOWING SQL

SELECT SUM(NVL(EMP_NO,0)),REGION FROM EMP
WHERE REGION=1000
GROUP BY REGION;

NO ROWS SELECTED

BUT WHEN USING IT WITHOUT GROUP BY I GET

SELECT SUM(NVL(EMP_NO,0)) FROM EMP
WHERE REGION=1000;


SUM(NVL(EMP_NO,0))
------------------
0

WHAT CAN I DO TO GET 0 IF NO ROWS SELECTED BUT WHEN USING GROUP BY CONDITION?
Re: NVL VS Group by [message #686776 is a reply to message #686772] Sun, 01 January 2023 08:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68598
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What you say is not possible:
SQL> select sum(nvl(sal,0)), deptno from emp where deptno = 0 group by deptno;

no rows selected

SQL> select sum(nvl(sal,0)) from emp where deptno = 0;
SUM(NVL(SAL,0))
---------------


1 row selected.
Post a test case that shows you can have this result.

And ALWAYS post Your Oracle version, with 4 decimals.
And FORMAT your posts.
After 10 years you still don't know how to properly post!

[Updated on: Sun, 01 January 2023 08:39]

Report message to a moderator

Re: NVL VS Group by [message #686777 is a reply to message #686776] Sun, 01 January 2023 09:00 Go to previous messageGo to next message
compuscience
Messages: 97
Registered: September 2012
Member
Really i'm so sorry for that
I have a big problem with that case
This who made me write the problem faster to find the solution
Again i'm so sorry
Re: NVL VS Group by [message #686778 is a reply to message #686777] Sun, 01 January 2023 09:50 Go to previous messageGo to next message
Littlefoot
Messages: 21802
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If I understood you correctly, it might be a combination of a CTE, outer join and modified NVL usage that might help.

Sample data:
SQL> select * from t_emp order by region;

    REGION        SAL
---------- ----------
        10     2572.5
        10       5250
        10       1365
        20    3198.13
        20       3225
        20     1182.5
        20        860
        20       3225
        30       1375
        30       1650
        30       1760
        30       1045
        30       3135
        30       1375

14 rows selected.

SQL> select region, sum(sal) from t_emp group by region;

    REGION   SUM(SAL)
---------- ----------
        20   11690.63
        30      10340
        10     9187.5         --> remember this value

SQL>
This is a query which might help. Set region you're interested in into a CTE, and then outer join it to the "main" table (t_emp in my example):
SQL> with temp (region) as
  2    (select 1000 deptno from dual)
  3  select t.region,
  4         nvl(sum(e.sal), 0) c_sum
  5  from temp t left join t_emp e on t.region = e.region
  6  group by t.region;

    REGION      C_SUM
---------- ----------
      1000          0             --> there are no rows for region 1000 in T_EMP, so
                                      query returned a row whose sum is 0
If region exists (10 - the one I said you should remember):
SQL> with temp (region) as
  2    (select 10 deptno from dual)
  3  select t.region,
  4         nvl(sum(e.sal), 0) c_sum
  5  from temp t left join t_emp e on t.region = e.region
  6  group by t.region;

    REGION      C_SUM
---------- ----------
        10     9187.5

SQL>
Re: NVL VS Group by [message #686779 is a reply to message #686778] Mon, 02 January 2023 07:42 Go to previous messageGo to next message
compuscience
Messages: 97
Registered: September 2012
Member
Littlefoot wrote on Sun, 01 January 2023 17:50

This is a query which might help. Set region you're interested in into a CTE, and then outer join it to the "main" table (t_emp in my example):
SQL> with temp (region) as
  2    (select 1000 deptno from dual)
  3  select t.region,
  4         nvl(sum(e.sal), 0) c_sum
  5  from temp t left join t_emp e on t.region = e.region
  6  group by t.region;

    REGION      C_SUM
---------- ----------
      1000          0             --> there are no rows for region 1000 in T_EMP, so
                                      query returned a row whose sum is 0
SQL>
This is what i need
But how can i do it with two table in database not temp
Didn't work with me

[Updated on: Mon, 02 January 2023 07:44]

Report message to a moderator

Re: NVL VS Group by [message #686780 is a reply to message #686779] Mon, 02 January 2023 14:09 Go to previous message
Littlefoot
Messages: 21802
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Which two tables in database? TEMP is - as I said - just a CTE, not real table. Do you know what a CTE is?

If it doesn't work for you, it works for me (as demonstrated) so - if you expect any help - you should post what you really have, what you really did and how Oracle exactly responded.
Previous Topic: Form flush or disappear
Next Topic: Debugging Constraint error on Forms 6i
Goto Forum:
  


Current Time: Thu Feb 29 23:35:26 CST 2024