I just found out an error (Invalid Number) in this line of SQL code:
WHERE NVL((submit_time - req_time) * 24 ,12121) != 12121
Can someone explain what may be the problem?
Asked
Active
Viewed 730 times
0
Jonathan Eustace
- 2,469
- 12
- 31
- 54
Sathish Kothandam
- 1,530
- 3
- 16
- 34
-
what is the possible values in submit_time and req_time – arunb2w Nov 07 '14 at 10:03
-
i think you are either trying (null - req_time) or (submit_time - null)? – SMA Nov 07 '14 at 10:05
-
Sometimes submit_time - req_time returns Null values . im trying to replace the null values with 12121 – Sathish Kothandam Nov 07 '14 at 10:08
-
@SathishK - Returning null wont cause problem here. can u let us know what is the data type of those fields. – arunb2w Nov 07 '14 at 10:10
-
Both are datetime data types – Sathish Kothandam Nov 07 '14 at 10:39
-
Oracle doesnt have datetime data type. Is it date or timestamp? – Noel Nov 07 '14 at 11:15
-
Yes.its timestamp :) – Sathish Kothandam Nov 07 '14 at 11:28
-
Subtracting timestamps gives INTERVAL type. So you shouldnt be comparing it with numbers. – Noel Nov 07 '14 at 11:38
-
My Aim is result should not contain any Null values . that's why im doing this ..can you help ? – Sathish Kothandam Nov 07 '14 at 11:47
1 Answers
1
Can you try something like this , to make sure that we are not dealing with null on those two fields
where decode(submit_time,NULL,12121,decode(req_time,null,12121,(submit_time - req_time) * 24))
arunb2w
- 1,196
- 9
- 28