To answer your original question: yes, either SQL*Plus or iSQL*Plus can be used to produce simple formatted reports. But, this is not the way that most organizations produce reports from Oracle. Usually a report-writing tool, like: Oracle Reports, Oracle BI Publisher, Crystal Reports, etc. is used for producing regular reports.

SQL*Plus and iSQL*Plus are a programmers command-line interface to Oracle, and are very useful for DBA tasks,and some programming activity but they are not ideal for producing reports.

0

CharleneS77Author Commented: 2007-12-01

I am making some progress. I'm trying to figure out how to format the top of the report as follows:
Patient Name: Patient ID:
Admission Date: Discharge Date:

Using SQL*Plus to produce formatted reports is possible, but not easy (as you have learned by now) and it introduces security risks. Most organizations use a reporting tool for this task, and only allow DBAs and developers the permission to use SQL*Plus.

0

CharleneS77Author Commented: 2007-12-06

I am close. I need only use one select statement so it is not printed twice. How do I get the correct Patient Name, Admission Date, and Discharge? I am able to get the correct name for the column, but I am not sure how to put this all into one query. My code is below. Here is my output:

Let me now update you that to build complex reports will all the formats you want in sql*plus may not be possible because it is not a reporting tool built for making good reports instead it is used as an application to interact with oracle database but i can try to help you if that is possible.

Just do the above change, and paste your modifed code and the output you are getting and also tell me what output do you need.

Thanks

0

CharleneS77Author Commented: 2007-12-06

I have changed PatientName to Name. The report is running three times because of the three queries. I am having a hard time combining this into one query.

With three separate queries, in SQL*Plus you will get three separate outputs. If you want just one output, you need to combine the three queries into one.

Trying to use SQL*Plus though to preduce carefully formatted reports is *NOT* what SQL*Plus does best! SQL*Plus is a command-line programming tool for Oracle. It works well for creating and modifiying tables, and for some data manipulation, but it is *NOT* primarily a report writer! Yes, it can do simple reports. But if you want carefully-formatted reports, you need to use a reporting tool, like: Oracle Reports, Oracle BI Publisher, Crystal Reports, etc.

0

CharleneS77Author Commented: 2007-12-07

>>>>With three separate queries, in SQL*Plus you will get three separate outputs. If you want just one output, you need to combine the three queries into one.

Right, the report is running three times because of the three queries. I am having a hard time combining this into one query.

>>>>Trying to use SQL*Plus though to preduce carefully formatted reports is *NOT* what SQL*Plus does best! SQL*Plus is a command-line programming tool for Oracle. It works well for creating and modifiying tables, and for some data manipulation, but it is *NOT* primarily a report writer! Yes, it can do simple reports. But if you want carefully-formatted reports, you need to use a reporting tool, like: Oracle Reports, Oracle BI Publisher, Crystal Reports, etc.

Thank you. You have mentioned this multiple times, and I should have responded to let you know that SQL*Plus is what I need to use for this report.

>>>>your second query does not have a where clause to join the three tables. hope you understand that with a join condition the output will be a cartersian join and the output will be a mess.

I do understand that if the joined columns are not specified in the where clause, it will result in a Cartesian product. All three of these tables do not have the same attributes. I don't know the proper way to join these tables.

>>>>i think u need to work first on getting a single query which can get the output you want. Then we can play with formatting stuff later.

Exactly, the single query is the part I am having a problem with. Here is my attempt:

I'm not familiar with the ANSI-standard join syntax ("join on...") but in standard Oracle query syntax whenever you use a "group" operator, like: "sum" on one or more columns, you *MUST* include every other column or column expression from the "select..." list that does not have a "group" operator in the "group by" clause. In your case, the concatenated FName and LName value (at least) is not repeated in the "group by", but it must be.

On the issue of why you "need to use" SQL*Plus for this report, whoever is paying for this work needs to be advised that trying to get this job done without buying a reporting tool may end up costing more (at least in time and frustration) than buying a tool that is designed for the job.

For building a house, I could say that the builder "needs to use" a hammer and hand saw only, but the job will not get done nearly as fast as if I allow him to use power tools.

0

CharleneS77Author Commented: 2007-12-07

There are no finances involved with this project. I am working on this in a learning environment, not a real-world environment. I am thankful for you pointing out the importance of (the reality of) reporting tools.

I am currently working on my query. The following query produces the right output (without the SUM).

>>>>in standard Oracle query syntax whenever you use a "group" operator, like: "sum" on one or more columns, you *MUST* include every other column or column expression from the "select..." list that does not have a "group" operator in the "group by" clause.

I will correct my error.

0

CharleneS77Author Commented: 2007-12-07

I have the query working except for SUM(Charge_Code). Any suggestions on what I need to change to SUM the Charge_Code?

Now, as for formatting, I would like to add the words "Summary Charges:" to the end of each Charge_Category. I would also like to add the words Total Charges before the total. Here is an example of what I would like to do:

i wanted to know whether this gets 1 record or 2 records which are duplicates. I want to know whether the report is getting duplicated because of data returned by the query or due to other formattting options.

I removed the pagesize statement to see if Total Charges would print at the bottom of the "second" report. For some reason, now the report is only running one time, even without the pagesize statement. So I guess I don't need to set the pagesize. What needs to changed to have the "Total Charges: " displayed?

Featured Post

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.