12 Replies Latest reply on Apr 18, 2014 6:04 PM by sanju2

    Help on replacing for loop with Decode

    sanju2

      Hi All,

       

      Please take a look at the query and proceeding information. Request your help.

       

      select count(*) UNRLS from lt_leg

      where to_char(NVL(est_dtm, schd_dtm),'mm/dd/yyyy') =

                                  to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '"+i+"' HOUR,'mm/dd/yyyy')

         and to_char(NVL(est_dtm, schd_dtm),'hh24') between

                                  to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '"+i+"' HOUR,'hh24')

                                  and

                                  to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '"+i+"' HOUR,'hh24')

         and sec in ("+s+") and ls_stat is null

         and act_dtm is null and comp_tm is null;

       

      I am running the query in for loop where the loop iterates for 8 time for each external record. [for(int i=8;...) ]

       

      "i" in the query is loop numbe that sets Hour intervel. "s" in the query is tha value coming from

      external for loop where "s" is set of values like ('abc','xyz','pqr'). They are different set every time.

      External record could be 50 to 60 in numbers. This mean this query executes 400 to 480 times. This is killing.

       

      I am trying to make use of Decode. Any help on this is much appreciated.

       

      Oracle Ver. 10g R2

       

      Thank you in adv.

      Regards,

       

      In addition to above -

      looking to use something like -

       

      SELECT (DECODE()),

            (DECODE()),

            (DECODE()),

            (DECODE()),

            (DECODE()),

            (DECODE()),

            (DECODE()),

            (DECODE())

          

            that would get me records like -

          

            19:00 20:00 21:00 22:00 23:00 00:00 01:00 02:00

            -------------------------------------------------------------------------

             0      0       1        4       1       0        1        2

             2      1       0        0       1       1        2        0

          

      This would reduce total fetch to matching external loop iteration that is 50 or even less from 400 and 480.

       

      regards,

       

      Message was edited by: sanju2

        • 1. Re: Help on replacing for loop with Decode
          Frank Kulash

          Hi,

           

          Instead of running 400 or more separate queries, each producing 1 row, it will be much more efficient if you run 1 query that produces 400 or more rows.  Start by getting the 50 or more values of sec that you want into a table.  The best way to do this depends on what form those values come in, and also on your Oracle version.  In the worst case, you could INSERT them, 1 by 1, into a real table, perhaps a global temporary table, which I called s_table in the query bleow, but there might be some more convenient way to use a WITH clause to simulate a table.

           

          The single big query might look something like this:

           

          SELECT    s.sec
          ,         TRUNC (NVL (l.est_dtm, l.schd_dtm), 'HH')    AS hour
          ,         COUNT (*)                                    AS unrls
          FROM      lt_leg   l
          JOIN      s_table  s   ON   l.sec  = s.sec
          WHERE     l.ls_stat  IS NULL
          AND       l.act_dtm  IS NULL
          AND       l.comp_tm  IS NULL
          AND       NVL (l.est_dtm, l.schd_dtm)
                               BETWEEN  SYS_EXTRACT_UTC (CURRENT_TIMESTAMP)
                               AND      SYS_EXTRACT_UTC (CURRENT_TIMESTAMP) + INTERVAL '8' HOUR
          GROUP BY  s.sec
          ,         TRUNC (NVL (l.est_dtm, l.schd_dtm), 'HH')
          ;

          This query would produce 1 row of output for each distinct combination of sec and hour.  If you'd rather have one row per sec, and 8 separate columns for the different hours, then you can pivot the results.  (That won't make the query any faster; in fact, it will make it a tiny bit slower.)  Also, if you want 0's in the output for combinations of sec and hour that don't happen to occur in the data, then you can do an outer join.

           

          I don't fully understand what you need to do, or even what the query you posted is doing  (it seems to have some non-Oracle elements, maybe pre-processing instructions for your front end), so I don't imagine the query above is exactly what you need, but it should point you in the right direction.

           

          If you'd like help implementing this, post a little sample data (CREATE TABLE and INSERT statements), and the exact results you want from that data.  Simplify the problem.  For example, instead of needing  50 or more values of sec for 8 hours, post sample data for 3 values of sec for 2 hours.  If you want 0's in the output, include examples.

          Since the results depend on CURRENT_TIMESTAMP, it would be helpful to post a couple of different values of CURRENT_TIMESTAMP, and the results you would want from the same sample data for each value.

          See the forum FAQ: https://forums.oracle.com/message/9362002#9362002

          • 2. Re: Help on replacing for loop with Decode
            sanju2

            Thank you Sir.

            what you said is obviously desired and would be too good for me but that is little difficult for me to achieve. I thought of doing something like this for the moment and check the performance, if reducing [java] calls  to database might improve the performance of the report. Here it is [below query].

            Please take a look here. Could you please help.

             

            SELECT DECODE(2-2,0,(select count(*) from lt_leg

                            where to_char(NVL(est_dtm, schd_dtm),'mm/dd/yyyy') =

                                          to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '1' HOUR,'mm/dd/yyyy')

                              and to_char(NVL(est_dtm, schd_dtm),'hh24') between

                                          to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '1' HOUR,'hh24')

                                          and

                                          to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '1' HOUR,'hh24')

                              and sec in ("+s+") and ls_stat is null

                              and act_dtm is null and comp_tm is null),null) AS UNRLS1,

                  .............

                  ................

                    DECODE(2-2,0,(select count(*) from lt_leg

                                where to_char(NVL(est_dtm, schd_dtm),'mm/dd/yyyy') =

                                              to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '8' HOUR,'mm/dd/yyyy')

                                  and to_char(NVL(est_dtm, schd_dtm),'hh24') between

                                              to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '8' HOUR,'hh24')

                                              and

                                              to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '8' HOUR,'hh24')

                                  and sec in ("+s+") and ls_stat is null

                                  and act_dtm is null and comp_tm is null),null) AS UNRLS8                 

            FROM DUAL; 

             

            This way the call is reduced but the performance is still not so good. One query alone takes 0.621 avg 0.7 seconds but combined together in Decode it takes 4.439 or 5 seconds!. For 32 sets of sec that is what I pass in "s" it would still take 2.6 to 3 minutes!. In production environment when I connect and run the app; report generates in 4.5 minutes!!! and that's just for 32 rows!!. There its taking avg 9 seconds per row!!.

             

            The query I am running to get the secs that gets me 32 sets of sec are -

             

            select a.desk DESK, a.user_id USERID, a.user_name USERNAME, b.sector SECTOR, d.start_tm STARTTM, d.end_tm ENDTM

            from desk_login a, sec_desk_map b, desk_sect c, shft d

            where ((d.start_tm<=to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP),'hh24mi')

                      and d.end_tm>=to_char((SYS_EXTRACT_UTC(CURRENT_TIMESTAMP)),'hh24mi'))

                  or(start_tm > end_tm -- today to tomorrow shift

                      AND (start_tm <= to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) ,'hh24mi')

                            OR                                                           

                          end_tm >= to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) ,'hh24mi')

                          )

                    )

                  )

            and a.active = 'Y'

            and b.desk = a.desk and b.sector=c.sect_id and c.desk_id=d.desk_id and c.shft_id=d.shft_id order by 1;

             

            and I remember you helping me out on this one too. Thank you again. You helped me get midnight shift in the above query.

            It would certainly be good If I could combine all of them and create one query as you suggested and instead of making 32+ calls make one call and get all the records. That would be tough for me. May be with your help I might be able to do it.

             

            Sample output desired is -

             

            ....         Sec           .....    19:00   20:00   21:00    22:00   23:00   00:00    01:00   02:00

            -------     ------           .....    ---------   --------   --------   ---------   --------   --------    --------   ---------

            ......     A1,A2,A3    .....    0          0         1          4          1         0          1          2

            ......     C1,C2         .....    2          1         0          0          1         1          2          0

             

            Oracle Ver.: 10g R2

            front end: java

            purpose: Generation of report

             

             

            Thank you for all your help.

            Regards,

             

            Message was edited by: sanju2

            • 3. Re: Help on replacing for loop with Decode
              Frank Kulash

              Hi,

               

              Sorry, unless you post some sample data (CREATE TABLE and INSERT statements) for a simplified problem (maybe 2 or 3 different sec values, and 2 or 3 hours), and the results you want from that data, as I mentioned before, then I can't help.

               

              Again,

              and sec in ("+s+")

              is not Oracle.  You need a way to set those values into an Oracle table or result set.

              If there is a lookup table that has all possible values of sec, then that might be as simple as

              WITH   secs_wanted    AS

              (

                  SELECT  sec

                  FROM    lookup_table

                  WHERE   sec IN ("+s+")

              )

              • 4. Re: Help on replacing for loop with Decode
                sanju2

                Thank you sir for the continued support and help. My problem is I can not use a lookup or temp table or anything new that has to go to DBA for any reason (create new).


                Can you please help me put the below query in such a way that I am able to get the count every hour up to n hours (8 hours to be precise) in one shot using all the where conditions only once instead of executing the whole query eight times or n times inside decode. "s" being passed is obviously same for all the queries in decode for each call. Other conditions, as you may see, remain the same. As I mentioned when I execute the query individually they take approx 0.6 seconds but in decode, say imbedded 8 times, they take avg 9 seconds or 10 seconds. That would be 10x35 seconds for 35 rows or set of sec.


                SELECT DECODE(2-2,0,(select count(*) from lt_leg

                                where to_char(NVL(est_dtm, schd_dtm),'mm/dd/yyyy hh24') between

                                              to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '1' HOUR,'mm/dd/yyyy hh24')

                                              and

                                              to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '1' HOUR,'mm/dd/yyyy hh24')

                                  and sec in ("+s+") and ls_stat is null

                                  and act_dtm is null and comp_tm is null),null) AS UNRLS1,

                      .............

                      ................

                        DECODE(2-2,0,(select count(*) from lt_leg

                                    where to_char(NVL(est_dtm, schd_dtm),'mm/dd/yyyy hh24') between

                                                  to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '8' HOUR,'mm/dd/yyyy hh24')

                                                  and

                                                  to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '8' HOUR,'mm/dd/yyyy hh24')

                                      and sec in ("+s+") and ls_stat is null

                                      and act_dtm is null and comp_tm is null),null) AS UNRLS8                 

                FROM DUAL; 


                Here "s" is set of values like ('abc','xyz','pqr') which is difficult to go with bind variable so I concatenate it as string. I am using this query in a java application.


                Thank you .

                Regards,

                • 5. Re: Help on replacing for loop with Decode
                  Frank Kulash

                  Hi,

                   

                   

                  sanju2 wrote:

                   

                  Thank you sir for the continued support and help. My problem is I can not use a lookup or temp table or anything new that has to go to DBA for any reason (create new).

                  ...

                  You don't need to create any new tables.  You can use a WITH clause, and the result set can act like a table later in the query.

                  • 6. Re: Help on replacing for loop with Decode
                    sanju2

                    Thank you. I got your "With clause" suggestion.  This is good for bringing in the main query as a look up for set of sec that I use. I got the example and recalled how it works -

                    WITH dept_count AS (

                      SELECT deptno, COUNT(*) AS dept_count

                      FROM emp

                      GROUP BY deptno)

                    SELECT e.ename AS employee_name,

                      dc.dept_count AS emp_dept_count

                    FROM emp e,

                      dept_count dc

                    WHERE e.deptno = dc.deptno;

                     

                    Here with clause will get me all the sets of "sec" based on that the second part of the query will execute for every set of "sec". This will help me reduce the call to 1. This is too good. thank you. But what about the time interval that I have to use for 8 times to get up to eight individual hours of data, for what I use decode. How do I get around with that problem. This as you may see in Decode that I have posted. A solution to that too would be a great help.

                     

                    Thank you once again.

                    Regards,

                    • 7. Re: Help on replacing for loop with Decode
                      Frank Kulash

                      Hi,

                       

                      You can get separate COUNTs for any number of hours in a single query by including the hour in the GROUP BY clause, as I showed in reply #1 above.


                      Once again, if you need help, post some sample data (for a simplified case, maybe 2 or 3 differerent values of sec, and 2 or 3 hours) and the exact results you want from that data.

                      You can post the sample data in a WITH clause, if can't post CREATE TABLE and INSERT statements.

                      • 8. Re: Help on replacing for loop with Decode
                        sanju2

                        Sir - please find the details.

                         

                        Here is the output sample. They are actual (modified in appearance) that I am getting from my existing solutions but the problem as I mentioed is the time it takes to generate. And so I am trying to work on queries.

                         

                        DeskName/IdSectorsShiftReleasedUnreleased
                        InflightReleased22:0023:0000:0001:0002:0003:0004:0005:00
                        C05D/03I05, 13, I141500-22593496221001
                        C37T/05I621500-22590120010200
                        C40K/91I45, I471500-22590000000000

                         

                        The queries that I use are there in this post except for the 2 columns of relesed. data up to Shift I get in one query and for Unrelease in Decode.

                        In Unreleased section 22:00 23:00 are Hours current hours +1 and +2 .... +8; what you see in Interval of decode query.

                         

                        The With clause may have the query that I use for getting sectors (I posted). Like-

                         

                        WITH sectr_list AS (select a.desk DESK, a.user_id USERID, a.user_name USERNAME, b.sector SECTOR, d.start_tm STARTTM, d.end_tm ENDTM

                        from desk_login a, sec_desk_map b, desk_sect c, shft d

                        where ((d.start_tm<=to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP),'hh24mi')

                                  and d.end_tm>=to_char((SYS_EXTRACT_UTC(CURRENT_TIMESTAMP)),'hh24mi'))

                              or(start_tm > end_tm -- today to tomorrow shift

                                  AND (start_tm <= to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) ,'hh24mi')

                                        OR                                                            

                                       end_tm >= to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) ,'hh24mi')

                                      )

                                 )

                              )

                        and a.active = 'Y'

                        and b.desk = a.desk and b.sector=c.sect_id and c.desk_id=d.desk_id and c.shft_id=d.shft_id )

                        SELECT DECODE(2-2,0,(select count(*) from lt_leg

                                         where to_char(NVL(est_dtm, schd_dtm),'mm/dd/yyyy') =

                                                      to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '1' HOUR,'mm/dd/yyyy')

                                           and to_char(NVL(est_dtm, schd_dtm),'hh24') between

                                                      to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '1' HOUR,'hh24')   <------these are the concerns, are the reason for putting them in decode to make just 1 call for 8 hrs

                                                      and

                                                      to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '1' HOUR,'hh24')

                                           and sec in ("+s+") and ls_stat is null        <---------------------------------------------------------------------Here sectors will come from WITH CLAUSE

                                           and act_dtm is null and comp_tm is null),null) AS UNRLS1,

                               .............  

                               ................

                                DECODE(2-2,0,(select count(*) from lt_leg

                                             where to_char(NVL(est_dtm, schd_dtm),'mm/dd/yyyy') =

                                                          to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '8' HOUR,'mm/dd/yyyy')

                                               and to_char(NVL(est_dtm, schd_dtm),'hh24') between

                                                          to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '8' HOUR,'hh24')

                                                          and

                                                          to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '8' HOUR,'hh24')

                                               and sec in ("+s+") and ls_stat is null      <---------------------------------------------------------------------Here sectors will come from WITH CLAUSE

                                               and act_dtm is null and comp_tm is null),null) AS UNRLS8                  

                        FROM DUAL;

                         

                        Or may be we can just skip all other column in WITH CLAUSE as I can get them in one separate call and just use b.sector for feed to inline.

                         

                        Thank you.

                        Regards,

                        • 9. Re: Help on replacing for loop with Decode
                          sanju2

                          WITH CLAUSE seems to work but the count that I get using decode for each hour for each sector and desk, adds up and displays total of all the counts for that particular hour.  Please have a look.

                           

                          Desk       Sector    20:00 21:00 22:00 23:00 00:00 01:00 02:00 03:00

                          C01         I01         3       29      45     49      29     31      35     10

                          C01         I21         3       29      45     49      29     31      35     10

                          C03         I03         3       29      45     49      29     31      35     10

                          C03         I23         3       29      45     49      29     31      35     10

                          ............

                          ...............

                           

                           

                           

                          I have tried grouping at all levels but that does not help. Could you please give a hint as why it is doing so and what I need to do further. I am using the same approach that I sent in my previous post with sample data and WITH CLAUSE query.

                           

                           

                          Regards,

                           

                          Message was edited by: sanju2

                          • 10. Re: Help on replacing for loop with Decode
                            sanju2

                            Sir - The Query I used to get the above post result is -

                             

                             

                             

                            WITH sectr_list AS (

                              select a.desk DESK, b.sector SECTOR

                            from desk_login a, sec_desk_map b, desk_sect c , shft d

                            where ((d.start_tm<=to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP),'hh24mi')

                                      and d.end_tm>=to_char((SYS_EXTRACT_UTC(CURRENT_TIMESTAMP)),'hh24mi'))

                                  or(start_tm > end_tm

                                      AND (start_tm <= to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) ,'hh24mi')

                                            OR                                                            

                                           end_tm >= to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) ,'hh24mi')

                                          )))

                            and a.active = 'Y'

                            and b.desk = a.desk and b.sector=c.sect_id and c.desk_id=d.desk_id and c.shft_id=d.shft_id)

                            SELECT x.desk, y.sector, DECODE(2-2,0,(select count(*) from lt_leg l, sectr_list s

                                     where to_char(NVL(l.est_dtm, l.schd_dtm),'mm/dd/yyyy hh24') between

                                            to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '1' HOUR,'mm/dd/yyyy hh24')

                                            and

                                            to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '1' HOUR,'mm/dd/yyyy hh24')

                                       and l.sector =s.sector and l.ls_stat is null

                                       and l.act_dtm is null and l.comp_tm is null),null) AS UNRLSFLT1,

                                   DECODE(2-2,0,(select count(*) from lt_leg l, sectr_list s

                                             where to_char(NVL(l.est_dtm, l.schd_dtm),'mm/dd/yyyy hh24') between

                                                          to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '2' HOUR,'mm/dd/yyyy hh24')

                                                          and

                                                          to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '2' HOUR,'mm/dd/yyyy hh24')

                                               and l.sector =s.sector and l.ls_stat is null

                                               and l.act_dtm is null and l.comp_tm is null),null) AS UNRLSFLT2,

                                   DECODE(2-2,0,(select count(*) from lt_leg l, sectr_list s

                                             where to_char(NVL(l.est_dtm, l.schd_dtm),'mm/dd/yyyy hh24') between

                                                          to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '3' HOUR,'mm/dd/yyyy hh24')

                                                          and

                                                          to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '3' HOUR,'mm/dd/yyyy hh24')

                                               and l.sector =s.sector and l.ls_stat is null

                                               and l.act_dtm is null and l.comp_tm is null),null) AS UNRLSFLT3,

                                   DECODE(2-2,0,(select count(*) from lt_leg l, sectr_list s

                                             where to_char(NVL(l.est_dtm, l.schd_dtm),'mm/dd/yyyy hh24') between

                                                          to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '4' HOUR,'mm/dd/yyyy hh24')

                                                          and

                                                          to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '4' HOUR,'mm/dd/yyyy hh24')

                                               and l.sector =s.sector and l.ls_stat is null

                                               and l.act_dtm is null and l.comp_tm is null),null) AS UNRLSFLT4,

                                   DECODE(2-2,0,(select count(*) from lt_leg l, sectr_list s

                                             where to_char(NVL(l.est_dtm, l.schd_dtm),'mm/dd/yyyy hh24') between

                                                          to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '5' HOUR,'mm/dd/yyyy hh24')

                                                          and

                                                          to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '5' HOUR,'mm/dd/yyyy hh24')

                                               and l.sector =s.sector and ls_stat is null

                                               and l.act_dtm is null and l.comp_tm is null),null) AS UNRLSFLT5,

                                   DECODE(2-2,0,(select count(*) from lt_leg l, sectr_list s

                                             where to_char(NVL(l.est_dtm, l.schd_dtm),'mm/dd/yyyy hh24') between

                                                          to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '6' HOUR,'mm/dd/yyyy hh24')

                                                          and

                                                          to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '6' HOUR,'mm/dd/yyyy hh24')

                                               and l.sector =s.sector and ls_stat is null

                                               and l.act_dtm is null and l.comp_tm is null),null) AS UNRLSFLT6,

                                   DECODE(2-2,0,(select count(*) from lt_leg l, sectr_list s

                                             where to_char(NVL(l.est_dtm, l.schd_dtm),'mm/dd/yyyy hh24') between

                                                          to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '7' HOUR,'mm/dd/yyyy hh24')

                                                          and

                                                          to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '7' HOUR,'mm/dd/yyyy hh24')

                                               and l.sector =s.sector and ls_stat is null

                                               and l.act_dtm is null and l.comp_tm is null),null) AS UNRLSFLT7,

                                   DECODE(2-2,0,(select count(*) from lt_leg l, sectr_list s

                                             where to_char(NVL(l.est_dtm, l.schd_dtm),'mm/dd/yyyy hh24') between

                                                          to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '8' HOUR,'mm/dd/yyyy hh24')

                                                          and

                                                          to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) + interval '8' HOUR,'mm/dd/yyyy hh24')

                                               and l.sector =s.sector and ls_stat is null

                                               and l.act_dtm is null and l.comp_tm is null),null) AS UNRLSFLT8

                            from desk_login x, sec_desk_map y, desk_sect z ,shft w

                            where ((w.start_tm<=to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP),'hh24mi')

                                      and w.end_tm>=to_char((SYS_EXTRACT_UTC(CURRENT_TIMESTAMP)),'hh24mi'))

                                  or(w.start_tm > w.end_tm

                                      AND (w.start_tm <= to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) ,'hh24mi')

                                            OR                                                            

                                           w.end_tm >= to_char(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) ,'hh24mi')

                                          )))

                            and x.active = 'Y' and y.desk = x.desk and y.sector=z.sect_id and z.desk_id=w.desk_id and z.shft_id=w.shft_id

                            group by x.desk, y.sector order by 1;

                             

                             

                             

                             

                            May be I am doing something stupid in there. Please help me correct that. Thank you.

                             

                            Regards,

                            • 11. Re: Help on replacing for loop with Decode
                              Frank Kulash

                              Hi,

                               

                              Sorry, I'd like to help, but until you post some useable sample data and results, there's not much I can do.

                               

                              If you want efficiency, then only access your tables once.  I don't see any reason why any one of your real tables needs to appear in more that 1 FROM clause.

                               

                              There's no reason to say

                              DECODE ( 2-2, 0, ...

                              because 2-2 will always be equal to 0.

                              • 12. Re: Help on replacing for loop with Decode
                                sanju2
                                DeskName/IdSectorsShiftReleasedUnreleased
                                InflightReleased22:0023:0000:0001:0002:0003:0004:0005:00
                                C05D/03I05, 13, I141500-22593496221001
                                C37T/05I621500-22590120010200
                                C40K/91I45, I471500-22590000000000

                                 

                                This is actual data may be taken as sample. and I am using DECODE(2-2,0, ... just get next eight hours data of unreleased. Next 8 hr is current time +1, +2 as it can be sen I pass 1, 2, 3, in interval HOUR. If I can get around to that, meaning If I can make query to pick interval 1, 2, 3, ....8 for each sector I will not need to use DECODE(2-2,0, ...   .


                                Data posted here and in previous post are actual.  In this post it is what  the existing report displays but the performance is issue. we want the same result with better performance. Data in previous is again actual run in SQL Developer copied and pasted.

                                 

                                Thank you.

                                 

                                regards,

                                 

                                Message was edited by: sanju2