8 Replies Latest reply on Oct 13, 2011 8:28 PM by 879605

    how to loop sql query

    879605
      hi ,

      i've already define date value as below ,
      DEFINE START_TIME = 2011-08-12 09:00:00
      DEFINE END_TIME = 2011-08-12 10:00:00
      And Quering the table depending on the Defined date as below ,
      select * from My_Table
      where
      ( to_date(Accounting_Start_Time,'yyyy-mm-dd HH24:MI:SS') >= to_date('&start_time','yyyy-mm-dd HH24:MI:SS') and to_date(Accounting_Stop_Time,'yyyy-mm-dd HH24:MI:SS') <= to_date('&end_time','yyyy-mm-dd HH24:MI:SS')
      OR to_date(Accounting_Start_Time,'yyyy-mm-dd HH24:MI:SS') >= to_date('&start_time','yyyy-mm-dd HH24:MI:SS') and to_date(Accounting_Stop_Time,'yyyy-mm-dd HH24:MI:SS') >=to_date('&end_time','yyyy-mm-dd HH24:MI:SS') and to_date(Accounting_Start_Time,'yyyy-mm-dd HH24:MI:SS') <= to_date('&end_time','yyyy-mm-dd HH24:MI:SS')
      OR to_date(Accounting_Start_Time,'yyyy-mm-dd HH24:MI:SS') <= to_date('&start_time','yyyy-mm-dd HH24:MI:SS') and to_date(Accounting_Stop_Time,'yyyy-mm-dd HH24:MI:SS') <= to_date('&end_time','yyyy-mm-dd HH24:MI:SS') and to_date(Accounting_Stop_Time,'yyyy-mm-dd HH24:MI:SS') >= to_date('&start_time','yyyy-mm-dd HH24:MI:SS')
      OR to_date(Accounting_Start_Time,'yyyy-mm-dd HH24:MI:SS') <= to_date('&start_time','yyyy-mm-dd HH24:MI:SS') and to_date(Accounting_Stop_Time,'yyyy-mm-dd HH24:MI:SS') >= to_date('&end_time','yyyy-mm-dd HH24:MI:SS'));
      but now i want to query the table on hour basis ,

      Any help please ,
        • 1. Re: how to loop sql query
          Frank Kulash
          Hi,
          876602 wrote:
          hi ,

          i've already define date value as below ,
          DEFINE START_TIME = 2011-08-12 09:00:00
          DEFINE END_TIME = 2011-08-12 10:00:00
          And Quering the table depending on the Defined date as below ,
          select * from My_Table
          where
          ( to_date(Accounting_Start_Time,'yyyy-mm-dd HH24:MI:SS') >= to_date('&start_time','yyyy-mm-dd HH24:MI:SS') and to_date(Accounting_Stop_Time,'yyyy-mm-dd HH24:MI:SS') <= to_date('&end_time','yyyy-mm-dd HH24:MI:SS')
          OR to_date(Accounting_Start_Time,'yyyy-mm-dd HH24:MI:SS') >= to_date('&start_time','yyyy-mm-dd HH24:MI:SS') and to_date(Accounting_Stop_Time,'yyyy-mm-dd HH24:MI:SS') >=to_date('&end_time','yyyy-mm-dd HH24:MI:SS') and to_date(Accounting_Start_Time,'yyyy-mm-dd HH24:MI:SS') <= to_date('&end_time','yyyy-mm-dd HH24:MI:SS')
          OR to_date(Accounting_Start_Time,'yyyy-mm-dd HH24:MI:SS') <= to_date('&start_time','yyyy-mm-dd HH24:MI:SS') and to_date(Accounting_Stop_Time,'yyyy-mm-dd HH24:MI:SS') <= to_date('&end_time','yyyy-mm-dd HH24:MI:SS') and to_date(Accounting_Stop_Time,'yyyy-mm-dd HH24:MI:SS') >= to_date('&start_time','yyyy-mm-dd HH24:MI:SS')
          OR to_date(Accounting_Start_Time,'yyyy-mm-dd HH24:MI:SS') <= to_date('&start_time','yyyy-mm-dd HH24:MI:SS') and to_date(Accounting_Stop_Time,'yyyy-mm-dd HH24:MI:SS') >= to_date('&end_time','yyyy-mm-dd HH24:MI:SS'));
          Accounting_Start_Time and Accounting_Stop_Time should really be DATEs, not strings. If they were DATEs, then you wouldn't need to to call TO_DATE every time you need to use them. The code would be easier to write and much more efficient to run.
          Given that you have strings in a format suitable for sorting, then it would be simpler, faster, and less error-prone if you just compared the strings, and not use TO_DATE at all.

          A simpler way to test if the range x_start to x_stop overlaps with the range y_start to y_stop is
          WHERE     x_start <= y_stop
          AND     y_start <= x_stop
          That is, two ranges overlap if and only if each one starts before the other one ends. If that's not obvious (and it certainly wasn't obvious to me when I first heard it), then look at it this way: two ranges do not overlap if and only if one of them begins after the other one ends.
          but now i want to query the table on hour basis ,
          It's unclear what you mean.
          Perhaps you want to GROUP BY SUBSTR (Accounting_Start_Time, 1, 13) (that is, the part that represents the day and hour, but not the minutes and seconds). If you GROUP BY, you'll neeed to use aggregate fucntions (such as MIN) on the other columns.
          Any help please ,
          Any help you can give would result in getting a solution quicker.
          Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables.
          Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.
          Always say which version of Oracle you're using.

          Edited by: Frank Kulash on Oct 12, 2011 3:07 PM
          • 2. Re: how to loop sql query
            Syed Ullah
            When you have so many 'and' and 'or' operators in a query, you should really use parentheses to enforce some kind of precedence.

            It is kind of hard to figure out what your query is doing.

            May be you can post some table data and the desired result.
            • 3. Re: how to loop sql query
              879605
              Thank you Frank for helpful infomation and i'm sorry for unclear Unclear question , I meant do the query on each hour like below ,

              Do the query where the hour between 01:00:00 and 02:00:00
              Do the query where the hour between 02:00:00 and 03:00:00
              Do the query where the hour between 03:00:00 and 04:00:00
              .
              .
              .


              this samble of my data
              REM INSERTING into MY_TABLE
              Insert into MY_TABLE (MSID,CORRELATION_ID,ACCOUNTING_START_TIME,ACCOUNTING_STOP_TIME,"Duration (Seconds)",HR,BSID,"Traffic (KB)") values ('5C4CA98EAC03','100034E44C04608D','2011-08-12 08:56:17','2011-08-12 09:16:32',1215,'00:20:15','9410',4629);
              Insert into MY_TABLE (MSID,CORRELATION_ID,ACCOUNTING_START_TIME,ACCOUNTING_STOP_TIME,"Duration (Seconds)",HR,BSID,"Traffic (KB)") values ('5C4CA98EAC01','100014E44CD20099','2011-08-12 09:50:06','2011-08-12 12:10:28',8422,'02:20:22','9420',77894);
              Insert into MY_TABLE (MSID,CORRELATION_ID,ACCOUNTING_START_TIME,ACCOUNTING_STOP_TIME,"Duration (Seconds)",HR,BSID,"Traffic (KB)") values ('5C4CA98EAC01','100014E44CD20099','2011-08-12 09:50:06','2011-08-12 12:10:28',8422,'02:20:22','9420',77894);
              Insert into MY_TABLE (MSID,CORRELATION_ID,ACCOUNTING_START_TIME,ACCOUNTING_STOP_TIME,"Duration (Seconds)",HR,BSID,"Traffic (KB)") values ('5C4CA98EAC17','100024E44D19A096','2011-08-12 10:10:13','2011-08-12 10:45:41',2128,'00:35:28','9410',662);
              Insert into MY_TABLE (MSID,CORRELATION_ID,ACCOUNTING_START_TIME,ACCOUNTING_STOP_TIME,"Duration (Seconds)",HR,BSID,"Traffic (KB)") values ('5C4CA98EABDC','100054E44D3B80B8','2011-08-12 10:18:13','2011-08-12 10:21:19',186,'00:03:06','9400',1750);
              Insert into MY_TABLE (MSID,CORRELATION_ID,ACCOUNTING_START_TIME,ACCOUNTING_STOP_TIME,"Duration (Seconds)",HR,BSID,"Traffic (KB)") values ('5C4CA98EABDC','100014E44D47709A','2011-08-12 10:21:25','2011-08-12 10:45:59',1474,'00:24:34','9400',1918);
              Insert into MY_TABLE (MSID,CORRELATION_ID,ACCOUNTING_START_TIME,ACCOUNTING_STOP_TIME,"Duration (Seconds)",HR,BSID,"Traffic (KB)") values ('5C4CA98EABDC','100054E44DA3B0B9','2011-08-12 10:46:02','2011-08-12 16:42:33',21391,'05:56:31','9400',221782);
              and this is my_table

              desc mytable
              Name Null Type
              --------------------- ---- ------------
              MSID VARCHAR2(20)
              CORRELATION_ID VARCHAR2(64)
              ACCOUNTING_START_TIME VARCHAR2(19)
              ACCOUNTING_STOP_TIME VARCHAR2(19)
              Duration (Seconds) NUMBER
              HR VARCHAR2(8)
              BSID VARCHAR2(4)
              Traffic (KB) NUMBER


              My DB is 10G R2

              Edited by: 876602 on 12/10/2011 12:55 م
              • 4. Re: how to loop sql query
                Frank Kulash
                Hi,

                Sorry, it's still not clear what you want.
                I see the INSERT statements now, but not the CREATE TABLE statement, anything about your Oracle version, or, most imporatantly, the results you're trying to produce.

                Post the exact results you want from that sample data. Format the output, so it's easy to read. Whenever you post formatted text (such as query results) on this site, type these 6 characters:

                \
                (small letters only, inside curly brackets) before and after each section of formatted text, to preserve spacing.
                
                
                Do you still want to pass start_time and end_time parameters to the query (and have it repeat something fro every hour between those points)?  If so, give a couple of sets of parameters and the results you want from the same sample data for each set.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                • 5. Re: how to loop sql query
                  879605
                  sorry again ,
                  create table my_table
                  (MSID VARCHAR2(20), 
                  CORRELATION_ID VARCHAR2(64) ,
                  ACCOUNTING_START_TIME VARCHAR2(19) ,
                  ACCOUNTING_STOP_TIME VARCHAR2(19) ,
                  Duration (Seconds) NUMBER ,
                  HR VARCHAR2(8) ,
                  BSID VARCHAR2(4) ,
                  Traffic (KB) NUMBER);
                  my DB 10G R2
                  what i want exactly is to find the result when the time is ,
                  between 2011-08-12 09:00:00 and 2011-08-12 10:00:00
                  then quering the table
                  select count(msid) from my_table
                  where to_date(Accounting_Start_Time,'yyyy-mm-dd HH24:MI:SS') <= to_date('2011-08-12 10:00:00','yyyy-mm-dd HH24:MI:SS')
                  and to_date('2011-08-12 09:00:00','yyyy-mm-dd HH24:MI:SS') <= to_date(Accounting_Stop_Time,'yyyy-mm-dd HH24:MI:SS')
                  then when the time is ,
                  between 2011-08-12 10:00:00 and 2011-08-12 11:00:00
                  then quering the table again
                  select count(msid) from my_table
                  where to_date(Accounting_Start_Time,'yyyy-mm-dd HH24:MI:SS') <= to_date('2011-08-12 11:00:00','yyyy-mm-dd HH24:MI:SS')
                  and to_date('2011-08-12 10:00:00','yyyy-mm-dd HH24:MI:SS') <= to_date(Accounting_Stop_Time,'yyyy-mm-dd HH24:MI:SS')
                  and repeat this for each hour so that mean i have to repeat this 24 time ,

                  expected results is
                  count(msid)        date&time
                  ---------------    -----------------   
                  2              2011-08-12 12:00:00  to 2011-08-12 01:00:00
                  3              2011-08-12 01:00:00  to 2011-08-12 02:00:00
                  2              2011-08-12 02:00:00  to 2011-08-12 03:00:00
                  2              2011-08-12 03:00:00  to 2011-08-12 04:00:00
                  2              2011-08-12 04:00:00  to 2011-08-12 05:00:00
                  .
                  . 
                  .
                  is there any way to do this automatically ?

                  Edited by: 876602 on 12/10/2011 02:11 م
                  • 6. Re: how to loop sql query
                    Frank Kulash
                    Hi,

                    It's not very hard in SQL*Plus.

                    First of all, put your query in a script file, like this one, called hour_query.sql:
                    --     hour_query.sql          Show data from my_table for a given time period
                    
                    
                    PROMPT     The data below shows the period from &1 to &2
                    PROMPT
                    
                    SELECT     *
                    FROM     my_table
                    WHERE     accounting_start_time     <= '&2'
                    AND     '&1'               <= accounting_stop_time
                    ;
                    The parameters &1 and &2 are the starting and ending times, e.g. "2011-08-12 09:00:00".
                    Note that this query does not use TO_DATE. If you have mal-formed strings in the columns that should be DATEs, no error will occur.

                    Assuming hour_query.sql is located on p:\some_dir\, you want another script that runs hour_query.sql 24 times, like this one, which I'll call all_hours.sql:
                    @p:\some_dir\hour_query  "2011-08-12 09:00:00"  "2011-08-12 10:00:00"
                    @p:\some_dir\hour_query  "2011-08-12 10:00:00"  "2011-08-12 11:00:00"
                    @p:\some_dir\hour_query  "2011-08-12 11:00:00"  "2011-08-12 12:00:00"
                    ...
                    (I just did 3 hours to test it. You can easily make that 24 hours.)

                    The following code creates, and then runs, all_hours.sql:
                    -- Turn off SQL*Plus features that interfere with raw output
                    SET     FEEDBACK     OFF
                    SET     PAGESIZE     0
                    SET     VERIFY          OFF
                    
                    -- Write all_hours.sql
                    SPOOL     p:\&some_dir\all_hours.sql
                    
                    WITH     got_start_time     AS
                    (
                         SELECT TO_DATE ( '2011-08-12 09:00:00'
                                            , 'YYYY-MM-DD HH24:MI:SS'
                                     ) AS start_time
                         FROM    dual
                    )
                    SELECT     '@p:\some_dir\hour_query  "'
                        ||  TO_CHAR ( start_time + ((LEVEL - 1) / 24)
                              , 'YYYY-MM-DD HH24:MI:SS'
                              )
                        ||  '"  "'
                        ||  TO_CHAR ( start_time + ( LEVEL      / 24)
                              , 'YYYY-MM-DD HH24:MI:SS'
                              )
                        ||  '"'
                    FROM    got_start_time
                    CONNECT BY     LEVEL <= 3     -- You can make this 24, or any other number
                    ;
                    
                    
                    SPOOL     OFF
                    
                    -- Turn on SQL*Plus features turned off earlier
                    SET     FEEDBACK     ON
                    SET     PAGESIZE     50
                    -- Except VERIFY.  leave that OFF while all_hours runs
                    --SET     VERIFY          OFF
                    
                    
                    -- Run all_hours.sql
                    @p:\some_dir\all_hours.sql
                    
                    SET     VERIFY     ON
                    • 7. Re: how to loop sql query
                      Most Wanted!!!!
                      may this'll help you
                      WITH t AS
                           (SELECT TO_DATE ('26-aug-2011 10:10:10',
                                            'dd-mon-yyyy hh24:mi:ss'
                                           ) start_date_time,
                                   TO_DATE ('26-aug-2011 10:12:10',
                                            'dd-mon-yyyy hh24:mi:ss'
                                           ) end_date_time
                              FROM DUAL)
                      SELECT    TRUNC (end_date_time - start_date_time)
                             || ' days '
                             || TO_CHAR (TRUNC (SYSDATE) + (end_date_time - start_date_time),
                                         'hh24:mi:ss'
                                        ) time_diff
                        FROM t
                      /
                      regards,
                      friend
                      • 8. Re: how to loop sql query
                        879605
                        thank you Frank for your help