1 Reply Latest reply: Sep 27, 2013 1:42 PM by Frank Kulash RSS

    Inserting records where value(s) depend on already existing values in target table

    1002123

      Inserting records where value(s) depend on already existing values in target table

        • 1. Re: Inserting records where value(s) depend on already existing values in target table
          Frank Kulash

          Hi,

           

          This sounds like a job for the analytic ROW_NUMBER function:

           

          ROW_NUMBER () OVER ( PARTITION BY  style_id

                               ,             header_id

                               ORDER BY      option_cd   -- or whatever

                              )

          will return unique integers (1, 2, 3, ...) for each combination of style_id and header_id.

          You can put any expression(s) you want in the ORDER BY clause, but you must have an ORDER BY clause.    You said you wanted them in "the order of occurrence in the second file".  If you're captuing that order somewhere, then you can use that value in the analytic ORDER BY clause.

           

          I'm not really sure what you want, but you can probably do it in a single SQL statement.

           

           

          I hope this answers your question.
          If not, post  a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.  Include any columns needed to determine the order.
          If you're asking about a DML statement, such as INSERT, the sample data will be the contents of the table(s) before the DML, and the results will be state of the changed table(s) when everything is finished.
          Explain, using specific examples, how you get those results from that data.
          Always say which version of Oracle you're using (e.g., 11.2.0.2.0).

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