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.
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:
If this works but the order of rows is incorrect then add the order by h, m to the query.
Hope this helps.
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
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.
answered Jan 17 at 02:23 PM