asked
create a table
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
I am not an expert in oracle but I do have the luxury of verifying the syntax in Oracle and @Oleg's query was correct as usual. :) Another way of achieving the same could be select to_char(to_date(SYSDATE) + numToDSInterval(rownum - 1, 'minute'), 'hh24:mi') from (select rownum from dual connect by rownum
@Usman Butt Thank you! Actually, I am guilty of being not active 2014 - 2016. During that time I worked at the place which I call easily call absolutely the best place I ever worked (or will ever work in the future), but there was so much work and so many tight drop dead dates that I did not have any time to visit this site. I will try my best to stay active at this time. It is good to see you back! By the way, this is a very neat script you posted in the comments, looks better than the script in my answer because it uses the single select of 1440 rows (courtesy of numToDSInterval) rather than marriage of the 60 and 24 like I have in my script.
@blackwidow Yes, the way it is written is guaranteed to run forever due to that OR part which is not "linked" to other AND conditions, and therefore, produces many more rows than needed and most of them are needlessly repeated/recounted. In a mean time, it looks like you did not want to use any of the offered solutions to the actual question (create a table with 1440 rows and values in hh:mm format, one row per minute of the day). There was a script in my answer and in @Usman Butt comment, both scripts produces ***exactly*** what you have asked. It would be nice though if you let us know whether the scripts work/help with solving original problem in question or not (even though we know they do). What you are asking in your comment is a different question but I will have a look later today and add it to my answer. Thank you.
@blackwidow I totally agree with @Oleg, you should have asked this as a separate question. Please post the DDL of your table, some sample data and the required output in that question. Help us to help you. Moreover, since you got at least the basic idea to solve your original problem from @Oleg's answer, please mark it as the answer if possible.
@Oleg :- And yes your solution for minutes table was right. I worked using it but this is a completely different scenario where any help would be appreciated.Thank You.
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.
@blackwidow Your statements are a bit confusing. This is why I asked you to post the DDL of the table, some data and the required output in a separate question. If we can help you on this thread, we will help you on the new thread as well. This to and fro communication will not help any of us. So please help us to help you.
I wanted to add this answer as a comment but not able to do so :( Even "convert to comment" not working.
@blackwidow It really looks like you need to either post a new question or let us know whether there is another question which you already asked in the past. In your question, please describe the problem, and provide a small set of sample data and desired output. This way it will be easier to figure out what needs to happen. Thank you.
@Usman Butt @Oleg : I am posting the question along with the requirements.
19 People are following this question.
