Home » SQL & PL/SQL » SQL & PL/SQL » In need of code ideas (merged)
In need of code ideas (merged) [message #311640] Sat, 05 April 2008 06:14 Go to next message
senaubi_me
Messages: 24
Registered: February 2008
Junior Member
To whom it may concern;

I am a newbie on sql, have a little experience but no actual training and only know the basics. I need help on how i can start my code on my report. I needed to create a report in this format:
A Code	SR#	Sales ID	Cash Amount	CPF Amount	Cust Name	Branch
101	1-12345	1-A111	0	        100,000	        Allan	        North
101	1-12346	1-A112	50,000	        50,000	        Cora	        South
101	1-12347	1-A113	99,000	        0	        Brian     	West

Total Amount		149,000		150,000		
Total # of records				3		

120	1-10001	1-A555	0		100,000		Allan		North
120	1-10002	1-A556	10,000		150,000		Cora		South
120	1-10003	1-A557	20,000		0		Brian		West
120	1-10004	1-A558	30,000		120,000		Kay		West
120	1-10005	1-A559	50,000		50,000		Leah		East

Total Amount		110,000		420,000		
Total # of records				5		

150	1-50001	1-A522	0		100,000		Allan		North
150	1-50002	1-A523	10,000		150,000		Cora		South
150	1-50003	1-A524	20,000		0		Brian		West
150	1-50004	1-A525	30,000		120,000		Kay		West
150	1-50005	1-A526	50,000		50,000		Leah		East
150	1-50005	1-A527	50,000		50,000		Michelle		East

Total Amount		160,000		470,000		
Total # of records				6		

Grand Amount		419,000		1,040,000		
Grand total # of records			14		


This is my idea to start my report, I created hidden columns Total Amount to sum up the Cash Amount and CPF Amount and Total Number of records which counts the number of records. But to display i used a page break option. So what happens is that the next set of records is printed on the next page. I tried my code to have this logic but it does not display what i wanted and is way to far with my expected output. What i needed is for the records to be displayed in the same page and will only break if it will not fit in the page so next records will be on the next page. I really had a hard time with this one and i hope you can help me.

I really appreciate it!

Thanks,
Jen


[Mod-edit: Frank added code tags]

[Updated on: Sat, 05 April 2008 09:22] by Moderator

Report message to a moderator

Re: In need of code ideas [message #311644 is a reply to message #311640] Sat, 05 April 2008 07:01 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
What are you using to write the report? SQL*Plus?

Ross Leishman
Re: In need of code ideas [message #311751 is a reply to message #311644] Sun, 06 April 2008 09:22 Go to previous messageGo to next message
senaubi_me
Messages: 24
Registered: February 2008
Junior Member
Hi Ross,

Yes, i am using sql plus. Hope you can help me.

Thanks,
Jen
Re: In need of code ideas [message #311753 is a reply to message #311751] Sun, 06 April 2008 09:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
Please see the following section of the online SQL*Plus User's Guide about report formatting:

http://download.oracle.com/docs/cd/B28359_01/server.111/b31189/ch6.htm#i1081008

If you are still having problems, then please post your code and the results that it produces. Please see the forum guidelines highlighted at the top of the forum page for complete posting guidelines.
Re: In need of code ideas [message #311759 is a reply to message #311751] Sun, 06 April 2008 11:35 Go to previous messageGo to next message
senaubi_me
Messages: 24
Registered: February 2008
Junior Member
so far here is what i have in my code that prints the folowing on the output:

BREAK ON REPORT
BREAK ON A_CODE SKIP 2 ON DUMMYTOTAL ON DUMMYSUM ON DUMMYSUM2
COMPUTE NUMBER LABEL 'Total No. of records: ' OF SR_NUM ON DUMMYTOTAL
COMPUTE SUM LABEL '$' OF CASH_AMNT ON DUMMYSUM
COMPUTE SUM LABEL '$' OF CPF_AMNT ON DUMMYSUM2

and please see the attachment output.csv with my current and needed output, there is a lot of diffrence like i do not know how i can print or how i can put the word total amount, my total amounts appears on different lines, and i do not know how to put the Grand Total Amount at the very end of the record.

I really appreciate your help.

Thanks!
  • Attachment: Output.csv
    (Size: 1.63KB, Downloaded 105 times)
Re: In need of code ideas [message #311784 is a reply to message #311759] Sun, 06 April 2008 16:50 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
You are confusing LABEL with NUMFORMAT or TO_CHAR. You need to use NUMFORMAT or TO_CHAR to get the $ and LABEL to get the 'Total Amount'.

You have omitted an important part of your code, the select statement. In order to COMPUTE, you must ORDER BY and BREAK ON what you are going to COMPUTE on.

When you use the BREAK command, each BREAK command replaces the previous one, so if you want to BREAK on multiple things, then you need to include them all in one BREAK command.

Please see the code below that demonstrates the above, then apply these principles to your code.

SCOTT@orcl_11g> CLEAR
SCOTT@orcl_11g> SET NUMFORMAT $999,999
SCOTT@orcl_11g> BREAK ON deptno ON report
SCOTT@orcl_11g> COMPUTE SUM LABEL 'Total Amount' OF sal ON deptno
SCOTT@orcl_11g> COMPUTE SUM LABEL 'Grand Total' OF sal ON report
SCOTT@orcl_11g> SELECT deptno, ename, sal
  2  FROM   emp
  3  ORDER  BY deptno
  4  /

       DEPTNO ENAME            SAL
------------- ---------- ---------
           10 CLARK         $2,450
              KING          $5,000
              MILLER        $1,300
*************            ---------
Total Amount                $8,750
           20 JONES         $2,975
              FORD          $3,000
              ADAMS         $1,100
              SMITH           $800
              SCOTT         $3,000
*************            ---------
Total Amount               $10,875
           30 WARD          $1,250
              TURNER        $1,500
              ALLEN         $1,600
              JAMES           $950
              BLAKE         $2,850
              MARTIN        $1,250
*************            ---------
Total Amount                $9,400
                         ---------
Grand Total                $29,025

14 rows selected.

SCOTT@orcl_11g> 

Re: In need of code ideas [message #311785 is a reply to message #311759] Sun, 06 April 2008 16:56 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
Also, for your total number of records, you need to COMPUTE the COUNT, not NUMBER. NUMBER is just a datatype.
Re: In need of code ideas [message #312115 is a reply to message #311785] Tue, 08 April 2008 00:44 Go to previous messageGo to next message
senaubi_me
Messages: 24
Registered: February 2008
Junior Member
Hi Barbara,

Thanks with your help because now my report looks better. But i would like to ask another question, in your example we break on dept no on report, with that duplicate values were remove. Is it possible that i will not suppress the values? the outputi wanted was dept number to appear repeatedly. And i have an option that when no records is found it should display "no records were found" and it does appear when no records found, but the label and sum for the total amount still appears. I do not need that if no values were returned. How can i remove that?

Thanks a lot! I really appreciate it.

JEN
Re: In need of code ideas [message #312128 is a reply to message #312115] Tue, 08 April 2008 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is it possible that i will not suppress the values

BREAK ON deptno dup ON report

Regards
Michel
How to remove repeated header and print "No records" [message #312235 is a reply to message #311640] Tue, 08 April 2008 04:53 Go to previous messageGo to next message
senaubi_me
Messages: 24
Registered: February 2008
Junior Member
Hi,

I have a problem with my output report it is repeating the header when no records was found. And does anyone knows how i can put "No records found" (when no record is found) in the output without the Total amount and Grand Total? Please refer to the attachent file for reference.

Thanks a lot! Hope someone can help me.

Cheers,
Jen
Re: How to remove repeated header and print "No records" [message #312244 is a reply to message #312235] Tue, 08 April 2008 05:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't repeat the question you posted in another topic.
Above all when you're refering some elements of this topic without explaining it in the current one.

Regards
Michel
How to remove repeated header and print "No records" [message #312262 is a reply to message #311640] Tue, 08 April 2008 05:41 Go to previous messageGo to next message
senaubi_me
Messages: 24
Registered: February 2008
Junior Member
Hi,

I have a problem with my output report it is repeating the header when no records was found. And does anyone knows how i can put "No records found" (when no record is found) in the output without the Total amount and Grand Total? Please refer to the attachent file for reference.

Thanks a lot! Hope someone can help me.

Cheers,
Jen
  • Attachment: text.txt
    (Size: 5.57KB, Downloaded 120 times)
Re: How to remove repeated header and print "No records" [message #312264 is a reply to message #312262] Tue, 08 April 2008 05:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Stop creating new topics with the same question.

Regards
Michel

[Updated on: Tue, 08 April 2008 05:47]

Report message to a moderator

Re: How to remove repeated header and print "No records" [message #312268 is a reply to message #312235] Tue, 08 April 2008 05:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL*Plus is a very old and rustic tool.
It can do many things but it can't do every thing.
What you ask can't be done with SQL*Plus.
You now have to do what SQL*Plus gives or chooses another tool (maybe write your own).

Regards
Michel
Re: How to remove repeated header and print "No records" [message #312273 is a reply to message #312268] Tue, 08 April 2008 06:24 Go to previous messageGo to next message
senaubi_me
Messages: 24
Registered: February 2008
Junior Member
Hi Michel,

Is it really impossible for sql to do that? Im just having a hard time figuring it out all day that is why i wnated to make sure. I really appreciate your help.

Cheers,
Jen
Re: How to remove repeated header and print "No records" [message #312281 is a reply to message #312273] Tue, 08 April 2008 07:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Afaik, it is not possible.

Regards
Michel
Re: How to remove repeated header and print "No records" [message #312285 is a reply to message #312281] Tue, 08 April 2008 07:33 Go to previous messageGo to next message
senaubi_me
Messages: 24
Registered: February 2008
Junior Member
Hi Michel,

Is there any document or reference that i can use as proof, that might help me. Thanks a lot.

Cheers,
Jen
Re: How to remove repeated header and print "No records" [message #312287 is a reply to message #312285] Tue, 08 April 2008 07:41 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL*PlusŪ User's Guide and Reference

Regards
Michel
Previous Topic: Union clause casuing the first query to show up on the bottom
Next Topic: Materialized view refresh
Goto Forum:
  


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

Total time taken to generate the page: 0.21555 seconds