OraFAQ Forum: Reports & Discoverer » Displaying zero if there is record

Home » Developer & Programmer » Reports & Discoverer » Displaying zero if there is record

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

Displaying zero if there is record [message #265298]

Thu, 06 September 2007 01:51

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

Report message to a moderator

Re: Displaying zero if there is record [message #265354 is a reply to message #265298]

Thu, 06 September 2007 03:05

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Did you try

SELECT COUNT(empno), NVL(SUM(sal), 0)

Report message to a moderator

Re: Displaying zero if there is record [message #265365 is a reply to message #265354]

Thu, 06 September 2007 03:21

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

Report message to a moderator

Re: Displaying zero if there is record [message #265397 is a reply to message #265365]

Thu, 06 September 2007 04:38

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: Displaying zero if there is record [message #265419 is a reply to message #265397]

Thu, 06 September 2007 05:17

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

Report message to a moderator

Re: Displaying zero if there is record [message #265589 is a reply to message #265419]

Thu, 06 September 2007 12:17

skooman
Messages: 913
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)

Report message to a moderator

Re: Displaying zero if there is record [message #265680 is a reply to message #265589]

Fri, 07 September 2007 00:26

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

Report message to a moderator

Re: Displaying zero if there is record [message #265681 is a reply to message #265680]

Fri, 07 September 2007 00:34

skooman
Messages: 913
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.

Report message to a moderator

Re: Displaying zero if there is record [message #265689 is a reply to message #265681]

Fri, 07 September 2007 01:04

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

Report message to a moderator

Re: Displaying zero if there is record [message #265697 is a reply to message #265689]

Fri, 07 September 2007 01:23

skooman
Messages: 913
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.

Report message to a moderator

Re: Displaying zero if there is record [message #265726 is a reply to message #265697]

Fri, 07 September 2007 03:11

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

Attachment: NJ_Report_output.doc
(Size: 85.50KB, Downloaded 1716 times)

Report message to a moderator

Re: Displaying zero if there is record [message #265727 is a reply to message #265726]

Fri, 07 September 2007 03:17

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: Displaying zero if there is record [message #266056 is a reply to message #265727]

Sun, 09 September 2007 06:48

skooman
Messages: 913
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

Report message to a moderator

Previous Topic:	Document incorrect format
Next Topic:	Printing reports in the required sequence (merged by LF)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 19:34:09 CST 2024