0

I m using this query to get a result of the difference between the start time and end time of an activity. Where the end time is null i wanted to put the minimum value as 500. Please advice and HELP!!

select * from table
   where (end_time - start_time) * 24 * 60 > 1, 
  IF end_time IS NULL THEN '500';
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • Apply `NVL` with proper conversion on `end_time`. See my answer. – Lalit Kumar B Feb 05 '15 at 14:02
  • What should be 500 (or '500')? Minimum what? You have multiple columns in your select list from the `*`, you aren't selecting the difference; so it that supposed to replace one of column values? Or if the end time is null then assume the difference is 500 so those rows are included in the result set - in which case wouldn't any value greater than 1 do? Though that would be even simpler with just an `or` condition. It really isn't clear what you're trying to do. – Alex Poole Feb 05 '15 at 15:12

3 Answers3

0

So this is your query:

select * from table where (end_time - start_time) * 24 * 60 > 1;

But you want to treat a null end_time as 500. So use NVL or COALESCE to replace the null with 500:

select * from table where (nvl(end_time,500) - start_time) * 24 * 60 > 1;
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • And it is wrong to simply apply NVL on end_time like that. What is 500? Days, hours, minutes, seconds etc. ? – Lalit Kumar B Feb 05 '15 at 13:58
  • @Lalit Kumar B: I don't know. Maybe start_time and end_time hold the nth minute of a day as an integer, so 500 would be the 500th minute of that day. I don't know what data is in those columns, I just answered the question on how to use 500 for NULL. You may be right though that Farhaan Ali confuses column types here. We cannot know this. – Thorsten Kettner Feb 05 '15 at 14:05
  • I would only say that the way the datetime columns are caculated in the query, it gives a clear picture that those columns are either DATE/TIMESTAMP data types. I am saying this per my experience. I would be more than happy if OP says I am wrong, since I would then help OP with further information about data types and design :-) No offence to your post, just that I see it to be an hypothesis and not at all practical. Don't mind, we all are learning and sharing. You seem to be much senior to me as your points indicate :-) – Lalit Kumar B Feb 05 '15 at 14:10
  • @Lalit Kumar B: Thinking about this and looking at the math involved, you are probably right. (end_time - date_time) is supposed to result in a fraction of a day here, so it is very likely that both are datetimes. And even if they were not, it would still be highly unlikely for a value of 500 to make sense in this context. It is good that you have pointed that out. – Thorsten Kettner Feb 05 '15 at 15:14
0

IF end_time IS NULL THEN '500';

Just to make it more clear, '500' is not a number rather a string since it is enclosed within single quotation marks.

Now, end_time is. DATE data type or a timestamp, ideally. So, 500 makes no sense. You must convert it to appropriate type, whether 500 is days, hours, minutes, seconds, fraction of a second.

As in other answer it is suggested to use NVL(end_time, 500), it makes no sense. What does 500 - a date mean? Applying NVL is the need, however, you must convert it to the required value, else those are two different data types and Oracle won't allow it.

UPDATE

In my opinion,

Difference between two dates gives the number of days to the precision of seconds converted back to days. But, difference between an arbitrary number and a date makes no sense.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • .. i need to put 500 MINUTES as the minimum value if the end time is NULL. start time and end time are of date datatype ..and yes i am quite new to sql and oracle. I would really appreciate if you could help me out with this query :) –  Feb 06 '15 at 13:12
  • i am able to get the right difference between the start time and end time in the table where both of them have a value.The problem is just the ones where end time is NULL. –  Feb 06 '15 at 13:14
  • Go with a case statement, `case when end_time is null then 500 else (end_time - start_time)*24*60 end` – Lalit Kumar B Feb 06 '15 at 13:17
  • Show what exactly are your doing. It is impossible to debug a code which I can't see. Edit your question and copy paste your session details. – Lalit Kumar B Feb 06 '15 at 15:09
  • i am not able to post the pic cuz of low reputation here. –  Feb 06 '15 at 15:23
  • basically i was given a sql server query and was asked to convert it for oracle. Here is the SQL Server query Where DATEDIFF(minute,t.start_time,coalesce(t.end_time,@v_sPeriodEnd))> a2.Above conditions : Where the difference between the start_time and end_time is above the value a2.above, When END_Time is null, replace it with parameter value. –  Feb 06 '15 at 15:25
  • No need of posting a pic, just copy paste your SQL*Plus session. If you are using any GUI based tools like SQL developer or TOAD, you could still copy paste your code is while you execute your code as script. By the way, any reason for not accepting it as an answer? If it doesn't help you, then fine. – Lalit Kumar B Feb 06 '15 at 15:53
  • SQL> select * from ncr; START_TIM END_TIME A2 SNO --------- --------- ---------- ---------- 11-MAY-13 15-MAY-13 500 1 18-MAY-13 20-MAY-13 500 2 30-JAN-15 500 3 31-JAN-15 03-FEB-15 500 4 SQL> select * from NCR 2 case when end_time is null then 500 else (end_time - start_time)*24*60 end; case when end_time is null then 500 else (end_time - start_time)*24*60 end * ERROR at line 2: ORA-00933: SQL command not properly ended –  Feb 06 '15 at 16:30
0

I assumed that start_time and end_time columns have number as datatype, for this calculation you need to select these specific columns and not all (*). Comparison is in where clause, this works in oracle11.

select ((NVL(END_TIME, 500)-START_TIME) * 24 * 60) from TABLE_NAME where ((NVL(END_TIME, 500)-START_TIME) * 24 * 60) > 1;
zKaj
  • 9
  • 4