x

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
more ▼

asked Jan 11 at 07:59 AM in Default

avatar image

blackwidow
11 1 4

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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 <= 24
     ) hr,
     (
         select rownum - 1 m from dual connect by rownum <= 60    
     ) mnt

If this works but the order of rows is incorrect then add the order by h, m to the query.

Hope this helps.

Oleg

more ▼

answered Jan 11 at 02:25 PM

avatar image

Oleg
19.5k 3 7 28

@Oleg : Thanks for the update....I'm working on it.

Jan 12 at 08:04 AM blackwidow

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 <= 1440)h;

With TO_CHAR you can get any format you want.

@Oleg Actually, the whole point was to say "Hi" to you. :) It has been a long time and good to see you helping out others continuously. Hats off to you.

Jan 12 at 02:08 PM Usman Butt

@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.

5 days ago Oleg

@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.

4 days ago Oleg

@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.

4 days ago Usman Butt
(comments are locked)
10|1200 characters needed characters left

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 <= 1440
        )
        select to_char(m.minute, 'MM/DD/YYYY HH24:MI') as minute, count(o.order_id) as 
        orders_count
        from minutes m
        left join ord_order o
       on odcreate >= 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.

more ▼

answered Jan 17 at 02:23 PM

avatar image

Usman Butt
14k 6 13 21

I wanted to add this answer as a comment but not able to do so :( Even "convert to comment" not working.

4 days ago Usman Butt

@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.

4 days ago Oleg

@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.

4 days ago Usman Butt

@Usman Butt @Oleg : I am posting the question along with the requirements.

4 days ago blackwidow
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1170
x33

asked: Jan 11 at 07:59 AM

Seen: 71 times

Last Updated: 4 days ago

Copyright 2018 Redgate Software. Privacy Policy