I need to create a minute table similar to the one given below in ORACLE DBMS. I am finding it hard to do it in ORACLE . It throws some or the other kind of error everytime. create table Minutes([Minute] varchar(5)) declare @ToDay datetime = '2018-01-11 00:00:00' --loop to fill minutes table with all minutes in a day while cast(@ToDay as Date) <'2018-01-12' Begin insert into Minutes(Minute) select left(cast( @ToDay as Time),5) set @ToDay = DATEADD(MINUTE,1, @ToDay) End
You can do many things in Oracle PL-SQL, but you cannot use the SQL Server specific T-SQL. During recent years the syntax differences between these flavours of SQL have become smaller, but still, there are some differences. For example, never use the VARCHAR data type in Oracle, it has been pretty much deprecated for last 40 years or so, use VARCHAR2 instead. DATEADD function exists, but the parameters are different, and there is no way to add minutes, some other function must be used for that. From the question definition it looks like all you need is the table with one column named Minute and 1,440 rows storing values from 00:00 to 23:59. If this assumption is correct then query below should work. I don't have access to any Oracle environment at this time so my query is written blindly with no possibility to check whether there are any syntax errors or not. I hope that it works as written. The idea is to generate numbers 0 through 23 (hours), numbers 0 through 59 (minutes) and then cross join the 2 to come up with all their combinations (formatted as hh:mm). Here is the script which I hope works as is: select lpad(hr.h, 2, '0')||':'||lpad(mnt.m, 2, '0') "Minute" from ( select rownum - 1 h from dual connect by rownum
As far as your new query is concerned, as already pointed out by @Oleg there seems to be some logical problems with your query, you would have to change it something like with minutes (minute) as ( select cast(trunc(sysdate-1) as timestamp) + interval '1' minute * (level - 1) from dual connect by level = cast(trunc(sysdate-1) as timestamp) and odcreate < cast(trunc(sysdate) as timestamp) --it is redundant to sysdate -1 add interval 1 day and odcomplete is not null and odcomplete >= m.minute and odcomplete < m.minute + interval '1' minute and (to_char(odcomplete, 'hh24:MI') >= to_char(odcreate, 'hh24:MI')) --implement as a constraint group by m.minute order by m.minute; I believe complete date should always be greater than the created date, and this should have been implemented as a constraint. But this all is a guess work, and if you need better help, then post a new question with all the details. Cheers.