Home » Developer & Programmer » Reports & Discoverer » Displaying zero if there is record
Displaying zero if there is record [message #265298] Thu, 06 September 2007 01:51 Go to next message
pravin9
Messages: 42
Registered: May 2007
Member
Hi,
I am taking two fields from a query as count(empno) sum(sal)
and i display these in my layout

with group by as
0-1000
1001-2000
2001-3000
but if there are no records in 2001-3000 group it should display the value as zero it means if it does not fetch any value from the database

Thanks,
Praveen
Re: Displaying zero if there is record [message #265354 is a reply to message #265298] Thu, 06 September 2007 03:05 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you try
SELECT COUNT(empno), NVL(SUM(sal), 0)
Re: Displaying zero if there is record [message #265365 is a reply to message #265354] Thu, 06 September 2007 03:21 Go to previous messageGo to next message
pravin9
Messages: 42
Registered: May 2007
Member
Hi,
Its not a null value in the database.there are no records fetched
from the database so i can't use nvl

Regards,
Praveen
Re: Displaying zero if there is record [message #265397 is a reply to message #265365] Thu, 06 September 2007 04:38 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Does your "no records fetched" mean NO-DATA-FOUND error?

Look at this piece of code:
SQL> select max(sal) from emp;

  MAX(SAL)
----------
      5000

SQL> select count(empno) cnt, nvl(sum(sal), 0) sum_sal
  2  from emp
  3  where sal > 5000;

       CNT    SUM_SAL
---------- ----------
         0          0

SQL>
Although there are no employees whose salary is higher than 5000, NVL will return 0 (zero).

How does your code look like? Please, use the copy-paste technique so that we could see what you are doing (if possible). Or, attach a RDF based on Scott's schema.
Re: Displaying zero if there is record [message #265419 is a reply to message #265397] Thu, 06 September 2007 05:17 Go to previous messageGo to next message
pravin9
Messages: 42
Registered: May 2007
Member
Hi,
SELECT SUM(SAL),COUNT(EMPNO),JOB,
CASE
WHEN (SAL BETWEEN 0 AND 1000) THEN '0-1000'
WHEN (SAL BETWEEN 1001 AND 2000) THEN '0-2000'
WHEN (SAL BETWEEN 2001 AND 3000) THEN '0-3000'
WHEN (SAL BETWEEN 3001 AND 4000) THEN '0-4000'
WHEN (SAL BETWEEN 4001 AND 5000) THEN '0-5000'
WHEN (SAL BETWEEN 5001 AND 6000) THEN '0-6000'
when (sal between 6001 and 7000) then '6001-7000'
END,
CASE WHEN (JOB='CLERK') THEN 'A'
WHEN (JOB='MANAGER') THEN 'B'
WHEN (JOB='ANALYST')THEN 'C'
WHEN (JOB='PRESIDENT') THEN 'D'
WHEN (JOB='SALESMAN') THEN 'E'
END
FROM EMP
GROUP BY
JOB,
CASE
WHEN (SAL BETWEEN 0 AND 1000) THEN '0-1000'
WHEN (SAL BETWEEN 1001 AND 2000) THEN '0-2000'
WHEN (SAL BETWEEN 2001 AND 3000) THEN '0-3000'
WHEN (SAL BETWEEN 3001 AND 4000) THEN '0-4000'
WHEN (SAL BETWEEN 4001 AND 5000) THEN '0-5000'
WHEN (SAL BETWEEN 5001 AND 6000) THEN '0-6000'
when (sal between 6001 and 7000) then '6001-7000'
END,
CASE WHEN (JOB='CLERK') THEN 'A'
WHEN (JOB='MANAGER') THEN 'B'
WHEN (JOB='ANALYST')THEN 'C'
WHEN (JOB='PRESIDENT') THEN 'D'
WHEN (JOB='SALESMAN') THEN 'E'
END
/
now there is no value for sal between 6001 and 7000 for that it should display as zero and zero
for remaining the data that exists

Thanks,
Praveen
Re: Displaying zero if there is record [message #265589 is a reply to message #265419] Thu, 06 September 2007 12:17 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Can't you do:

NVL(CASE
WHEN (SAL BETWEEN 0 AND 1000) THEN '0-1000'
WHEN (SAL BETWEEN 1001 AND 2000) THEN '0-2000'
WHEN (SAL BETWEEN 2001 AND 3000) THEN '0-3000'
WHEN (SAL BETWEEN 3001 AND 4000) THEN '0-4000'
WHEN (SAL BETWEEN 4001 AND 5000) THEN '0-5000'
WHEN (SAL BETWEEN 5001 AND 6000) THEN '0-6000'
when (sal between 6001 and 7000) then '6001-7000'
END,0)

?
Re: Displaying zero if there is record [message #265680 is a reply to message #265589] Fri, 07 September 2007 00:26 Go to previous messageGo to next message
pravin9
Messages: 42
Registered: May 2007
Member
Hi,
I repeat there is no record in the database so it is not a null value to use nvl function
Thanks,
Praveen
Re: Displaying zero if there is record [message #265681 is a reply to message #265680] Fri, 07 September 2007 00:34 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Ah okay, got it. In that case, you first have to "generate" dummy records for those descriptions (like '0-1000','0-2000', etc) and outerjoin the results to that generated list.
Check asktom.com, there is a nice example generating records in a view with use of level and connect by.

Basically, you'll end up with something like:

SELECT
FROM (select statement for emps and range the sals fall in)
,    (generate required ranges)
WHERE generates_range = sal_range (+)


If you provide us with create table and insert scripts, perhaps someone will have try to create an example script for you.
Re: Displaying zero if there is record [message #265689 is a reply to message #265681] Fri, 07 September 2007 01:04 Go to previous messageGo to next message
pravin9
Messages: 42
Registered: May 2007
Member
Hi,
SUM(SAL) COUNT(EMPNO) JOB NVL(CASEW C
--------- ------------ --------- --------- -
1750 2 CLERK 0-1000 A
2400 2 CLERK 0-2000 A
6000 2 ANALYST 0-3000 C
8275 3 MANAGER 0-3000 B
5600 4 SALESMAN 0-2000 E
5000 1 PRESIDENT 0-5000 D
0 0 clerk 5001-6000

as such coz there is no record in the database for that range
that is how it should come
And this i will be using in report with job as the coloumn and sal range as row col

Thanks,
Praveen
Re: Displaying zero if there is record [message #265697 is a reply to message #265689] Fri, 07 September 2007 01:23 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Yes, I got it (like stated in my previous post). If you provide create table and insert scripts, someone might want to try to create an example script for you.
Re: Displaying zero if there is record [message #265726 is a reply to message #265697] Fri, 07 September 2007 03:11 Go to previous messageGo to next message
pravin9
Messages: 42
Registered: May 2007
Member
Hi,
It runs this way
create table oe_order_lines_all(industry_attribute7 number,liability amt number)
and i cant modify my tables i am using oracle 11i and i want to display these in output using reports
I am attaching output how i need
i am extracting from here using the above query
Re: Displaying zero if there is record [message #265727 is a reply to message #265726] Fri, 07 September 2007 03:17 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is OraFAQ Forum Guide. Read it to find what it says about DOC (and similar) files.
Re: Displaying zero if there is record [message #266056 is a reply to message #265727] Sun, 09 September 2007 06:48 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Praveen,

You're using Oracle 11i?? Cool, since 11g has been released just a couple of weeks ago and hasn't even has it's official launch. So either you're ahead of the troops, or you mean something else. Based on the tablename you give, I guess you mean that you're working on Oracle eBusiness Suite 11 something.

So, one last attempt to make clear to you what we need to be able to help you:
- the oracle database version (up or close to 4 decimals)
- perhaps the oracle EBS version (up to 4 decimals)
- a create table script (not just a describe)
- insert script for the source data (we've seen you're desired result now a couple of times, but I need the source data)
- like stated by LF, please don't use DOC or other not-so-reliable formats for attachments.

I do have access to an EBS environment and I'm willing to help, but if you don't bother to provide us with the data we need, then I don't bother about helping you.

Regards,
Sabine
Previous Topic: Document incorrect format
Next Topic: Printing reports in the required sequence (merged by LF)
Goto Forum:
  


Current Time: Thu Dec 08 20:08:07 CST 2016

Total time taken to generate the page: 0.08553 seconds