CharleneS77
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
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
try the same thing in SQL*plus not in iSQL*Plus
Thanks
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
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?
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.
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
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.
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:
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
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
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
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
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
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
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;
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
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
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
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
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
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'
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
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
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'
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.
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.
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_Cod e
group by CHARGE_CODE.Charge_Code, Description, Charge_Category, PATIENT_ADMISSION.Patient_ ID, PATIENT_LOCATION.Adm_Date, PATIENT_LOCATION.Disc_Date ,
PATIENT_CHARGES.Charge_Cod e, 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
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_
from PATIENT_LOCATION, CHARGE_CODE, PATIENT_CHARGES, PATIENT_DEMOGRAPHIC JOIN PATIENT_ADMISSION
on (PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO)
where PATIENT_ADMISSION.Patient_
group by CHARGE_CODE.Charge_Code, Description, Charge_Category, PATIENT_ADMISSION.Patient_
PATIENT_CHARGES.Charge_Cod
order by PATIENT_ADMISSION.Patient_
I get the following error:
select FName || ' ' || MI || ' ' || LName as Name, PATIENT_ADMISSION.Patient_
*
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.
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_I D = PATIENT_ADMISSION.Patient_ ID
and PATIENT_CHARGES.Charge_Cod e = 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_I D = PATIENT_ADMISSION.Patient_ ID
and PATIENT_CHARGES.Charge_Cod e = 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.
I am currently working on my query. The following query produces the right output (without the SUM).
select LName, MI, FName, PATIENT_ADMISSION.Patient_
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO
and PATIENT_LOCATION.Patient_I
and PATIENT_CHARGES.Charge_Cod
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_
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO
and PATIENT_LOCATION.Patient_I
and PATIENT_CHARGES.Charge_Cod
and PATIENT_CHARGES.Patient_ID
select LName, MI, FName, PATIENT_ADMISSION.Patient_
*
PATIENT_LOCATION.Adm_Date,
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.
>>>>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_I D = PATIENT_ADMISSION.Patient_ ID
and PATIENT_CHARGES.Charge_Cod e = 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
select LName, MI, FName, PATIENT_ADMISSION.Patient_
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO
and PATIENT_LOCATION.Patient_I
and PATIENT_CHARGES.Charge_Cod
and PATIENT_CHARGES.Patient_ID
group by LName, MI, FName, PATIENT_ADMISSION.Patient_
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
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_I D = PATIENT_ADMISSION.Patient_ ID
and PATIENT_CHARGES.Charge_Cod e = 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;
select LName, MI, FName, PATIENT_ADMISSION.Patient_
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO
and PATIENT_LOCATION.Patient_I
and PATIENT_CHARGES.Charge_Cod
and PATIENT_CHARGES.Patient_ID
group by LName, MI, FName, PATIENT_ADMISSION.Patient_
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_I D = PATIENT_ADMISSION.Patient_ ID
and PATIENT_CHARGES.Charge_Cod e = 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;
select FName || ' ' || MI || ' ' || LName as Name, PATIENT_ADMISSION.Patient_
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO
and PATIENT_LOCATION.Patient_I
and PATIENT_CHARGES.Charge_Cod
and PATIENT_CHARGES.Patient_ID
group by LName, MI, FName, PATIENT_ADMISSION.Patient_
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
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
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_I D = PATIENT_ADMISSION.Patient_ ID
and PATIENT_CHARGES.Charge_Cod e = 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
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_
PATIENT_LOCATION.Adm_Date,
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO
and PATIENT_LOCATION.Patient_I
and PATIENT_CHARGES.Charge_Cod
and PATIENT_CHARGES.Patient_ID
group by LName, MI, FName, PATIENT_ADMISSION.Patient_
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
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
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_I D = PATIENT_ADMISSION.Patient_ ID
and PATIENT_CHARGES.Charge_Cod e = 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
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_
PATIENT_LOCATION.Adm_Date,
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO
and PATIENT_LOCATION.Patient_I
and PATIENT_CHARGES.Charge_Cod
and PATIENT_CHARGES.Patient_ID
group by LName, MI, FName, PATIENT_ADMISSION.Patient_
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_I D = PATIENT_ADMISSION.Patient_ ID
and PATIENT_CHARGES.Charge_Cod e = 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
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_
Charge_Category || ' Summary Charges:' Charge_Category,
PATIENT_LOCATION.Adm_Date,
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO
and PATIENT_LOCATION.Patient_I
and PATIENT_CHARGES.Charge_Cod
and PATIENT_CHARGES.Patient_ID
group by LName, MI, FName, PATIENT_ADMISSION.Patient_
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
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
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
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
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_I D = PATIENT_ADMISSION.Patient_ ID
and PATIENT_CHARGES.Charge_Cod e = 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
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_
' Summary Charges: ' Charge_Category,
PATIENT_LOCATION.Adm_Date,
from PATIENT_DEMOGRAPHIC, PATIENT_ADMISSION, PATIENT_LOCATION, PATIENT_CHARGES, CHARGE_CODE
where PATIENT_DEMOGRAPHIC.SSNO = PATIENT_ADMISSION.SSNO
and PATIENT_LOCATION.Patient_I
and PATIENT_CHARGES.Charge_Cod
and PATIENT_CHARGES.Patient_ID
group by LName, MI, FName, PATIENT_ADMISSION.Patient_
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
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
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
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.
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!
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
Thank you so much for your time and effort!
Char
ASKER
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
.