Solved

How do I get the difference between a TIMESTAMP and a NUMBER?

Posted on 2007-12-01
2
1,302 Views
Last Modified: 2013-12-18
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/yyyy'))
- Charge_Date * Charge_Value
where Charge_Code = 'RMS';

update PATIENT_CHARGES set Charge_Value = cast(to_date('04/09/2007','mm/dd/yyyy'))
                                                                                                                                              *
ERROR at line 1:
ORA-00905: missing keyword
0
Comment
Question by:CharleneS77
2 Comments
 
LVL 11

Accepted Solution

by:
mohammadzahid earned 500 total points
ID: 20390174
update PATIENT_CHARGES set Charge_Value = (to_Date('04092007','mmddyyyy') - to_Date(to_char(charge_date,'mmddyyyy'),'mmddyyyy'))  * Charge_Value
where Charge_Code = 'RMS';
0
 

Author Closing Comment

by:CharleneS77
ID: 31412160
Perfect.  Thank you!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now