7 Replies Latest reply on Apr 23, 2019 8:58 PM by jaramill

    Pull a list where 1 field is dependent on another

    user517674

      I have a scenario where I need to pull a list where 1 field is dependent on the other:

       

      My source table looks like below:

       

      Col1                                    Col2                                                              Col3                                   Col4

       

      1000                                       123                                                             2000                                   Current one

      1001                                       123                                                             2000                                   Not Current

      1002                                       123                                                             2000                                   Not Current

      4000                                       456                                                             1000                                   xyz

      5000                                       567                                                             1001                                   abc

      6000                                       789                                                             1000                                   def

      7000                                       986                                                             4000                                   uvw     

       


      CREATE TABLE test1
      (
         Col1   NUMBER,
         col2   NUMBER,
         col3   NUMBER,
         col4   VARCHAR2 (40)
      );

       

      insert into test1 values (1000, 123, 2000, 'Current one');
      insert into test1 values (1001, 123, 2000, 'Not Current');
      insert into test1 values (1002, 123, 2000, 'Not Current');
      insert into test1 values (4000, 456, 1000, 'xyz');
      insert into test1 values (5000, 567, 1001, 'abc');
      insert into test1 values (6000, 789, 1000, 'def');
      insert into test1 values (7000, 986, 4000, 'uvw');

       

      Target table should exclude the Not current records and the col2 value 567 should have the col3 field as 1000 instead of 1001, since the correct value of 123 is 1000 and not 1001.

       

      Output I am looking for:

       

      Col1                                    Col2                                                              Col3                                   Col4

       

      1000                                       123                                                             2000                                   Current one

      4000                                       456                                                             1000                                   xyz

      5000                                       567                                                             1000                                   abc

      6000                                       789                                                             1000                                   def

      7000                                               986                                                                        4000                                         uvw

       

      Database version: 12.1.0.2

        • 1. Re: Pull a list where 1 field is dependent on another
          jaramill

          Please read the FAQ (Frequently Asked Questions) link on --> Re: 2. How do I ask a question on the forums?

           

          Answer question 2 through 6 (need your tables/data in DDL and DML statements as well as the full 4 decimal digit version of your database)

           

          What have YOU tried first?  This is not a free-coding service.

          Is this a homework assignment?

          Or is this for work?

          • 2. Re: Pull a list where 1 field is dependent on another
            CarlosDLG

            It is good that you added the script to create the table, but you still need to put more effort in your explanation of the situation, because, as it is now, it is not clear at all.

            Target table should exclude ...

            What does "Target table" mean?  Do you want to create another table?  You didn't mention that before.

             

            ... the col2 value 567 should have the col3 field as 1000 instead of 1001, since the correct value of 123 is 1000 and not 1001.

            I suppose that by "correct value of 123" you are referring to the row where col2=123. If so, what do you mean by "value"?  Is it col1?

            Also, how does the row for col2=123 relate to the row with col2=567?

             

            If the problem or the logic behind the desired output is not clear enough, nobody will be able to help you.

            • 3. Re: Pull a list where 1 field is dependent on another
              Paulzip

              So Col3 is a reference to Col1 in a child -> parent manner, but should only come into play if 'Not Current'?  Also where's 2000, that the first 3 record refer to?

               

              What if you have a multiple links?  What should the results of this be...

               

              Col1                                    Col2                                                              Col3                                   Col4

               

              1000                                       123                                                             2000                                   Current one

              1001                                       123                                                             2000                                   Not Current

              1002                                       123                                                             2000                                   Not Current

              4000                                       456                                                             1000                                   Not Current

              4001                                       456                                                             1001                                   Current one

              5000                                       567                                                             1001                                   abc

              6000                                       789                                                             1000                                   def

              7000                                       986                                                             4000                                   uvw  

               

              Your rules are too ambiguous.

              • 4. Re: Pull a list where 1 field is dependent on another
                user517674

                "Target Table" --> Yes, I would like to create another table with the desired output.

                 

                Now as far as the output table goes, col2 is what I need a unique list created from. Since values of 1000 & 10001 from colA has the value of 123 in colB and since 1000 is the correct value based on a value of "Current One" in col4, I want that to be represented as the value in row 5 of the source table instead of 1001.

                • 5. Re: Pull a list where 1 field is dependent on another
                  Frank Kulash

                  Hi,

                   

                  Please explain in more detail what the desired results should be.  It's good to give specific examples, but make sure you state the general rules.

                   

                  What results would you want from this data:

                  col1  col2  col3  col4
                  ----  ----  ----  ----
                  9000  987   9999  Current one
                  9001  987   9999  Not Current
                  9001  986   9999  Current one
                  9002  986   9999  Not Current
                  9003  985   9002  foo

                  ?  Why?

                   

                  How about this:

                  col1  col2  col3  col4
                  ----  ----  ----  ----
                  9010  976   9999  Current one
                  9011  976   9999  Not Current
                  9012  975   9999  Current one
                  9011  975   9999  Not Current
                  9013  974   9011  foo

                  ? Why?

                   

                  When you post the sample data, include examples of any special cases like these that you need to handle.

                  • 6. Re: Pull a list where 1 field is dependent on another
                    user517674

                    I did not reflect 2000 since 2000 would look like:

                    2000          999               0000               Current one

                     

                    2000 is the top of the hierarchy

                     

                    Col2 is what holds it together. So for the table you are asking, the output would have another row for 1001 and would look like below. What I am looking for is a way to always ensure any value in have in Col1 is also Part of Col3 and vice versa. Output needs to be unique and should contain every Col2 value

                     

                    Col1                                    Col2                                                              Col3                                   Col4

                     

                    1000                                       123                                                             2000                                   Current one

                    1001                                       112                                                             5000                                   zvc    

                    4001                                       456                                                             1001                                   Current one

                    5000                                       567                                                             1000                                   abc

                    6000                                       789                                                             1000                                   def

                    7000                                       986                                                             4000                                   uvw       

                    • 7. Re: Pull a list where 1 field is dependent on another
                      jaramill

                      You completely are IGNORING what I (and others have asked/told you).  Post the ACTUAL table definition with insert statements, then show WHAT query you've written that you're having problems with and SPELL out the rules CLEARLY.

                      So far, we have had to ask you MANY questions to your ambiguous request and so far nothing.