Search code examples
sqloracle-databaseoracle19c

Oracle SQL Query to properly position the data based on time intervals

I have table load_ext which is an external table for the below file structure

customer interval_type data_count Start_time interval1 interval2 interval3 ,..interval24
67891 60 5 06022022040000AM 0.07 0.767 0.65 0.69 0 0...
12345 60 8 06022022120000PM 0.07 0.767 0.65 0.69 0.767 0.69 0 0

To explain the above columns, All columns are varchar2. Interval type is in minutes, data_count column says the number of intervals to be posted starting from the start_time column, Interval1 is the value for 00:00:00 to 01:00:00 AM and likewise. Target table will have the same structure but the above intervals should be moved to the respective columns. For example, the value of interval1 column in the first row should be moved to column interval4 and the same for all other columns to the respective interval periods.

My target table should have the data like below

customer interval_type data_count Start_time interval1 interval2 interval3 interval4 interval5 ..interval24
67891 60 5 06022022040000AM 0 0 0 0.07 0.767 0.65 0.81 0 0
12345 60 8 06022022120000PM 0 0 0 0 0 0 0 0 0.07 0.65 0.07 0.65 0 0...

I am providing the table data with ',' delimiter as the table structure is too big to post in the same format. This has to be done in Oracle only, we are using Oracle 19.

Solution

  • Thank you for trying to help me, I figured out an alternate and faster way of doing it. Instead of using the load_ext which is an external table, I preferred using the utl_file to read the file directly line by line and writing the same by to new file, in between, I used an existing table that has rows already created for each interval.

    My existing for interval periods has start and end time stamp for every 15 mins and its respective period number. So I just considered this period number based on the timestamp from the file for the respective line and generate as many spaces and concatenate to the last column of the file. The last column in the file contains all the intervals but only limited to the number provided in the data_count field of the file.

    My steps are like this.

    1. Rename and Open the source file in loop
    2. Read the one line at a time, retrieve the data field.
    3. Based on the timestamp, get the period number from the existing table and add data_count to this.
    4. Generate as many spaces as derived in step 3 and concatenate to data field.
    5. write this to a new file named as actual source file.

    My job is done in a faster way compared to the SQL query.

    Thank You Again