1 2 3 Previous Next 33 Replies Latest reply: Oct 8, 2013 8:58 AM by Greg Spall RSS

    Need Help With a Query

    881668

      I have a procedure containing multiple SELECT statements using UNION ALL.

      How do I add the following SELECT statement using the UNION ALL?

       

      LOOP

        nCount:= e_nCount+ 1;

            SELECT

                UserID

              , datavalue

              , datavalue2

        FROM MyTable b

      WHERE (b.ID, b.StartDate) IN (

                             SELECT   a.ID, MAX (a.StartDate)

                             FROM     MyTablea

                                WHERE datavalue< datavalue2

                             GROUP BY a.ID)

                MINUS

                SELECT a.ID

                  FROM MyTable a

                 WHERE datavalue< datavalue2 ;

       

       

         exit when datavalue2=datavalue or nCount> 100;

       

       

         SELECT datavalue INTO datavalue2 FROM DUAL;

      END LOOP;

        • 1. Re: Need Help With a Query
          SomeoneElse

          Sorry, but this code makes no sense.

           

          I can't figure out what you want to do.

           

          Please rephrase or show a complete example.


          • 2. Re: Need Help With a Query
            Frank Kulash

            Hi,

             

            It's very unclear what you want to do.

             

            Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), so that the people who want to help you can re-create the problem and test their ideas.

            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 (for example, 11.2.0.2.0).

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

            • 3. Re: Need Help With a Query
              Greg Spall

              That depends.

               

              What are you trying to do? Can you explain what you need, in plain English ?

              Can you provide sample data to work from ?

              Can you explain what result you are expecting ?

               

              https://forums.oracle.com/message/9362002#9362002

              • 4. Re: Need Help With a Query
                881668

                I am neither looking to correct its syntax nor want to worry about its output. 

                I just want to add this block of sql statement to my existing procedure which is like the following:

                 

                INSERT INTO MyTable (field1, field2)

                SELECT field1, field2 from MyTable1

                UNION ALL

                SELECT field1, field2 from MyTable2

                UNION ALL

                SELECT field1, field2 from MyTable3;

                 

                The code I posted originally may not make sense to you but it has been part of a long query in production for years. The query in production is like the following and I am trying to make it better:

                 

                INSERT INTO MyTable (field1, field2)

                      SELECT field1, field2 from MyTable1;

                INSERT INTO MyTable (field1, field2)

                     SELECT field1, field2 from MyTable2;

                INSERT INTO MyTable (field1, field2)

                     SELECT field1, field2 from MyTable3;

                LOOP

                  nCount:= e_nCount+ 1;

                INSERT INTO MyTable (field1, field2)

                      SELECT

                          UserID

                        , datavalue

                        , datavalue2

                  FROM MyTable b

                WHERE (b.ID, b.StartDate) IN (

                                       SELECT   a.ID, MAX (a.StartDate)

                                       FROM     MyTablea

                                          WHERE datavalue< datavalue2

                                       GROUP BY a.ID)

                          MINUS

                          SELECT a.ID

                            FROM MyTable a

                           WHERE datavalue< datavalue2 ;

                   exit when datavalue2=datavalue or nCount> 100;

                     SELECT datavalue INTO datavalue2 FROM DUAL;

                END LOOP;

                • 5. Re: Need Help With a Query
                  Greg Spall

                  881668 wrote:

                  nor want to worry about its output. 

                   

                  Well, if you don't want to worry about the output, just do this:

                   

                  INSERT INTO MyTable (field1, field2)

                        SELECT field1, field2 from MyTable1

                  union all

                       SELECT field1, field2 from MyTable2

                  union all

                       SELECT field1, field2 from MyTable3

                  union all

                        SELECT

                         ,datavalue

                          , datavalue2

                    FROM MyTable b

                  WHERE (b.ID, b.StartDate) IN (

                                         SELECT   a.ID, MAX (a.StartDate)

                                         FROM     MyTablea

                                            WHERE datavalue< datavalue2

                                         GROUP BY a.ID)

                            MINUS

                            SELECT a.ID

                              FROM MyTable a

                             WHERE datavalue< datavalue2 ;

                   

                  *shrug*

                   

                  now, you did provide a bit more info .. thank-you.  We know now *what* we're trying to tack this beast onto. That helps.

                  881668 wrote:

                   

                  The code I posted originally may not make sense to you but it has been part of a long query in production for years.

                  I don't think anyone really is complaining about this, nor do we really care to make sense of your legacy code

                   

                  You ask for help, we need information to help.

                  If you don't provide some basic information, we cannot help.

                  It's a pretty simple situation, really.

                   

                  All 3 posts asked you for the same things:

                   

                  1) complete example.

                   

                  You still haven't provided as such.

                  How do you expect us to help you if you don't provide us the information we need to help you?

                   

                  Please read the links both Frank and I posted.

                  Please read them.

                  Please put together a small working example.

                  We are not going to do your job for you - we are going to *help* you do your job. We have jobs too, and we are very busy people.

                  • 6. Re: Need Help With a Query
                    Hoek

                    In addition to the other posts, in case you still don't get it:

                    The Tom Kyte Blog: How to ask questions

                    • 7. Re: Need Help With a Query
                      davidp 2

                      The short answer to your question is: You can't because the fragment you want to add is PL/SQL not pure SQL, and the UNION can only include pure SQL. The procedural logic of the loop, and exit when are PL/SQL statements, not SQL.

                      Incidentally the second last line

                      SELECT datavalue INTO datavalue2

                      should just be

                      datavalue2 := datavalue;


                      To combine this in a UNION, you need to turn the logic into a single SQL statement. It looks like a candidate for a CONNECT BY heirarchical query, but it's not too clear because

                      a) your code wouldn't compile - you are selecting 3 columns to insert into 2 columns

                      b) your code wouldn't work sensibly: Because the SELECT is in an insert, it doesn't modify datavalue, so datavalue2=datavalue on the second time around unless datavalue2 was null.


                      Personally I wouldn't call turning

                      INSERT INTO MyTable (field1, field2)

                            SELECT field1, field2 from MyTable1;

                      INSERT INTO MyTable (field1, field2)

                           SELECT field1, field2 from MyTable2;

                      INSERT INTO MyTable (field1, field2)

                           SELECT field1, field2 from MyTable3;

                      into

                      INSERT INTO MyTable (field1, field2)

                      SELECT field1, field2 from MyTable1

                      UNION ALL

                      SELECT field1, field2 from MyTable2

                      UNION ALL

                      SELECT field1, field2 from MyTable3;

                      a worth while improvement - it is no more clear, and the performance difference in PL/SQL will be insignificant.

                       

                      I hope this helps.

                      • 8. Re: Need Help With a Query
                        kendenny

                        881668 wrote:

                         

                        I am neither looking to correct its syntax nor want to worry about its output.

                        I just want to add this block of sql statement to my existing procedure which is like the following:

                         

                        INSERT INTO MyTable (field1, field2)

                        SELECT field1, field2 from MyTable1

                        UNION ALL

                        SELECT field1, field2 from MyTable2

                        UNION ALL

                        SELECT field1, field2 from MyTable3;

                         

                        The code I posted originally may not make sense to you but it has been part of a long query in production for years. The query in production is like the following and I am trying to make it better:

                         

                        INSERT INTO MyTable (field1, field2)

                              SELECT field1, field2 from MyTable1;

                        INSERT INTO MyTable (field1, field2)

                             SELECT field1, field2 from MyTable2;

                        INSERT INTO MyTable (field1, field2)

                             SELECT field1, field2 from MyTable3;

                        LOOP

                          nCount:= e_nCount+ 1;

                        INSERT INTO MyTable (field1, field2)

                              SELECT

                                  UserID

                                , datavalue

                                , datavalue2

                          FROM MyTable b

                        WHERE (b.ID, b.StartDate) IN (

                                               SELECT   a.ID, MAX (a.StartDate)

                                               FROM     MyTablea

                                                  WHERE datavalue< datavalue2

                                               GROUP BY a.ID)

                                  MINUS

                                  SELECT a.ID

                                    FROM MyTable a

                                   WHERE datavalue< datavalue2 ;

                           exit when datavalue2=datavalue or nCount> 100;

                             SELECT datavalue INTO datavalue2 FROM DUAL;

                        END LOOP;

                        Are you sure this code is in production now? It looks to me like it has a syntax error. You have a select with 3 columns minus a select with 1 column. That's not valid.

                        • 9. Re: Need Help With a Query
                          881668

                          Here is the actual working code from production; I have just removed additional INSERT statements from the end.

                          My objective is to create a procedure with only one INSERT INTO instead of multiples.

                           

                          declare  

                             nCount                       INTEGER := 0;

                             CreateDate              varchar2(6) := '201208';

                          begin

                           

                           

                          LOOP

                            nCount := nCount + 1;

                                 INSERT INTO MyTable

                                 (  startdate

                                  , enddate

                                  , GUID

                                  , rate

                                  , LUP_Date

                                  , Remarks

                                  , IsValid

                                  , UserID

                                  ,STATE)

                                 SELECT

                                   TRUNC(LAST_DAY(ADD_MONTHS(to_date(CreateDate,'YYYYMM'),-1))+ 1)

                                  ,TRUNC (LAST_DAY (to_date(CreateDate,'YYYYMM'))+ 1)

                                  , GUID

                                  , rate

                                  , sysdate   

                                  , Remarks

                                  , 'No'   

                                  , UserID

                                  , 'NY'  state

                            FROM MyTable b

                          WHERE (b.UserID, b.startdate) IN (

                                                 SELECT   a.UserID, MAX (a.startdate)

                                                 FROM     MyTable a

                                                    WHERE to_char(a.startdate,'YYYYMM') < CreateDate

                                                      AND a.IsValid = 'NO'

                                                 GROUP BY a.UserID)

                             AND b.UserID IN (

                                    SELECT b.UserID

                                      FROM MyTable b

                                     WHERE (b.UserID, b.startdate) IN (

                                                                      SELECT   a.UserID,

                                                                               MAX (a.startdate)

                                                                      FROM     MyTable a

                                                                         WHERE to_char(a.startdate,'YYYYMM') <  CreateDate

                                                                      GROUP BY a.UserID)

                                    MINUS

                                    SELECT a.UserID

                                      FROM MyTable a

                                     WHERE to_char(a.startdate,'YYYYMM') = CreateDate )

                             AND b.IsValid = 'NO';

                           

                           

                             exit when nCount > 15;

                           

                           

                             SELECT to_char(add_months(to_date(CreateDate,'YYYYMM'),+1),'YYYYMM') INTO CreateDate  FROM DUAL;

                          END LOOP;

                          commit;

                           

                           

                          INSERT INTO MyTable

                                 (  startdate

                                  , enddate

                                  , GUID

                                  , rate

                                  , LUP_Date

                                  , Remarks

                                  , IsValid

                                  , UserID

                                  ,STATE)

                            SELECT   startdate

                                  , enddate

                                  , GUID

                                  , rate

                                  , LUP_Date

                                  , Remarks

                                  , IsValid

                                  , UserID

                                  ,STATE

                          FROM MyTable_Temp

                          commit;

                          • 10. Re: Need Help With a Query
                            Warren Tolentino

                            by looking at your code it appears to be that the first inserts is doing a loop 15 times and by that it will insert 15 rows . the second insert which is outside of your loop inserts row one time only for all the rows in the from clause. so do you really want to include the insert statement that is outside of the loop to be included in the loop to do insert of the same rows 15 times? assuming that you have 100 rows on the myTable_temp and you included them on the loop. that will give you 1500 of the same rows.

                             

                            begin
                              loop
                                ...
                                ...
                                -- your code inserts 15 times
                              end loop

                              ...
                              -- your code inserts all rows in the myTable_temp to MyTable
                            end;

                             

                            if you add the insert code from your outside loop that will insert all the rows that you have on your myTable_temp to MyTable 15 times.

                             

                            declare  
                               nCount                       INTEGER := 0;
                               CreateDate              varchar2(6) := '201208';
                            begin
                              LOOP
                                nCount := nCount + 1;
                                INSERT INTO MyTable
                                   (  startdate
                                    , enddate
                                    , GUID
                                    , rate
                                    , LUP_Date
                                    , Remarks
                                    , IsValid
                                    , UserID
                                    ,STATE)
                                SELECT TRUNC(LAST_DAY(ADD_MONTHS(to_date(CreateDate,'YYYYMM'),-1))+ 1)
                                       ,TRUNC (LAST_DAY (to_date(CreateDate,'YYYYMM'))+ 1)
                                       , GUID
                                       , rate
                                       , sysdate   
                                       , Remarks
                                       , 'No'   
                                       , UserID
                                       , 'NY'  state
                                  FROM MyTable b
                                 WHERE (b.UserID, b.startdate) IN (SELECT a.UserID, MAX (a.startdate)
                                                                     FROM MyTable a
                                                                    WHERE to_char(a.startdate,'YYYYMM') < CreateDate
                                                                     AND a.IsValid = 'NO'
                                                                   GROUP BY a.UserID)
                                   AND b.UserID IN (SELECT b.UserID
                                                      FROM MyTable b
                                                     WHERE (b.UserID, b.startdate) IN (SELECT a.UserID,
                                                                                          MAX (a.startdate)
                                                                                         FROM MyTable a
                                                                                        WHERE to_char(a.startdate,'YYYYMM') <  CreateDate
                                                                                       GROUP BY a.UserID)
                                                    MINUS
                                                    SELECT a.UserID
                                                      FROM MyTable a
                                                     WHERE to_char(a.startdate,'YYYYMM') = CreateDate )
                                   AND b.IsValid = 'NO'
                                UNION ALL
                                SELECT startdate
                                       , enddate
                                       , GUID
                                       , rate
                                       , LUP_Date
                                       , Remarks
                                       , IsValid
                                       , UserID
                                       ,STATE
                                  FROM MyTable_Temp;
                               exit when nCount > 15;
                               SELECT to_char(add_months(to_date(CreateDate,'YYYYMM'),+1),'YYYYMM') INTO CreateDate  FROM DUAL;

                            END LOOP;

                            commit;
                            END;

                            • 11. Re: Need Help With a Query
                              881668

                              I want to leave alone the logic of LOOP and the code inside of LOOP.  I can create a single procedure having only one INSERT INTO using UNION ALL for the rest of the code but I am not able to figure out how to add the code with LOOP using UNION ALL. I have a really long UNION ALL e.g.:

                              INSERT INTO (A, B, C)

                                SELECT A, B, C from MyTable1

                              UNION ALL

                                   SELECT A, B, C from MyTable2

                              UNION ALL

                                   SELECT A, B, C from MyTable3

                              UNION ALL

                                   SELECT A, B, C from MyTable4

                              UNION ALL

                                   SELECT A, B, C from MyTable5

                              • 12. Re: Need Help With a Query
                                Mike Kutz

                                881668 wrote:

                                 

                                I am neither looking to correct its syntax nor want to worry about its output. 

                                The query in production is like the following and I am trying to make it better:

                                 

                                 

                                Pick one.  You can't have both.

                                In order to make it better, you MUST get rid of that unnecessary LOOP.

                                 

                                THINK IN SETS!!

                                • 13. Re: Need Help With a Query
                                  Warren Tolentino

                                  881668 wrote:

                                   

                                  I want to leave alone the logic of LOOP and the code inside of LOOP.  I can create a single procedure having only one INSERT INTO using UNION ALL for the rest of the code but I am not able to figure out how to add the code with LOOP using UNION ALL. I have a really long UNION ALL e.g.:

                                  INSERT INTO (A, B, C)

                                    SELECT A, B, C from MyTable1

                                  UNION ALL

                                       SELECT A, B, C from MyTable2

                                  UNION ALL

                                       SELECT A, B, C from MyTable3

                                  UNION ALL

                                       SELECT A, B, C from MyTable4

                                  UNION ALL

                                       SELECT A, B, C from MyTable5

                                   

                                  if you are not concerned about duplicate rows see my prior post for the samples.

                                   

                                  if you are concerned about duplicate rows just check for the existence of the rows that was already inserted so as not to re-insert the same row again.

                                  e.g.

                                   

                                    LOOP
                                      nCount := nCount + 1;
                                      INSERT INTO MyTable
                                         (startdate
                                          ,enddate
                                          ,GUID
                                          ,rate
                                          ,LUP_Date
                                          ,Remarks
                                          ,IsValid
                                          ,UserID
                                          ,STATE)
                                      SELECT TRUNC(LAST_DAY(ADD_MONTHS(to_date(CreateDate,'YYYYMM'),-1))+ 1)
                                             ,TRUNC (LAST_DAY (to_date(CreateDate,'YYYYMM'))+ 1)
                                             ,GUID
                                             ,rate
                                             ,sysdate   
                                             ,Remarks
                                             ,'No'   
                                             ,UserID
                                             ,'NY'  state
                                        FROM MyTable b
                                       WHERE (b.UserID, b.startdate) IN (SELECT a.UserID, MAX (a.startdate)
                                                                           FROM MyTable a
                                                                          WHERE to_char(a.startdate,'YYYYMM') < CreateDate
                                                                           AND a.IsValid = 'NO'
                                                                         GROUP BY a.UserID)
                                         AND b.UserID IN (SELECT b.UserID
                                                            FROM MyTable b
                                                           WHERE (b.UserID, b.startdate) IN (SELECT a.UserID,
                                                                                                MAX (a.startdate)
                                                                                               FROM MyTable a
                                                                                              WHERE to_char(a.startdate,'YYYYMM') <  CreateDate
                                                                                             GROUP BY a.UserID)
                                                          MINUS
                                                          SELECT a.UserID
                                                            FROM MyTable a
                                                           WHERE to_char(a.startdate,'YYYYMM') = CreateDate )
                                         AND b.IsValid = 'NO'
                                      UNION ALL
                                      SELECT startdate
                                             , enddate
                                             , GUID
                                             , rate
                                             , LUP_Date
                                             , Remarks
                                             , IsValid
                                             , UserID
                                             ,STATE
                                        FROM MyTable_Temp;
                                       where (startdate,
                                              enddate,
                                              GUID,
                                              rate,
                                              LUP_Date,
                                              Remarks,
                                              IsValid,
                                              UserID,
                                              STATE) not in (select startdate,
                                                                      enddate,
                                                                      GUID,
                                                                      rate,
                                                                      LUP_Date,
                                                                      Remarks,
                                                                      IsValid,
                                                                      UserID,
                                                                      STATE
                                                               from MyTable); 
                                     exit when nCount > 15;
                                     SELECT to_char(add_months(to_date(CreateDate,'YYYYMM'),+1),'YYYYMM') INTO CreateDate  FROM DUAL;

                                     END LOOP;

                                  • 14. Re: Need Help With a Query
                                    881668

                                    Warren,

                                    In your previous post with sample; you have UNION ALL inside of the LOOP and that is not what I am looking for. Please look at the example below and let me know if something like this can be done.

                                     

                                    INSERT INTO (A, B, C)

                                      SELECT A, B, C from MyTable1

                                    UNION ALL

                                         SELECT A, B, C from MyTable2

                                    UNION ALL

                                         SELECT A, B, C from MyTable3

                                    UNION ALL

                                         SELECT A, B, C from MyTable4

                                    UNION ALL

                                         SELECT A, B, C from MyTable5

                                    UNION ALL

                                          LOOP

                                              -- My query

                                         END LOOP

                                    UNION ALL

                                         SELECT A, B, C from MyTable6

                                    1 2 3 Previous Next