CharleneS77
asked on
How do I get the difference between a TIMESTAMP and a NUMBER?
I am working on a Hospital database. The variable 'RMS' represents the charge per day for staying in a hospital room. I need to repeat the 'RMS' charge for each date till 04/09/2007. How do I get the difference between 04/09/2007 and Charge_Date?
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');
I have tried the following and I get these errors:
update PATIENT_CHARGES set Charge_Value = '04/09/2007' - to_date(Charge_Date) * Charge_Value
where Charge_Code = 'RMS';
update PATIENT_CHARGES set Charge_Value = '04/09/2007' - to_date(Charge_Date) * Charge_Value
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got DATE
update PATIENT_CHARGES set Charge_Value = (to_date('04/09/2007','mm/ dd/yyyy'))
- Charge_Date * Charge_Value
where Charge_Code = 'RMS';
- Charge_Date * Charge_Value
*
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected NUMBER got TIMESTAMP
update PATIENT_CHARGES set Charge_Value = cast(to_date('04/09/2007', 'mm/dd/yyy y'))
- Charge_Date * Charge_Value
where Charge_Code = 'RMS';
update PATIENT_CHARGES set Charge_Value = cast(to_date('04/09/2007', 'mm/dd/yyy y'))
*
ERROR at line 1:
ORA-00905: missing keyword
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.
insert into PATIENT_CHARGES
values (SEQ_PATIENT_ADMISSION_PK.
insert into PATIENT_CHARGES
values (SEQ_PATIENT_ADMISSION_PK.
I have tried the following and I get these errors:
update PATIENT_CHARGES set Charge_Value = '04/09/2007' - to_date(Charge_Date) * Charge_Value
where Charge_Code = 'RMS';
update PATIENT_CHARGES set Charge_Value = '04/09/2007' - to_date(Charge_Date) * Charge_Value
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got DATE
update PATIENT_CHARGES set Charge_Value = (to_date('04/09/2007','mm/
- Charge_Date * Charge_Value
where Charge_Code = 'RMS';
- Charge_Date * Charge_Value
*
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected NUMBER got TIMESTAMP
update PATIENT_CHARGES set Charge_Value = cast(to_date('04/09/2007',
- Charge_Date * Charge_Value
where Charge_Code = 'RMS';
update PATIENT_CHARGES set Charge_Value = cast(to_date('04/09/2007',
*
ERROR at line 1:
ORA-00905: missing keyword
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER