Create a table using a while loop 1

[CAFCrew]

HI Everyone,

I am fairly new to Oracle development having come from the world of TSQL and am struggling with the syntax slightly.

I need to create a table that will find out a maximum ID from another table, then create a row for every ID from 0 - the maximum for every hour in the day. i.e.

Hour ID
0000 1
0100 1
0200 1
0300 1
0400 1
0500 1
0600 1
0700 1
0800 1
0900 1
1000 1
---up to 2300---
then start on the next id

Hour ID
0000 2
0100 2
0200 2
0300 2
0400 2
0500 2
0600 2
0700 2
0800 2
0900 2
1000 2
---up to 2300---

etc till all the ID's have this up to the max in the table. This is different on different sites.

Can anyone help. I have looked on lots of pages but cant find anything similar and cant get my head around the oracle syntax when using loops or variables.

I would very much appreciate some help with this

Cafcrew
aka.Matt

 

[SantaMufasa]

Here you go, CAF:

declare
    max_id number;
begin
    begin
        execute immediate 'drop table caf';
    exception
        when others then null;
    end;
    execute immediate 'create table caf (hour varchar2(4), id number)';
    select max(id) into max_id from s_region;
    for x in 1..max_id loop
        for y in 0..23 loop
            insert into caf values (substr('0000'||y,-4),x);
        end loop;
    end loop;
    commit;
end;
/

PL/SQL procedure successfully completed.

select * from caf;

HOUR         ID
---- ----------
0000          1
0001          1
0002          1
0003          1
0004          1
0005          1
0006          1
0007          1
0008          1
0009          1
0010          1
0011          1
0012          1
0013          1
0014          1
0015          1
0016          1
0017          1
0018          1
0019          1
0020          1
0021          1
0022          1
0023          1
0000          2
0001          2
0002          2
0003          2
0004          2
0005          2
0006          2
0007          2
0008          2
0009          2
0010          2
0011          2
0012          2
0013          2
0014          2
0015          2
0016          2
0017          2
0018          2
0019          2
0020          2
0021          2
0022          2
0023          2

24 rows selected.

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[CAFCrew]

Thats impressive. I dont really understand how it works though?

I wrote code to temporarily do this and there were hundreds of lines of it!

Thank you so much for this, its so much neater this way

Matt

 

[SantaMufasa]

CAFCrew,

You are certainly welcome to ask questions about any of the above code that you do not understand. PL/SQL code is not always intuitive. I would never recommend relying upon code that puzzles you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[CAFCrew]

Thanks Mufasa,

I have had to slightly edit it to return the 1-23 as hours as it was putting them in the minutes, but apart from that small change it works perfectly.

The : "substr('0000'||y,-4),x);" was a bit confusing. I am not sure what this is doing?

I understand what you are setting the "x" and "y" things to do in this bit :

for x in 1..max_id loop
for y in 0..23 loop


but not really where "x" and "y" have come from? are they just generic variables or something that are part of the code when doing loops?

Thanks for this, its much appreciated.

Matt

 

[SantaMufasa]

substr('0000'||y,-4),x);" was a bit confusing. I am not sure what this is doing?

This means:

1) '0000'||y: Concatenate four zeroes to the front of the current value of the variable "y".
2) -4: extract the last four characters from the concatenated string.
3) INSERT into the CAF table's two columns, 1) the results of the concatenate/substring and 2) the current value of "x".

but not really where "x" and "y" have come from? are they just generic variables or something that are part of the code when doing loops?

Yes...Good intuition!

Let me know if you have more questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[CAFCrew]

cool. All seems to make sense

Thanks again for this. Very helpful.

Matt

 

[Beilstwh]

How about a simple query

>desc x
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(4)


>select * from x;

ID
----------
1
23

select z.hour,id_num
from (select to_char(rownum,'00')||'00' hour
from all_objects
where rownum < 24) z,
(select rownum id_num
 from all_objects
where rownum  <= (select max(id) from x) )y
order by 2,1;

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

Bill,

Yes, I prefer (when possible and more economical) using straight SQL over PL/SQL, as well.

To do what CAF requests, however, your code needs a couple of slight adjustments (to begin at midnight instead of at 01:00):

select z.hour,id_num
from (select to_char(rownum-1,'00')||'00' hour
from all_objects
where rownum <= 24) z,
(select rownum id_num
 from all_objects
where rownum  <= (select max(id) from s_region) )y
order by 2,1;

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Beilstwh]

Your right, I didn't know if the user knew that it could be done with a simple select instead of using a procedure. I was just showing him another option. That being said, your answer was the correct one for the specs given.

Bill
Oracle DBA/Developer
New York State, USA