Start Free Trial

asked on

How do I create a simple formatted report?

I do not completely understand how to create a report. Is it a completely seperate file? What starts the report?

I am in the process of creating a formatted report. I get the following errors:

SP2-0158: unknown SET option "newpage"
SP2-0852: Option not available in iSQL*Plus
SP2-0850: Command "spool" is not available in iSQL*Plus
SP2-0850: Command "spool" is not available in iSQL*Plus

The following will go after spool billingSummary.lst:

select ChargeCatagory, Cost
from
order by

rem Patient Billing Summary
 
set headsep!
 
ttitle     'Patient Billing Summary'
btitle     'from HADA database'
 
column ChargeCatagory format a20
column Cost format a20 word_wrapped
 
break on ChargeCatagory skip 1 on report
 
set linesize 80
set pagesize 60
set newpage 0
set feedback off
 
spool billingSummary.lst
 
spool off

Open in new window

ASKER

This link shows how I am attempting to format my report:
https://filedb.experts-exchange.com/incoming/ee-stuff/5899-PatientBillingSummary.txthttps://filedb.experts-exchange.com/incoming/ee-stuff/5900-HADS.txt
https://filedb.experts-exchange.com/incoming/ee-stuff/5901-SQLPlus-Errrors.txt
.

try the same thing in SQL*plus not in iSQL*Plus

Thanks

may be they are not supported in isql*plus.

http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a90842/apd.htm

This url gives the list of commands which are not supported in isql*plus.

set newpage is one among them and hence not supported in isql*plus.

Try commenting out that line in your script and run to see the output as to how it looks without newpage.

Thanks

ASKER

When I ran my SQL in iSQLPlus, I was not getting any errors.

Now that I have run it in SQLPlus, I get the errors. You can see my SQL code and the errors at this link:
https://filedb.experts-exchange.com/incoming/ee-stuff/5899-PatientBillingSummary.txthttps://filedb.experts-exchange.com/incoming/ee-stuff/5900-HADS.txt
https://filedb.experts-exchange.com/incoming/ee-stuff/5901-SQLPlus-Errrors.txt
.

What is the difference? What needs to be changed?

>> references PATIENT_ADMISSION (Patient_ID)
THis line is giving the errors.

You should have a primary key or Unique key defined on PATIENT_ADMISSION (Patient_ID). This will solve all the errors.

without a primary key or unique key you cannot create a referential integrity relationship.

You can do it with the below command :

alter table PATIENT_ADMISSION constraint pk_patient_id_cons primary key ( patient_id );

Thanks

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.

ASKER

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:

rem Patient Billing Summary
 
set headsep!
 
ttitle     'Patient Billing Summary'
btitle     'from HADA database'
 
column PatientName heading 'Patient Name:'
column PatientID format 'Patient ID:'
column AdmissionDate 'Admission Date:'
column DischargeDate 'Discharge Date:'
 
column PatientName format a30
column PatientID format a6
column AdmissionDate format a12
column DischargeDate format a12
 
column ChargeCatagory format a20
column Cost format $999.99 
	
break on ChargeCatagory skip 1 on report
 
set linesize 80
set pagesize 60
set newpage 0
set feedback off
 
spool billingSummary.lst
 
select FName || ' ' || MI || ' '  || LName as Patient_Name
from PATIENT_DEMOGRAPHIC;
 
spool off

Open in new window

can you tell me how your output should look like with sample data ?

Just open the url and search for "COLUMN MANAGER_ID NEW_VALUE MGRVAR NOPRINT" without double quotes.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch6.htm#sthref1134

may be you want something like this.

Thanks

ASKER

Patient Billing Summary

Patient Name: John K Smith                  Patient ID: 111111
Admission Date: 04/02/2007                  Discharge Date: 04/09/2007

Charge Category                    Cost
Facility Summary Charges:            $6,000.00
Medicine Summary Charges:            $350.00
Clinical Test Summary Charges:        $600.00
Radiology Test Summary Charges:        $0.00
Pathology Summary Charges:            $0.00

            Total Charges:       $6950.00

ASKER

Sorry about the formatting of the cost column.

rem Patient Billing Summary

set headsep!

ttitle 'Patient Billing Summary'
btitle 'from HADS database'

column FName NOPRINT NEW_VALUE First_Name
column MI NOPRINT NEW_VALUE Middle_Initial
column LName NOPRINT NEW_VALUE Last_Name
column Patient_ID NOPRINT NEW_VALUE ID
column Admission_Date NOPRINT NEW_VALUE Admitted
column Discharge_Date NOPRINT NEW_VALUE Discharged

break on Charge_Category skip 1 on report
compute sum of Charge_Category on ID
compute sum of Charge_Category on report

LEFT 'Patient Name: ' First_Name || ' ' || Middle_Initial || ' ' ||
Last_Name-
RIGHT 'Patient ID: ' ID-
LEFT 'Admission Date: ' Admitted-
RIGHT 'Discharge Date: ' Discharged skip 2

ASKER

I'm still having a hard time with this. Any suggestions are appreciated.

REM Patient Billing Summary
 
SET ECHO ON
SET HEADSEP!
 
COLUMN LName NOPRINT NEW_VALUE LastName
COLUMN Patient_ID NOPRINT NEW_VALUE ID
 
TTITLE CENTER 'Patient Billing Summary' skip 2 -
LEFT 'Last Name: ' LastName -
RIGHT 'Patient ID: ' ID skip 2
BREAK ON Patient_ID skip page
BTITLE 'from HADS database'
 
SELECT Patient_ID
FROM PATIENT_ADMISSION
WHERE PATIENT_ID IN (111111)
ORDER BY Patient_ID;
 
SPOOL patientSummary.lst
 
SELECT Patient_ID
FROM PATIENT_ADMISSION
WHERE Patient_ID IN (111111)
ORDER BY Patient_ID;
 
SPOOL OFF

Open in new window

ASKER

I am not able to get the correct output for my report.

I need my output to be formatted as follows:

Patient Billing Summary

Patient Name: John K Smith Patient ID: 111111
Admission Date: 04/02/2007 Discharge Date: 04/09/2007

Charge Category Cost
Facility Summary Charges: $6,000.00
Medicine Summary Charges: $350.00
Clinical Test Summary Charges: $600.00
Radiology Test Summary Charges: $0.00
Pathology Summary Charges: $0.00

Total Charges: $6950.00

Here is what I have for my report so far:

REM Patient Billing Summary

SET HEADSEP!

COLUMN Name NOPRINT NEW_VALUE PatientName
COLUMN Patient_ID NOPRINT NEW_VALUE ID
COLUMN Adm_Date NOPRINT NEW_VALUE AdmissionDate
COLUMN Disc_Date NOPRINT NEW_VALUE DischargeDate

TTITLE CENTER 'Patient Billing Summary' skip 2 -
LEFT 'Patient Name: ' PatientName -
RIGHT 'Patient ID: ' ID skip 1 -
LEFT 'Admission Date: ' AdmissionDate -
RIGHT 'Discharge Date: ' DischargeDate skip 3

BREAK ON Patient_ID skip 2
BTITLE 'from HADS database'

SPOOL patientSummary.lst

select FName || ' ' || MI || ' ' || LName as Patient_Name, Patient_ID
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO;

select Adm_Date, Disc_Date
from PATIENT_LOCATION;

SPOOL OFF

create table PATIENT_DEMOGRAPHIC(
SSNO      		NUMBER NOT NULL,
LName		VARCHAR2(25) NOT NULL,     
MI                   	VARCHAR2(3),
FName           	VARCHAR2(25),
Street               	VARCHAR2(35),
City                 	VARCHAR2(25),
State                	VARCHAR2(2),
Zip                   	NUMBER,
DOB                	DATE NOT NULL,
Gender            	VARCHAR2(1) constraint check_Gender CHECK (Gender IN ('M', 'F')) NOT NULL,
Race                	VARCHAR2(3) constraint check_Race CHECK (Race IN ('AFR', 'WHI', 'API', 'HSP', 'OTH')),
constraint PATIENT_DEMOGRAPHIC_PK primary key (SSNO)
);
 
create table PATIENT_ADMISSION(
Patient_ID             	NUMBER NOT NULL,
SSNO  		NUMBER NOT NULL,     
Adm_Date            	TIMESTAMP NOT NULL,
Disc_Date            	DATE,
Adm_Type              VARCHAR2(2) constraint check_Adm_Type CHECK (Adm_Type IN ('IP', 'OP', 'TP', 'RP')),
constraint PATIENT_ADMISSION_PK primary key (Patient_ID),
constraint PATIENT_DEMOGRAPHIC_FK foreign key (SSNO)
references PATIENT_DEMOGRAPHIC (SSNO)
);
 
create sequence SEQ_PATIENT_ADMISSION_PK increment by 1 start with 111111;
 
create or replace trigger PATIENT_ADMISSION_TRIGGER
after insert or update on PATIENT_DEMOGRAPHIC
for each row
begin
      insert into PATIENT_ADMISSION (Patient_ID, SSNO, Adm_Date, Disc_Date, Adm_Type)
      values (SEQ_PATIENT_ADMISSION_PK.NextVal, :new.SSNO, SYSDATE, null, 'OP');
end;
/
 
show errors trigger PATIENT_ADMISSION_TRIGGER;
 
insert into PATIENT_DEMOGRAPHIC 
values ('999999999', 'Smith', 'K', 'John', '111 Baker Street', 'Smilesville', 'CA', '90833', '10-JUL-70', 'M', 'WHI');
 
create table PATIENT_ADMISSION_NEW as
select * from PATIENT_ADMISSION;
update PATIENT_ADMISSION_NEW
set Adm_Type = 'IP';
update PATIENT_ADMISSION pa1
set Adm_Type = (
select Adm_Type
from PATIENT_ADMISSION_NEW pa2
where pa1.patient_id = pa2.patient_id);
 
drop table PATIENT_ADMISSION_NEW;
 
update PATIENT_ADMISSION 
set Adm_Type = 'IP' 
where patient_id = 111111;
 
create table INSURANCE_COMPANY(
Comp_Code        	VARCHAR2(25) NOT NULL,
Comp_Name         	VARCHAR2(25),
Street                   	VARCHAR2(25),
City        		VARCHAR2(25),
State                     VARCHAR2(2),
Zip                     	NUMBER,
Phone                   	NUMBER,
Contact               	VARCHAR2(35),
constraint INSURANCE_COMPANY_PK primary key (Comp_Code)
);
 
create table PATIENT_INSURANCE(
Policy_Num            	VARCHAR2(20) NOT NULL,
Comp_Code           	VARCHAR2(20),
SSNO                    	NUMBER NOT NULL,
LName                 	VARCHAR2(25),
FName                	VARCHAR2(25),
Eff_Date              	DATE,
Exp_Date               	DATE,
Ins_Type         	VARCHAR2(3) constraint check_Ins_Type CHECK (Ins_Type IN ('PPO', 'HMO')),
Primary_Phy        	VARCHAR2(35),
constraint PATIENT_INSURANCE_PK primary key (Policy_Num, SSNO),
constraint INS_COMP_FK foreign key (Comp_Code)
references INSURANCE_COMPANY (Comp_Code),
constraint PATIENT_SSNO_FK foreign key (SSNO)
references PATIENT_DEMOGRAPHIC (SSNO)
);
 
insert into INSURANCE_COMPANY 
values ('JUP', 'Jupiter Insurance Inc', '111 Good Health Ave', 'Smilesville', 'MD', '20888', '3019998888', 'Ms. Patty Snyder');
 
insert into INSURANCE_COMPANY 
values ('GHI', 'Good Health Insurance Inc', '222 Pink Cheeks Drive', 'Healthville', 'NJ', '08956', '2028936784', 'Mr. Bob Simpson');
 
insert into PATIENT_INSURANCE 
values ('A0358F6', 'JUP', '999999999',  'Smith', 'John', '01-JAN-07', '31-DEC-07', 'PPO', 'Dr. Joseph Campbell');
 
create or replace trigger PPO_TRIGGER
before insert or update on PATIENT_INSURANCE
for each row
begin
	if :new.Ins_Type = 'PPO' and :new.Primary_Phy is null 
	then
	RAISE_APPLICATION_ERROR (
	-20001, 'Primary_Phy cannot be null when Ins_Type is PPO');
	end if;
end;
/
 
show errors trigger PPO_TRIGGER;
 
create table WARD_INFORMATION(
Ward_Code       	VARCHAR2(3) constraint check_Ward_Code CHECK (Ward_Code IN ('GWD', 'INC', 'CRD', 'ONC', 'OBG', 'PED')) NOT NULL,
Ward_Name        	VARCHAR2(25),
No_Beds              	NUMBER,
Location              	VARCHAR2(25),
constraint WARD_INFORMATION_PK primary key (Ward_Code)
);
 
create table PATIENT_LOCATION(
Patient_ID        	NUMBER NOT NULL,
Ward_Code            	VARCHAR2(3) NOT NULL,
Bed_Num            	NUMBER,
Adm_Date            	DATE,
Disc_Date            	DATE,
constraint PATIENT_LOCATION_PK primary key (Patient_ID, Ward_Code, Bed_Num, Adm_Date),
constraint PL_PATIENT_ID_FK foreign key (Patient_ID)
references PATIENT_ADMISSION (Patient_ID),
constraint WARD_FK foreign key (Ward_Code)
references WARD_INFORMATION (Ward_Code)
);
 
insert into WARD_INFORMATION 
values ('CRD', 'Cardiology', '30',  'Building 223');
 
insert into WARD_INFORMATION 
values ('ONC', 'Oncology', '20',  'Building 222');
 
insert into PATIENT_LOCATION 
values (SEQ_PATIENT_ADMISSION_PK.CurrVal, 'CRD', '3', '02-APR-07', '09-APR-07');
 
create table CHARGE_CODE(
Charge_Code        	VARCHAR2(3) NOT NULL,
Description            	VARCHAR2(30),
Charge_Category  	VARCHAR2(15),
constraint CHARGE_CODE_PK primary key (Charge_Code)
);
 
insert into CHARGE_CODE 
values ('RMS', 'Room Stay', 'Facility');
 
insert into CHARGE_CODE 
values ('SUP', 'Room Supplies', 'Facility');
 
insert into CHARGE_CODE 
values ('PRD', 'Prescription Drugs', 'Medicine');
 
insert into CHARGE_CODE 
values ('BLD', 'Blood Tests', 'Clinical Test');
 
insert into CHARGE_CODE 
values ('URI', 'Urine Tests', 'Clinical Test');
 
insert into CHARGE_CODE 
values ('XRA', 'X-Rays', 'Radiology Test');
 
insert into CHARGE_CODE 
values ('EKG', 'Electro Cardiogram', 'Radiology Test');
 
insert into CHARGE_CODE 
values ('MRI', 'Magnetic Resonance Imaging', 'Radiology Test');
 
insert into CHARGE_CODE 
values ('TIS', 'Tissue Tests', 'Pathology');
 
create table PATIENT_CHARGES(
Patient_ID            	NUMBER NOT NULL,
Charge_Code    	VARCHAR2(3),
Charge_Date        	TIMESTAMP,
Charge_Value       	NUMBER,
constraint PATIENT_CHARGES_PK primary key (Patient_ID, Charge_Code, Charge_Date),
constraint PC_PATIENT_ID_FK foreign key (Patient_ID)
references PATIENT_ADMISSION (Patient_ID),
constraint CHARGE_CODE_FK foreign key (Charge_Code)
references CHARGE_CODE (Charge_Code)
);
 
insert into PATIENT_CHARGES 
values (SEQ_PATIENT_ADMISSION_PK.CurrVal, 'RMS', '02-APR-07', '750');
 
insert into PATIENT_CHARGES 
values (SEQ_PATIENT_ADMISSION_PK.CurrVal, 'PRD', '04-APR-07', '350');
 
insert into PATIENT_CHARGES 
values (SEQ_PATIENT_ADMISSION_PK.CurrVal, 'EKG', '08-APR-07', '650');
 
update PATIENT_CHARGES set Charge_Value = ((to_Date('04092007','mmddyyyy') - to_Date(to_char(Charge_Date,'mmddyyyy'),'mmddyyyy')) + 1)  * Charge_Value 
where Charge_Code = 'RMS';
 
create table PATIENT_ADMISSION_NEW as
select * from PATIENT_ADMISSION;
update PATIENT_ADMISSION_NEW
set Disc_Date = SYSDATE;
update PATIENT_ADMISSION pa1
set Disc_Date = (
select Disc_Date
from PATIENT_ADMISSION pa2
where pa1. Disc_Date = pa2. Disc_Date);
 
drop table PATIENT_ADMISSION_NEW;
 
create or replace trigger PADM_DISC_DATE_TRIGGER
before insert or update on PATIENT_ADMISSION
for each row
begin
	if :new.Disc_Date > :new.Adm_Date 
	then
	RAISE_APPLICATION_ERROR (
	-20002, 'Disc_Date cannot come before Adm_Date');
	end if;
end;
/
 
show errors trigger PADM_DISC_DATE_TRIGGER;
 
create or replace trigger PLOC_DISC_DATE_TRIGGER
before insert or update on PATIENT_LOCATION
for each row
begin
	if :new.Disc_Date > :new.Adm_Date 
	then
	RAISE_APPLICATION_ERROR (
	-20003, 'Disc_Date cannot come before Adm_Date');
	end if;
end;
/
 
show errors trigger PLOC_DISC_DATE_TRIGGER;
 
create or replace trigger PATIENT_LOCATION_TRIGGER
after update on PATIENT_ADMISSION
for each row
when (new.Disc_Date is not null)
begin
	update PATIENT_LOCATION
  	set Disc_Date = :new.Disc_Date
  	where Patient_Id = :new.Patient_Id;
end;
/
 
show errors trigger PATIENT_LOCATION_TRIGGER;
 
create table ALL_PATIENTS(
PatientSSNO        	NUMBER NOT NULL,
PatientLName        	VARCHAR2(25) NOT NULL,
PatientMI               	VARCHAR2(3),
PatientFName         	VARCHAR2(25),
PatientStreet   	VARCHAR2(35),
PatientCity              VARCHAR2(25),
PatientState            VARCHAR2(2),
PatientZip           	NUMBER,
PatientDOB             DATE NOT NULL,
PatientGender      	VARCHAR2(1),
PatientRace      	VARCHAR2(3),
PatientAdm_Date  	TIMESTAMP NOT NULL,
constraint ALL_PATIENTS_PK primary key (PatientSSNO, PatientAdm_Date)
);
 
create or replace trigger ALL_PATIENTS_TRIGGER
after insert or update on PATIENT_DEMOGRAPHIC
for each row
begin
	insert into ALL_PATIENTS
      	(PatientSSNO, PatientLName, PatientMI, PatientFName, PatientStreet, PatientCity,
	PatientState, PatientZip, PatientDOB, PatientGender, PatientRace)
	values 
	(:new.SSNO, :new.LName, :new.MI, :new.FName, :new.Street, :new.City,
	:new.State, :new.Zip, :new.DOB, :new.Gender, :new.Race);
end;
/
 
show errors trigger ALL_PATIENTS_TRIGGER;
 
insert into ALL_PATIENTS 
values ('999999999', 'AllLName', 'ALL', 'AllFName', 'AllStreet', 'AllCity',  'AS', '99999', '05-APR-79', 'F', 'OTH', SYSDATE);
 
select * from PATIENT_DEMOGRAPHIC;
select * from PATIENT_ADMISSION;      
select * from INSURANCE_COMPANY;
select * from PATIENT_INSURANCE;
select * from WARD_INFORMATION;
select * from PATIENT_LOCATION;
select * from CHARGE_CODE;
select * from PATIENT_CHARGES;
select * from ALL_PATIENTS;

Open in new window

what is the current output you are getting that which is incorrect ?

Also if you need total or aggregate like avg, sum, etc.. then you should also use

put this after your break on ... statement

compute sum of cost on patient_id

Also when you are using break/compute, your queries should have order by. In your case, it should be order by patient_id because that is the one which we have used in break/compute.

Thanks

ASKER

Here is my most recent output:

Patient Billing Summary

Patient Name: PatientName Patient ID: 111111
Admission Date: 02-APR-07 Discharge Date: 09-APR-07

PATIENTNAME
-------------------------------------------------------
John K Smith

from HADS database

>>>>Also when you are using break/compute, your queries should have order by.

I don't fully understand how to use Break to compute the totals. Where do these queries go?

I cannot figure out what I am doing wrong for Patient Name.
Also, I am not sure how to format the bottom of the report (Charge Categories).

Thank you


REM Patient Billing Summary
 
SET HEADSEP!
 
COLUMN Name NOPRINT NEW_VALUE PatientName
COLUMN Patient_ID NOPRINT NEW_VALUE ID
COLUMN Adm_Date NOPRINT NEW_VALUE AdmissionDate
COLUMN Disc_Date NOPRINT NEW_VALUE DischargeDate
 
TTITLE CENTER 'Patient Billing Summary' skip 2 -
LEFT 'Patient Name: ' PatientName -
RIGHT 'Patient ID: ' ID skip 1 -
LEFT 'Admission Date: ' AdmissionDate -
RIGHT 'Discharge Date: ' DischargeDate skip 3
 
BREAK ON Patient_ID skip 2
BTITLE 'from HADS database'
 
COLUMN Charge_Catagory format a30
COLUMN Cost format $9,999.99
 
SPOOL patientSummary.lst
 
select FName || ' ' || MI || ' '  || LName as PatientName,
PATIENT_LOCATION.Patient_ID, PATIENT_LOCATION.Adm_Date,
PATIENT_LOCATION.Disc_Date
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION;
 
SPOOL OFF

Open in new window

Do the below suggested code and the suggestions as well and then paste what output you are getting.

REM Patient Billing Summary
 
SET HEADSEP!
 
COLUMN Name NOPRINT NEW_VALUE PatientName
COLUMN Patient_ID NOPRINT NEW_VALUE ID
COLUMN Adm_Date NOPRINT NEW_VALUE AdmissionDate
COLUMN Disc_Date NOPRINT NEW_VALUE DischargeDate
 
TTITLE CENTER 'Patient Billing Summary' skip 2 -
LEFT 'Patient Name: ' PatientName -
RIGHT 'Patient ID: ' ID skip 1 -
LEFT 'Admission Date: ' AdmissionDate -
RIGHT 'Discharge Date: ' DischargeDate skip 3
 
BREAK ON Patient_ID skip 2
compute sum of cost on patient_id -- but your select query below should select cost also
BTITLE 'from HADS database'
 
COLUMN Charge_Catagory format a30
COLUMN Cost format $9,999.99
 
SPOOL patientSummary.lst
 
select FName || ' ' || MI || ' '  || LName as Name, -- it should be Name not patientname here
PATIENT_LOCATION.Patient_ID, PATIENT_LOCATION.Adm_Date,
PATIENT_LOCATION.Disc_Date, --- add the cost column also here
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION
where ... -- you have not added the join condition, so add that to link the three tables used
          -- in the from clause
order by patient_id; -- should be used here
 
SPOOL OFF

Open in new window

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.

ASKER

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:

Patient Billing Summary

Patient Name: PatientName Patient ID: 111111
Admission Date: 02-DEC-07 08.27.42.000000 PM Discharge Date:

PATIENTNAME
-------------------------------------------------------
John K Smith

from HADS database

Patient Billing Summary

Patient Name: PatientName Patient ID: 111111
Admission Date: 02-DEC-07 08.27.42.000000 PM Discharge Date:

CHARGE_CATEGORY COST
------------------------------ -----------
Radiology Test $650.00
Medicine $350.00
Facility $6,000.00

from HADS database


REM Patient Billing Summary
 
SET HEADSEP!
 
COLUMN Name NOPRINT NEW_VALUE PatientName
COLUMN Patient_ID NOPRINT NEW_VALUE ID
COLUMN Adm_Date NOPRINT NEW_VALUE AdmissionDate
COLUMN Disc_Date NOPRINT NEW_VALUE DischargeDate
 
TTITLE CENTER 'Patient Billing Summary' skip 2 -
LEFT 'Patient Name: ' PatientName -
RIGHT 'Patient ID: ' ID skip 1 -
LEFT 'Admission Date: ' AdmissionDate -
RIGHT 'Discharge Date: ' DischargeDate skip 3
 
select FName || ' ' || MI || ' '  || LName as PatientName, Patient_ID, Adm_Date, Disc_Date
from PATIENT_DEMOGRAPHIC JOIN PATIENT_ADMISSION
on (PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO)
where Patient_ID = 111111;
 
COLUMN Charge_Category format a30
COLUMN Cost format $99,999.99
BREAK on Patient_ID skip 2
COMPUTE sum of Cost on PatientID
 
select Charge_Category, SUM(Charge_Value) as Cost
from CHARGE_CODE, PATIENT_CHARGES
where CHARGE_CODE.Charge_Code = PATIENT_CHARGES.Charge_Code
group by CHARGE_CODE.Charge_Code, Description, Charge_Category, Patient_ID, 
PATIENT_CHARGES.Charge_Code, Charge_Date, Charge_Value
order by Patient_ID desc;
	
BTITLE 'from HADS database'

Open in new window

i have already suggested the below but why is that not done. can you do it :

select FName || ' ' || MI || ' ' || LName as Name, -- it should be Name not patientname here

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

ASKER

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.

The output I need is:

Patient Billing Summary

Patient Name: John K Smith Patient ID: 111111
Admission Date: 04/02/2007 Discharge Date: 04/09/2007

Charge Category Cost
Facility Summary Charges: $6,000.00
Medicine Summary Charges: $350.00
Clinical Test Summary Charges: $600.00
Radiology Test Summary Charges: $0.00
Pathology Summary Charges: $0.00

Total Charges: $6950.00

The output I am currently getting is:

Patient Billing Summary

Patient Name: John K Smith Patient ID: 111111
Admission Date: 02-APR-07 Discharge Date: 09-APR-07

from HADS database

Patient Billing Summary

Patient Name: John K Smith Patient ID: 111111
Admission Date: 02-APR-07 Discharge Date: 09-APR-07

PATIENTNAME
-------------------------------------------------------
John K Smith

from HADS database

Patient Billing Summary

Patient Name: John K Smith Patient ID: 111111
Admission Date: 02-APR-07 Discharge Date: 09-APR-07

CHARGE_CATEGORY COST
------------------------------ -----------
Radiology Test $650.00
Medicine $350.00
Facility $6,000.00

from HADS database

REM Patient Billing Summary
 
SET HEADSEP!
 
COLUMN Name NOPRINT NEW_VALUE PatientName
COLUMN Patient_ID NOPRINT NEW_VALUE ID
COLUMN Adm_Date NOPRINT NEW_VALUE AdmissionDate
COLUMN Disc_Date NOPRINT NEW_VALUE DischargeDate
 
TTITLE CENTER 'Patient Billing Summary' skip 2 -
LEFT 'Patient Name: ' PatientName -
RIGHT 'Patient ID: ' ID skip 1 -
LEFT 'Admission Date: ' AdmissionDate -
RIGHT 'Discharge Date: ' DischargeDate skip 3
 
COLUMN Charge_Category format a30
COLUMN Cost format $99,999.99
BREAK on Patient_ID skip 2
COMPUTE sum of Cost on PatientID
 
SPOOL patientSummary.lst
 
select FName || ' ' || MI || ' '  || LName as Name, PATIENT_ADMISSION.Patient_ID, PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date
from PATIENT_LOCATION, PATIENT_DEMOGRAPHIC JOIN PATIENT_ADMISSION
on (PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO)
where PATIENT_ADMISSION.Patient_ID = 111111;
 
select FName || ' ' || MI || ' '  || LName as PatientName,
PATIENT_LOCATION.Patient_ID, PATIENT_LOCATION.Adm_Date,
PATIENT_LOCATION.Disc_Date
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION;
 
select Charge_Category, SUM(Charge_Value) as Cost
from CHARGE_CODE, PATIENT_CHARGES
where CHARGE_CODE.Charge_Code = PATIENT_CHARGES.Charge_Code
group by CHARGE_CODE.Charge_Code, Description, Charge_Category, Patient_ID, 
PATIENT_CHARGES.Charge_Code, Charge_Date, Charge_Value
order by Patient_ID desc;
 
SPOOL OFF
	
BTITLE 'from HADS database'

Open in new window

i do not know what to say now.

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 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.

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.

ASKER

>>>>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:

select FName || ' ' || MI || ' ' || LName as Name, PATIENT_ADMISSION.Patient_ID, PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date, Charge_Category, SUM(Charge_Value) as Cost
from PATIENT_LOCATION, CHARGE_CODE, PATIENT_CHARGES, PATIENT_DEMOGRAPHIC JOIN PATIENT_ADMISSION
on (PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO)
where PATIENT_ADMISSION.Patient_ID = 111111 and CHARGE_CODE.Charge_Code = PATIENT_CHARGES.Charge_Code
group by CHARGE_CODE.Charge_Code, Description, Charge_Category, PATIENT_ADMISSION.Patient_ID, PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date,
PATIENT_CHARGES.Charge_Code, Charge_Date, Charge_Value
order by PATIENT_ADMISSION.Patient_ID desc;

I get the following error:

select FName || ' ' || MI || ' ' || LName as Name, PATIENT_ADMISSION.Patient_ID, PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date, Charge_Category, SUM(Charge_Value) as Cost
*
Error at line 1:
ORA-00979: not a GROUP BY expression

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.

ASKER

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).

select LName, MI, FName, PATIENT_ADMISSION.Patient_ID, Charge_Value, Charge_Category, PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO
and PATIENT_LOCATION.Patient_ID = PATIENT_ADMISSION.Patient_ID
and PATIENT_CHARGES.Charge_Code = CHARGE_CODE.Charge_Code;

LNAME MI FNAME PATIENT_ID CHARGE_VAL CHARGE_CAT ADM_DATE       DISC_DATE
Smith K John 111111 6000        Facility        02-APR-07       09-APR-07
Smith K John 111111 350        Medicine        02-APR-07       09-APR-07
Smith K John 111111 650        Radiology Test 02-APR-07       09-APR-07

When I attempt to also SUM(Charge_Value), I get an error.

select LName, MI, FName, PATIENT_ADMISSION.Patient_ID, Charge_Value, Charge_Category, PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date, SUM(Charge_Value)
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO
and PATIENT_LOCATION.Patient_ID = PATIENT_ADMISSION.Patient_ID
and PATIENT_CHARGES.Charge_Code = CHARGE_CODE.Charge_Code
and PATIENT_CHARGES.Patient_ID = PATIENT_ADMISSION.Patient_ID;

select LName, MI, FName, PATIENT_ADMISSION.Patient_ID, Charge_Value, Charge_Category,
*
PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date, SUM(Charge_Value)

ERROR at line 1:
ORA-00937: not a single-group group function

Any suggestions are appreciated.

ASKER

I'm sorry, you just pointed this out to me.

>>>>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.

ASKER

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

select LName, MI, FName, PATIENT_ADMISSION.Patient_ID, Charge_Value, Charge_Category, PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date, SUM(Charge_Value)
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO
and PATIENT_LOCATION.Patient_ID = PATIENT_ADMISSION.Patient_ID
and PATIENT_CHARGES.Charge_Code = CHARGE_CODE.Charge_Code
and PATIENT_CHARGES.Patient_ID = PATIENT_ADMISSION.Patient_ID
group by LName, MI, FName, PATIENT_ADMISSION.Patient_ID, Charge_Value, Charge_Category, PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date
order by Patient_ID desc;

LN MI FN PAT_ID CHG_VAL CHG_CAT        ADM_DATE       DISC_DATE       SUM(CHARGE_VALUE)
Smith K John 111111 350 Medicine        02-APR-07       09-APR-07       350
Smith K John 111111 650 Radiology Test 02-APR-07       09-APR-07       650
Smith K John 111111 6000 Facility        02-APR-07       09-APR-07       6000

select SUM(Charge_Value)
from PATIENT_CHARGES, PATIENT_ADMISSION
where PATIENT_CHARGES.Patient_ID = PATIENT_ADMISSION.Patient_ID;

SUM(CHARGE_VALUE)
7000

Remove the Charge_value from the GROUP BY list.

select LName, MI, FName, PATIENT_ADMISSION.Patient_ID, Charge_Category, PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date, SUM(Charge_Value)
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO
and PATIENT_LOCATION.Patient_ID = PATIENT_ADMISSION.Patient_ID
and PATIENT_CHARGES.Charge_Code = CHARGE_CODE.Charge_Code
and PATIENT_CHARGES.Patient_ID = PATIENT_ADMISSION.Patient_ID
group by LName, MI, FName, PATIENT_ADMISSION.Patient_ID, Charge_Category, PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date
order by Patient_ID desc;

ASKER

I corrected the Name.

select FName || ' ' || MI || ' ' || LName as Name, PATIENT_ADMISSION.Patient_ID, Charge_Value, Charge_Category, PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date, SUM(Charge_Value)
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO
and PATIENT_LOCATION.Patient_ID = PATIENT_ADMISSION.Patient_ID
and PATIENT_CHARGES.Charge_Code = CHARGE_CODE.Charge_Code
and PATIENT_CHARGES.Patient_ID = PATIENT_ADMISSION.Patient_ID
group by LName, MI, FName, PATIENT_ADMISSION.Patient_ID, Charge_Value, Charge_Category, PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date
order by Patient_ID desc;

ASKER

What is causing the TTITLE 'Patient Billing Summary' to print twice?

OUTPUT:
Patient Billing Summary

Patient Name: John K Smith Patient ID: 111111
Admission Date: 02-APR-07 Discharge Date: 09-APR-07

CHARGE_CATEGORY COST
------------------------------ -----------
Facility $6,000.00
Medicine $350.00
Radiology Test $650.00
-----------
from HADS database

Patient Billing Summary

Patient Name: John K Smith Patient ID: 111111
Admission Date: 02-APR-07 Discharge Date: 09-APR-07

CHARGE_CATEGORY COST
------------------------------ -----------
$7,000.00

from HADS database

REM Patient Billing Summary
 
SET HEADSEP!
 
COLUMN Name NOPRINT NEW_VALUE PatientName
COLUMN Patient_ID NOPRINT NEW_VALUE ID
COLUMN Adm_Date NOPRINT NEW_VALUE AdmissionDate
COLUMN Disc_Date NOPRINT NEW_VALUE DischargeDate
 
TTITLE CENTER 'Patient Billing Summary' skip 2 -
LEFT 'Patient Name: ' PatientName -
RIGHT 'Patient ID: ' ID skip 1 -
LEFT 'Admission Date: ' AdmissionDate -
RIGHT 'Discharge Date: ' DischargeDate skip 3
 
COLUMN Charge_Category format a30
COLUMN Cost format $99,999.99
BREAK on Patient_ID skip 2
COMPUTE sum of Cost on Patient_ID
COMPUTE sum of Cost on report
 
SPOOL patientSummary.lst
 
select FName || ' ' || MI || ' ' || LName as Name, PATIENT_ADMISSION.Patient_ID, Charge_Category,
PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date, SUM(Charge_Value) as Cost
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO
and PATIENT_LOCATION.Patient_ID = PATIENT_ADMISSION.Patient_ID
and PATIENT_CHARGES.Charge_Code = CHARGE_CODE.Charge_Code
and PATIENT_CHARGES.Patient_ID = PATIENT_ADMISSION.Patient_ID
group by LName, MI, FName, PATIENT_ADMISSION.Patient_ID, Charge_Category, PATIENT_LOCATION.Adm_Date,
PATIENT_LOCATION.Disc_Date
order by Patient_ID desc;
 
SPOOL OFF

Open in new window

try the attached code :

Also just execute the select query and paste the output ( to see how many records it returns ):

select FName || ' ' || MI || ' ' || LName as Name, PATIENT_ADMISSION.Patient_ID, Charge_Category,
PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date, SUM(Charge_Value) as Cost
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO
and PATIENT_LOCATION.Patient_ID = PATIENT_ADMISSION.Patient_ID
and PATIENT_CHARGES.Charge_Code = CHARGE_CODE.Charge_Code
and PATIENT_CHARGES.Patient_ID = PATIENT_ADMISSION.Patient_ID
group by LName, MI, FName, PATIENT_ADMISSION.Patient_ID, Charge_Category, PATIENT_LOCATION.Adm_Date,
PATIENT_LOCATION.Disc_Date
order by Patient_ID desc;

Thanks

REM Patient Billing Summary
 
SET HEADSEP!
set pagesize 100       --- we can set the pagesize to 100 lines ( just to make sure
                      -- that title does not come twice because of page size
 
COLUMN Name NOPRINT NEW_VALUE PatientName
COLUMN Patient_ID NOPRINT NEW_VALUE ID
COLUMN Adm_Date NOPRINT NEW_VALUE AdmissionDate
COLUMN Disc_Date NOPRINT NEW_VALUE DischargeDate
 
TTITLE CENTER 'Patient Billing Summary' skip 2 -
LEFT 'Patient Name: ' PatientName -
RIGHT 'Patient ID: ' ID skip 1 -
LEFT 'Admission Date: ' AdmissionDate -
RIGHT 'Discharge Date: ' DischargeDate skip 3
 
COLUMN Charge_Category format a30
COLUMN Cost format $99,999.99
BREAK on Patient_ID skip 2
COMPUTE sum of Cost on Patient_ID
COMPUTE sum of Cost on report
 
SPOOL patientSummary.lst
 
select FName || ' ' || MI || ' ' || LName as Name, PATIENT_ADMISSION.Patient_ID, Charge_Category,
PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date, SUM(Charge_Value) as Cost
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO
and PATIENT_LOCATION.Patient_ID = PATIENT_ADMISSION.Patient_ID
and PATIENT_CHARGES.Charge_Code = CHARGE_CODE.Charge_Code
and PATIENT_CHARGES.Patient_ID = PATIENT_ADMISSION.Patient_ID
group by LName, MI, FName, PATIENT_ADMISSION.Patient_ID, Charge_Category, PATIENT_LOCATION.Adm_Date,
PATIENT_LOCATION.Disc_Date
order by Patient_ID desc;
 
SPOOL OFF

Open in new window

ASKER

It looks like that fixed the issue with the report being printed twice:

Patient Billing Summary

Patient Name: John K Smith Patient ID: 111111
Admission Date: 02-APR-07 Discharge Date: 09-APR-07

CHARGE_CATEGORY COST
------------------------------ -----------
Facility $6,000.00
Medicine $350.00
Radiology Test $650.00
-----------
$7,000.00

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:

Patient Billing Summary

Patient Name: John K Smith Patient ID: 111111
Admission Date: 02-APR-07 Discharge Date: 09-APR-07

CHARGE_CATEGORY COST
---------------------------------------------- ---------------
Facility Summary Charges: $6,000.00
Medicine Summary Charges: $350.00
Radiology Test Summary Charges: $650.00
---------------
Total Charges: $7,000.00

what about this ?

Also just execute the select query and paste the output ( to see how many records it returns ):

select FName || ' ' || MI || ' ' || LName as Name, PATIENT_ADMISSION.Patient_ID, Charge_Category,
PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date, SUM(Charge_Value) as Cost
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO
and PATIENT_LOCATION.Patient_ID = PATIENT_ADMISSION.Patient_ID
and PATIENT_CHARGES.Charge_Code = CHARGE_CODE.Charge_Code
and PATIENT_CHARGES.Patient_ID = PATIENT_ADMISSION.Patient_ID
group by LName, MI, FName, PATIENT_ADMISSION.Patient_ID, Charge_Category, PATIENT_LOCATION.Adm_Date,
PATIENT_LOCATION.Disc_Date
order by Patient_ID desc;

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.

Thanks

these two lines should be changed as shown below :

1)
COMPUTE sum of Cost on Patient_ID
COMPUTE sum of Cost on report

>>>>>

COMPUTE sum LABEL "Total Charges:" of Cost on Patient_ID
COMPUTE sum LABEL "Total Charges:" of Cost on report

2) query should be changed as below ;

select FName || ' ' || MI || ' ' || LName as Name, PATIENT_ADMISSION.Patient_ID,
Charge_Category || ' Summary Charges:' Charge_Category,
PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date, SUM(Charge_Value) as Cost
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO
and PATIENT_LOCATION.Patient_ID = PATIENT_ADMISSION.Patient_ID
and PATIENT_CHARGES.Charge_Code = CHARGE_CODE.Charge_Code
and PATIENT_CHARGES.Patient_ID = PATIENT_ADMISSION.Patient_ID
group by LName, MI, FName, PATIENT_ADMISSION.Patient_ID,
Charge_Category||' Summary Charges:' , PATIENT_LOCATION.Adm_Date,
PATIENT_LOCATION.Disc_Date
order by Patient_ID desc;

Thanks

ASKER

Everything works except for the "Total Charges: ".

Patient Billing Summary

Patient Name: John K Smith Patient ID: 111111
Admission Date: 02-APR-07 Discharge Date: 09-APR-07

CHARGE_CATEGORY COST
--------------------------------------------- -----------
Facility Summary Charges: $6,000.00
Medicine Summary Charges: $350.00
Radiology Test Summary Charges: $650.00
-----------
$7,000.00

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?

Thank you

REM Patient Billing Summary
 
SET HEADSEP!
 
COLUMN Name NOPRINT NEW_VALUE PatientName
COLUMN Patient_ID NOPRINT NEW_VALUE ID
COLUMN Adm_Date NOPRINT NEW_VALUE AdmissionDate
COLUMN Disc_Date NOPRINT NEW_VALUE DischargeDate
 
TTITLE CENTER 'Patient Billing Summary' skip 2 -
LEFT 'Patient Name: ' PatientName -
RIGHT 'Patient ID: ' ID skip 1 -
LEFT 'Admission Date: ' AdmissionDate -
RIGHT 'Discharge Date: ' DischargeDate skip 3
 
COLUMN Charge_Category format a40
COLUMN Cost format $99,999.99
BREAK on Patient_ID skip 2
COMPUTE sum LABEL "Total Charges: " of Cost on Patient_ID
COMPUTE sum LABEL "Total Charges: " of Cost on report
 
SPOOL patientSummary.lst
 
select FName || ' ' || MI || ' ' || LName as Name, PATIENT_ADMISSION.Patient_ID, Charge_Category ||
' Summary Charges: ' Charge_Category,
PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date, SUM(Charge_Value) as Cost
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO
and PATIENT_LOCATION.Patient_ID = PATIENT_ADMISSION.Patient_ID
and PATIENT_CHARGES.Charge_Code = CHARGE_CODE.Charge_Code
and PATIENT_CHARGES.Patient_ID = PATIENT_ADMISSION.Patient_ID
group by LName, MI, FName, PATIENT_ADMISSION.Patient_ID, Charge_Category || ' Summary Charges: ',
PATIENT_LOCATION.Adm_Date,
PATIENT_LOCATION.Disc_Date
order by Patient_ID desc;
 
SPOOL OFF

Open in new window

refer to the url on how to define a dummy column to get the output formatting you need :

http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12014.htm#i2697257

to make you understand, i did the below in my test database :

my table name is : A
it has two numeric columns : a and b

Thanks

break on a on report
column dummy format a14 heading '' justify right
compute sum label 'my sub charges:' of b on a
compute sum label 'total charges:' of b on report
select null dummy, a,b
from a order by a;
 
output:
                        A          B
-------------- ---------- ----------
                        4          2
               ********** ----------
               my sub cha          2
                       10          5
                                   5
               ********** ----------
               my sub cha         10
                          ----------
total charges:                    12

Open in new window

I am trying to say that if we want to print some label for the computed column on report then we need to have a dummy column something like as shown in my pasted example.

so just modify your code to have the below :

-- i have given a14 because the length of 'total charges:' is 14. you can increase as per your need

1) single quote ' is used instead of double quote "
COMPUTE sum LABEL 'Total Charges: ' of Cost on Patient_ID
COMPUTE sum LABEL 'Total Charges: ' of Cost on report

2) two consecutive single quotes '' not double quote
column dummy format a14 heading '' justify right

2) select null dummy, FName || ' ' || MI || ' ' || LName as Name, PATIENT_ADMISSION.Patient_ID, Charge_Category ||
' Summary Charges: ' Charge_Category,
PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date, SUM(Charge_Value) as Cost
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO
and PATIENT_LOCATION.Patient_ID = PATIENT_ADMISSION.Patient_ID
and PATIENT_CHARGES.Charge_Code = CHARGE_CODE.Charge_Code
and PATIENT_CHARGES.Patient_ID = PATIENT_ADMISSION.Patient_ID
group by LName, MI, FName, PATIENT_ADMISSION.Patient_ID, Charge_Category || ' Summary Charges: ',
PATIENT_LOCATION.Adm_Date,
PATIENT_LOCATION.Disc_Date
order by Patient_ID desc;

Thanks

ASKER

It is adding a dummy column out to the left of the Charge_Category column, and still no 'Total Charges'.

Patient Billing Summary

Patient Name: John K Smith Patient ID: 111111
Admission Date: 02-APR-07 Discharge Date: 09-APR-07

CHARGE_CATEGORY COST
-------------- ---------------------------------------- ------------
Facility Summary Charges: $6,000.00
Medicine Summary Charges: $350.00
Radiology Test Summary Charges: $650.00
------------
$7,000.00

REM Patient Billing Summary
 
SET HEADSEP!
 
COLUMN Name NOPRINT NEW_VALUE PatientName
COLUMN Patient_ID NOPRINT NEW_VALUE ID
COLUMN Adm_Date NOPRINT NEW_VALUE AdmissionDate
COLUMN Disc_Date NOPRINT NEW_VALUE DischargeDate
 
TTITLE CENTER 'Patient Billing Summary' skip 2 -
LEFT 'Patient Name: ' PatientName -
RIGHT 'Patient ID: ' ID skip 1 -
LEFT 'Admission Date: ' AdmissionDate -
RIGHT 'Discharge Date: ' DischargeDate skip 3
 
COLUMN Charge_Category format a40
COLUMN Cost format $999,999.99
BREAK on Patient_ID skip 2
COLUMN dummy format a14 heading '' justify right
COMPUTE sum LABEL 'Total Charges: ' of Cost on Patient_ID
COMPUTE sum LABEL 'Total Charges: ' of Cost on report
 
SPOOL patientSummary.lst
 
select null dummy, FName || ' ' || MI || ' ' || LName as Name,
PATIENT_ADMISSION.Patient_ID, Charge_Category ||
' Summary Charges: ' Charge_Category,
PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date, SUM(Charge_Value)
as Cost
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION,
PATIENT_CHARGES, CHARGE_CODE
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO
and PATIENT_LOCATION.Patient_ID = PATIENT_ADMISSION.Patient_ID
and PATIENT_CHARGES.Charge_Code = CHARGE_CODE.Charge_Code
and PATIENT_CHARGES.Patient_ID = PATIENT_ADMISSION.Patient_ID
group by LName, MI, FName, PATIENT_ADMISSION.Patient_ID, Charge_Category
|| ' Summary Charges: ',
PATIENT_LOCATION.Adm_Date,
PATIENT_LOCATION.Disc_Date
order by Patient_ID desc;
 
SPOOL OFF

Open in new window

can you give me the sql script to create all your tables and insert 1 record into it with patient id 1111, so that i can debug in my test database to see what is the issue.

Thanks

ASKER

Below is the sql that creates all tables in my database. The patient, John K. Smith is inserted and he has the patient id 111111.

Thank you.

create table PATIENT_DEMOGRAPHIC(
SSNO      		NUMBER NOT NULL,
LName		VARCHAR2(25) NOT NULL,     
MI                   	VARCHAR2(3),
FName           	VARCHAR2(25),
Street               	VARCHAR2(35),
City                 	VARCHAR2(25),
State                	VARCHAR2(2),
Zip                   	NUMBER,
DOB                	DATE NOT NULL,
Gender            	VARCHAR2(1) constraint check_Gender CHECK (Gender IN ('M', 'F')) NOT NULL,
Race                	VARCHAR2(3) constraint check_Race CHECK (Race IN ('AFR', 'WHI', 'API', 'HSP', 'OTH')),
constraint PATIENT_DEMOGRAPHIC_PK primary key (SSNO)
);
 
create table PATIENT_ADMISSION(
Patient_ID             	NUMBER NOT NULL,
SSNO  		NUMBER NOT NULL,     
Adm_Date            	TIMESTAMP NOT NULL,
Disc_Date            	DATE,
Adm_Type              VARCHAR2(2) constraint check_Adm_Type CHECK (Adm_Type IN ('IP', 'OP', 'TP', 'RP')),
constraint PATIENT_ADMISSION_PK primary key (Patient_ID),
constraint PATIENT_DEMOGRAPHIC_FK foreign key (SSNO)
references PATIENT_DEMOGRAPHIC (SSNO)
);
 
create sequence SEQ_PATIENT_ADMISSION_PK increment by 1 start with 111111;
 
create or replace trigger PATIENT_ADMISSION_TRIGGER
after insert or update on PATIENT_DEMOGRAPHIC
for each row
begin
      insert into PATIENT_ADMISSION (Patient_ID, SSNO, Adm_Date, Disc_Date, Adm_Type)
      values (SEQ_PATIENT_ADMISSION_PK.NextVal, :new.SSNO, SYSDATE, null, 'OP');
end;
/
 
show errors trigger PATIENT_ADMISSION_TRIGGER;
 
insert into PATIENT_DEMOGRAPHIC 
values ('999999999', 'Smith', 'K', 'John', '111 Baker Street', 'Smilesville', 'CA', '90833', '10-JUL-70', 'M', 'WHI');
 
create table PATIENT_ADMISSION_NEW as
select * from PATIENT_ADMISSION;
update PATIENT_ADMISSION_NEW
set Adm_Type = 'IP';
update PATIENT_ADMISSION pa1
set Adm_Type = (
select Adm_Type
from PATIENT_ADMISSION_NEW pa2
where pa1.patient_id = pa2.patient_id);
 
drop table PATIENT_ADMISSION_NEW;
 
update PATIENT_ADMISSION 
set Adm_Type = 'IP' 
where patient_id = 111111;
 
create table INSURANCE_COMPANY(
Comp_Code        	VARCHAR2(25) NOT NULL,
Comp_Name         	VARCHAR2(25),
Street                   	VARCHAR2(25),
City        		VARCHAR2(25),
State                     VARCHAR2(2),
Zip                     	NUMBER,
Phone                   	NUMBER,
Contact               	VARCHAR2(35),
constraint INSURANCE_COMPANY_PK primary key (Comp_Code)
);
 
create table PATIENT_INSURANCE(
Policy_Num            	VARCHAR2(20) NOT NULL,
Comp_Code           	VARCHAR2(20),
SSNO                    	NUMBER NOT NULL,
LName                 	VARCHAR2(25),
FName                	VARCHAR2(25),
Eff_Date              	DATE,
Exp_Date               	DATE,
Ins_Type         	VARCHAR2(3) constraint check_Ins_Type CHECK (Ins_Type IN ('PPO', 'HMO')),
Primary_Phy        	VARCHAR2(35),
constraint PATIENT_INSURANCE_PK primary key (Policy_Num, SSNO),
constraint INS_COMP_FK foreign key (Comp_Code)
references INSURANCE_COMPANY (Comp_Code),
constraint PATIENT_SSNO_FK foreign key (SSNO)
references PATIENT_DEMOGRAPHIC (SSNO)
);
 
insert into INSURANCE_COMPANY 
values ('JUP', 'Jupiter Insurance Inc', '111 Good Health Ave', 'Smilesville', 'MD', '20888', '3019998888', 'Ms. Patty Snyder');
 
insert into INSURANCE_COMPANY 
values ('GHI', 'Good Health Insurance Inc', '222 Pink Cheeks Drive', 'Healthville', 'NJ', '08956', '2028936784', 'Mr. Bob Simpson');
 
insert into PATIENT_INSURANCE 
values ('A0358F6', 'JUP', '999999999',  'Smith', 'John', '01-JAN-07', '31-DEC-07', 'PPO', 'Dr. Joseph Campbell');
 
create or replace trigger PPO_TRIGGER
before insert or update on PATIENT_INSURANCE
for each row
begin
	if :new.Ins_Type = 'PPO' and :new.Primary_Phy is null 
	then
	RAISE_APPLICATION_ERROR (
	-20001, 'Primary_Phy cannot be null when Ins_Type is PPO');
	end if;
end;
/
 
show errors trigger PPO_TRIGGER;
 
create table WARD_INFORMATION(
Ward_Code       	VARCHAR2(3) constraint check_Ward_Code CHECK (Ward_Code IN ('GWD', 'INC', 'CRD', 'ONC', 'OBG', 'PED')) NOT NULL,
Ward_Name        	VARCHAR2(25),
No_Beds              	NUMBER,
Location              	VARCHAR2(25),
constraint WARD_INFORMATION_PK primary key (Ward_Code)
);
 
create table PATIENT_LOCATION(
Patient_ID        	NUMBER NOT NULL,
Ward_Code            	VARCHAR2(3) NOT NULL,
Bed_Num            	NUMBER,
Adm_Date            	DATE,
Disc_Date            	DATE,
constraint PATIENT_LOCATION_PK primary key (Patient_ID, Ward_Code, Bed_Num, Adm_Date),
constraint PL_PATIENT_ID_FK foreign key (Patient_ID)
references PATIENT_ADMISSION (Patient_ID),
constraint WARD_FK foreign key (Ward_Code)
references WARD_INFORMATION (Ward_Code)
);
 
insert into WARD_INFORMATION 
values ('CRD', 'Cardiology', '30',  'Building 223');
 
insert into WARD_INFORMATION 
values ('ONC', 'Oncology', '20',  'Building 222');
 
insert into PATIENT_LOCATION 
values (SEQ_PATIENT_ADMISSION_PK.CurrVal, 'CRD', '3', '02-APR-07', '09-APR-07');
 
create table CHARGE_CODE(
Charge_Code        	VARCHAR2(3) NOT NULL,
Description            	VARCHAR2(30),
Charge_Category  	VARCHAR2(15),
constraint CHARGE_CODE_PK primary key (Charge_Code)
);
 
insert into CHARGE_CODE 
values ('RMS', 'Room Stay', 'Facility');
 
insert into CHARGE_CODE 
values ('SUP', 'Room Supplies', 'Facility');
 
insert into CHARGE_CODE 
values ('PRD', 'Prescription Drugs', 'Medicine');
 
insert into CHARGE_CODE 
values ('BLD', 'Blood Tests', 'Clinical Test');
 
insert into CHARGE_CODE 
values ('URI', 'Urine Tests', 'Clinical Test');
 
insert into CHARGE_CODE 
values ('XRA', 'X-Rays', 'Radiology Test');
 
insert into CHARGE_CODE 
values ('EKG', 'Electro Cardiogram', 'Radiology Test');
 
insert into CHARGE_CODE 
values ('MRI', 'Magnetic Resonance Imaging', 'Radiology Test');
 
insert into CHARGE_CODE 
values ('TIS', 'Tissue Tests', 'Pathology');
 
create table PATIENT_CHARGES(
Patient_ID            	NUMBER NOT NULL,
Charge_Code    	VARCHAR2(3),
Charge_Date        	TIMESTAMP,
Charge_Value       	NUMBER,
constraint PATIENT_CHARGES_PK primary key (Patient_ID, Charge_Code, Charge_Date),
constraint PC_PATIENT_ID_FK foreign key (Patient_ID)
references PATIENT_ADMISSION (Patient_ID),
constraint CHARGE_CODE_FK foreign key (Charge_Code)
references CHARGE_CODE (Charge_Code)
);
 
insert into PATIENT_CHARGES 
values (SEQ_PATIENT_ADMISSION_PK.CurrVal, 'RMS', '02-APR-07', '750');
 
insert into PATIENT_CHARGES 
values (SEQ_PATIENT_ADMISSION_PK.CurrVal, 'PRD', '04-APR-07', '350');
 
insert into PATIENT_CHARGES 
values (SEQ_PATIENT_ADMISSION_PK.CurrVal, 'EKG', '08-APR-07', '650');
 
update PATIENT_CHARGES set Charge_Value = ((to_Date('04092007','mmddyyyy') - to_Date(to_char(Charge_Date,'mmddyyyy'),'mmddyyyy')) + 1)  * Charge_Value 
where Charge_Code = 'RMS';
 
create table PATIENT_ADMISSION_NEW as
select * from PATIENT_ADMISSION;
update PATIENT_ADMISSION_NEW
set Disc_Date = SYSDATE;
update PATIENT_ADMISSION pa1
set Disc_Date = (
select Disc_Date
from PATIENT_ADMISSION pa2
where pa1. Disc_Date = pa2. Disc_Date);
 
drop table PATIENT_ADMISSION_NEW;
 
create or replace trigger PADM_DISC_DATE_TRIGGER
before insert or update on PATIENT_ADMISSION
for each row
begin
	if :new.Disc_Date > :new.Adm_Date 
	then
	RAISE_APPLICATION_ERROR (
	-20002, 'Disc_Date cannot come before Adm_Date');
	end if;
end;
/
 
show errors trigger PADM_DISC_DATE_TRIGGER;
 
create or replace trigger PLOC_DISC_DATE_TRIGGER
before insert or update on PATIENT_LOCATION
for each row
begin
	if :new.Disc_Date > :new.Adm_Date 
	then
	RAISE_APPLICATION_ERROR (
	-20003, 'Disc_Date cannot come before Adm_Date');
	end if;
end;
/
 
show errors trigger PLOC_DISC_DATE_TRIGGER;
 
create or replace trigger PATIENT_LOCATION_TRIGGER
after update on PATIENT_ADMISSION
for each row
when (new.Disc_Date is not null)
begin
	update PATIENT_LOCATION
  	set Disc_Date = :new.Disc_Date
  	where Patient_Id = :new.Patient_Id;
end;
/
 
show errors trigger PATIENT_LOCATION_TRIGGER;
 
create table ALL_PATIENTS(
PatientSSNO        	NUMBER NOT NULL,
PatientLName        	VARCHAR2(25) NOT NULL,
PatientMI               	VARCHAR2(3),
PatientFName         	VARCHAR2(25),
PatientStreet   	VARCHAR2(35),
PatientCity              VARCHAR2(25),
PatientState            VARCHAR2(2),
PatientZip           	NUMBER,
PatientDOB             DATE NOT NULL,
PatientGender      	VARCHAR2(1),
PatientRace      	VARCHAR2(3),
PatientAdm_Date  	TIMESTAMP NOT NULL,
constraint ALL_PATIENTS_PK primary key (PatientSSNO, PatientAdm_Date)
);
 
create or replace trigger ALL_PATIENTS_TRIGGER
after insert or update on PATIENT_DEMOGRAPHIC
for each row
begin
	insert into ALL_PATIENTS
      	(PatientSSNO, PatientLName, PatientMI, PatientFName, PatientStreet, PatientCity,
	PatientState, PatientZip, PatientDOB, PatientGender, PatientRace)
	values 
	(:new.SSNO, :new.LName, :new.MI, :new.FName, :new.Street, :new.City,
	:new.State, :new.Zip, :new.DOB, :new.Gender, :new.Race);
end;
/
 
show errors trigger ALL_PATIENTS_TRIGGER;
 
insert into ALL_PATIENTS 
values ('999999999', 'AllLName', 'ALL', 'AllFName', 'AllStreet', 'AllCity',  'AS', '99999', '05-APR-79', 'F', 'OTH', SYSDATE);
 
select * from PATIENT_DEMOGRAPHIC;
select * from PATIENT_ADMISSION;      
select * from INSURANCE_COMPANY;
select * from PATIENT_INSURANCE;
select * from WARD_INFORMATION;
select * from PATIENT_LOCATION;
select * from CHARGE_CODE;
select * from PATIENT_CHARGES;
select * from ALL_PATIENTS;

Open in new window

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

That did it:

Patient Billing Summary

Patient Name: John K Smith Patient ID: 111111
Admission Date: 02-APR-07 Discharge Date: 09-APR-07

PATIENT_ID CHARGE_CATEGORY COST
------------- ---------------------------------------- ------------
111111Facility Summary Charges: $6,000.00
Medicine Summary Charges: $350.00
Radiology Test Summary Charges: $650.00
************* ------------
Total Charges $7,000.00

Thank you!

ASKER

nav_kum_v,

Thank you so much for your time and effort!

Char