IT Employment

Ask something
Question Locked

Oracle join table and calculation problem

0 helpful
2 replies

by alex789

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi all and thanks in advance,

I need to output the patient's name, patient's address, item_code, description, and total cost for a particular month in the folloing tables.
Here are the tables

CREATE TABLE patient(
pati_Ref_no NUMBER(6) NOT NULL,
name VARCHAR2(8),
dob DATE,
address VARCHAR2(15),
PRIMARY KEY(pati_Ref_no));

CREATE TABLE items(
item_code NUMBER(4) NOT NULL,
description VARCHAR2(20),
cost NUMBER(6,2),
PRIMARY KEY(item_code));

CREATE TABLE charge(
pati_Ref_no NUMBER(6) NOT NULL,
itemcode NUMBER(4) NOT NULL,
chargedate DATE,
PRIMARY KEY(pati_ref_no, itemcode, chargedate),
FOREIGN KEY(pati_ref_no)REFERENCES patient,
FOREIGN KEY(item_code)REFERENCES items);

Thank you for your reply.

2 replies

by sonuyadav

break on pati_ref_no skip 1 on report
compute sum of cost on pati_ref_no
col pati_ref_no noprint

Select
a.pati_ref_no,
a.name,
a.address,
b.charge_date,
c.item_code,
c.description,
c.cost
from
patient a,
charge b,
items c
where
a.pati_ref_no = nvl('&&pref',a.pati_ref_no)
and b.pati_ref_no = a.pati_ref_no
and c.item_code = b.item_code
order by 1

This discussion is closed to new replies.