Create a view with oracle database

Question

1.00/5 (1 vote)

See more:

Please, how do I create a view that will give total charges for a given patient on a given day for a given doctor for the first quarter(January 1 through March 31, 2016). The output of the view should be given.

What I have tried:

I tried to SELECT DID from DOCTOR,
SELECT PID from PATIENT,
SELECT n from APPOINTMEnT where n > = 01-JAN-16 and < = 31-MAR-16
AND CHARGES NOT IN (SELECT DISTINCT CHARGES FROM TREATMENT);

then I use the view command as:
CREATE VIEW totalcharge AS SELECT query
SeleCT PID, DID, DATE, CHARGES from treatment;

Posted 26-Oct-17 4:47am

Member 13487285

Updated 26-Oct-17 20:15pm

Add a Solution

Comments

Santosh kumar Pithani 27-Oct-17 0:20am

Here,your not mentioned any relationship on tables.i think you question is additional charges collected by doctor from patients which were in first quarter?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Santosh kumar Pithani · Answer 1 · 2017-10-26T20:15:00

             ---#DOCTOR Table ---
CREATE TABLE #Doctor(
              DID INT,PID INT,DNAME VARCHAR(50));
INSERT INTO #Doctor 
              VALUES(1215,1209,'harish'),(1234,1261,'Saikiran'),
                    (1245,1271,'satish'),(1246,1214,'Santoshkumar'); 

          ------#patient TABLE ---
CREATE TABLE #patient (
             PID INT,P_name VARCHAR(50),APMID INT,charges MONEY DEFAULT 500);
INSERT INTO #patient 
             VALUES(1271,'satya',1,2000),(1261,'masthan',2,500),
                   (1209,'Varma',3,500),(1214,'sree',4,600);

           ------#AppointMent Table -----

CREATE TABLE #AppointMent(
                    APMID  INT,APMDATE DATE,DID INT)

INSERT INTO #AppointMent 
                 VALUES(3,'21/MAR/2017',1215),(2,'03/JUL/2017',1234),
                       (1,'05/JAN/2017',1245),(4,'08/FEB/2017',1246)

                  ----Create View on Below Query ----

 SELECT  #PATIENT.PID, #DOCTOR.DID,#APPOINTMENT.APMDATE,SUM(#PATIENT.charges)as charges 
      FROM  #DOCTOR inner join #PATIENT ON(#DOCTOR.PID=#PATIENT.PID) 
       inner join #APPOINTMENT 
       ON(#APPOINTMENT.APMID=#PATIENT.APMID)and(#APPOINTMENT.DID=#Doctor.DID) 
       WHERE APMDATE> ='01-JAN-2017' and APMDATE< ='31-MAR-2017' 
       GROUP BY  #PATIENT.PID,#DOCTOR.DID,#APPOINTMENT.APMDATE
       HAVING SUM(#PATIENT.charges)<>(select 500 as TREATMENTCHARGES);
--------------------------------
PID	DID	APMDATE	charges
--------------------------------
1214	1246	2017-02-08	600.00
1271	1245	2017-01-05	2000.00