11 Replies Latest reply on Jul 30, 2020 1:53 PM by jaramill

    Query to get desire Output

    user11691341

      Hi,

       

      I am trying to get a desire output in single query.

       

      Source table

       

      CREATE TABLE "ATABLE"

      ( "ID" NUMBER(10,0),

      "TYPE" NUMBER(2,0),

      "CODE" VARCHAR2(20 BYTE)

      );

      Insert into ATABLE (ID,TYPE,CODE) values (1,1,'CODE01');

      Insert into ATABLE (ID,TYPE,CODE) values (2,1,'CODE01');

      Insert into ATABLE (ID,TYPE,CODE) values (2,11,'CODE011');

      Insert into ATABLE (ID,TYPE,CODE) values (3,11,'CODE011');

      Insert into ATABLE (ID,TYPE,CODE) values (4,5,'CODE05');

      Insert into ATABLE (ID,TYPE,CODE) values (4,4,'CODE04');

       

      basically for each id 1 record in target

      1) if id has 1 record in source bring that record

      2) if id is more than 1 record in source then it has below rule

      a) if one of the code_id is CODE011 then filter that record

      b) if one of the code_id is CODE04 then filter that record

       

      Remember these CODE011 or CODE04 is configurable and tomorrow  it can be changed to supress any second entry for CODE09

       

       

      Out Put Should be

       

      ID    TYPE     CODE

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

      1       1           CODE01

      2        1          CODE01

      3      11          CODE011

      4       5          CODE05

        • 1. Re: Query to get desire Output
          Frank Kulash

          HI,

          user11691341 wrote:

           

          Hi,

           

          I am trying to get a desire output in single query.

           

          Source is like

          id Type Desc Code_id

          1 1 C CODE01

          2 11 CO CODE011

          2 1 C CODE01

          3 11 CO CODE011

          4 5 C CODE05

          4 4 CO CODE04

          5 5 C CODE04

           

          Out Put Something like

           

          id Type Desc Code_id

          1 1 C CODE01

          2 1 C CODE01

          3 11 CO CODE11

          4 5 C CODE05

          5 4 C CODE04

           

          basically for each id 1 record in target

          1) if id has 1 record in source bring that record

          2) if id is more than 1 record in source then it has below rule

          a) if one of the code_id is CODE011 then filter that record

          b) if one of the code_id is CODE04 then filter that record

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

          Explain, using specific examples, how you get those results from that data.

          Always say what version of Oracle you're using (e.g. 12.2.0.2.0).

          See the forum FAQ: Re: 2. How do I ask a question on the forums?

           

          This sounds like a Top-N problem, where you want to select the top N rows (N=1 in this case) from the top of a sorted list.  You can do that with the aggregate LAST function, or the analytic RANK or ROW_NUMBER function.  CASE expressions may be helpful in sorting the data correctly (e.g.,  CASE WHEN code_id = 'CODE011' THEN 1 ...  )

           

          What results do you want in case of a tie (e.g., multiple rows have the same id and code_id)?  If that's possible in your table, include an eample when you post the sample dat and results.

          • 2. Re: Query to get desire Output
            jaramill

            user11691341 wrote:

             

            Hi

             

            I have given sample Source data

            and Also the Desire Output i need.

             

            Also its Oracle 12c.

             

            I tried using or the analytic RANK or ROW_NUMBER function. but Since it has a rule in case we have more than 1 Id record in Source like below

            basically for each id 1 record in target

            1) if id has 1 record in source bring that record

            2) if id is more than 1 record in source then it has below rule

            a) if one of the code_id is CODE011 then filter that record

            b) if one of the code_id is CODE04 then filter that record

             

             

            You have to give the sample data as INSERT statements (aka DML) and the object of the table as a create table statement (DDL).  We're not going to do ALL the work for you.

            You need to provide that and make sure there are NO errors so that we can copy/paste into our own environments.


            We have full-time jobs and this is a volunteer-forum, and not a PAID service.

            • 3. Re: Query to get desire Output
              Frank Kulash

              Hi,

               

              I see that you changed your message after you posted it.  Please don't do that; it makes the thread very hard for people to read and understand, and it also makes it more likely that your changes will not be noticed.  If you have to make corrections or additions to a message after posting it, then do so in  new reply.

              • 4. Re: Query to get desire Output
                user11691341

                Sure. I have Corrected the Source table as per your suggestion.

                 

                can you have a look now and suggest how we can use configuration table to get the output

                • 5. Re: Query to get desire Output
                  Frank Kulash

                  Hi,

                  user11691341 wrote:

                   

                  Sure. I have Corrected the Source table as per your suggestion.

                   

                  can you have a look now and suggest how we can use configuration table to get the output

                  Sure; post a couple of different sets of configurable rules, and the exact results you want from the given sample data for each set.

                  Once again, do this in a new reply.  Don't change any posted message.

                  • 6. Re: Query to get desire Output
                    chris227

                    Something like this:

                     

                     

                    create table code_order  (code varchar2(20) primary key, code_rank number unique)
                    /
                    insert into code_order values (1, 'CODE011')
                    /
                    insert into code_order values (2, 'CODE04')
                    /
                    select * from (
                    select t.id, t.type, t.code
                    ,      row_number() over (partition by t.id order by c.code_rank)
                    from atable t
                    ,    code_order c
                    where t.code = c.code(+)
                    ) where rn = 1
                    
                    • 7. Re: Query to get desire Output
                      user11691341

                      We want to configure it through some rule Table

                       

                      say if we have more than one Code for same id then Select as per rule table

                       

                      CODE001 and CODE003 select CODE003

                      CODE001 and CODE009 select CODE001

                      CODE003 and CODE005 select CODE005

                      CODE003 and CODE008 select CODE003

                      anycode  with CODE011  select anycode

                       

                      like this. Can you suggest some rule table example.

                       

                      Also we dont want to use Analytical Function, any other way other than analytical function

                      • 8. Re: Query to get desire Output
                        chris227

                        user11691341 wrote:

                         

                        We want to configure it through some rule Table

                         

                        say if we have more than one Code for same id then Select as per rule table

                         

                        CODE001 and CODE003 select CODE003

                        CODE001 and CODE009 select CODE001

                        CODE003 and CODE005 select CODE005

                        CODE003 and CODE008 select CODE003

                        anycode with CODE011 select anycode

                         

                        like this. Can you suggest some rule table example.

                         

                        Sorry, that's not clear to me.

                        Please give some concret examples especially for the expcted results.

                         

                        user11691341 wrote:

                         

                        Also we dont want to use Analytical Function, any other way other than analytical function

                        Yes, there are some more Options.

                         

                        To give you the desireed one you should give some more Information on your restrictions, requirements, db Version etc.

                         

                        Why dont you like analytical functions?

                        • 9. Re: Query to get desire Output
                          user11691341

                          Eample

                           

                          Source is like

                          ID    CODE

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

                          1       CODE01

                          1       CODE03

                          2      CODE01

                          2      CODE09

                          3      CODE011

                          3      CODE02

                          4      CODE05

                          4      CODE03

                          5      CODE03

                          5      CODE08

                          6      CODE11

                          7      CODE01

                          8      CODE03

                           

                          So if you see here for some ID we have multiple Code.

                          if it has multiple code we have some rule which one to Include and which one to exclude

                          like

                          between CODE01 and CODE03 select CODE003 ( Example id =1 records which has 2 code but we need to show the record with code as CODE03)

                          Also today its CODE03  tomorrow we can configure to take CODE01 record

                          between CODE01 and CODE09 select CODE01

                          between CODE03 and CODE05 select CODE05

                          between CODE03 and CODE08 select CODE03

                          between any code with CODE11 select Any Code (example id =3)

                           

                          Also the one with has just one record for Id should come as it is (example id = 6,7,8)

                           

                          We want to keep the Rule like CODE01 and CODE09 select CODE01 in a rule table and use that to determine the output record

                           

                          So the OutPut will be like below

                           

                          ID     CODE

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

                          1      CODE03

                          2      CODE01

                          3      CODE02

                          4      CODE05

                          5      CODE03

                          6      CODE11

                          7      CODE01

                          8      CODE03

                           

                          Hope its helps

                          • 10. Re: Query to get desire Output
                            chris227
                            create table code_order  (code varchar2(20) primary key, code_rank number unique) 
                            / 
                            insert into code_order values ('CODE03',1) 
                            / 
                            insert into code_order values ('CODE01',2) 
                            / 
                            select id, type, code from ( 
                            select
                              t.id, t.type, t.code, c.code_rank
                            , row_number() over (partition by t.id order by null)  rn -- whatever your default rank should be
                            , count(*) over (partition by t.id) cnt
                            , count(c.code_rank) over (partition by t.id) cnt_rnk
                            from atable t 
                            ,    code_order c 
                            where t.code = c.code(+) 
                            ) where
                              case when cnt = cnt_rnk then code_rank
                                    else rn
                              end = 1
                            
                            • 11. Re: Query to get desire Output
                              jaramill

                              YOU must provided the sample data as INSERT statements or a "WITH clause SELECT statement with the data in it".

                              It's part of the requests on the link --> Re: 2. How do I ask a question on the forums?

                               

                              6) Tables/Indexes
                              Provide us with table structures and indexes where necessary. Use the DESC command in SQL*Plus or your CREATE statements if you have them.

                               

                              7) Sample Data
                              Provide us with sample data to help recreate the issue

                               

                              You can do this using subquery factoring (a "WITH" statement) e.g.

                               

                              with t as (select 1 as id, to_date('01/01/2009','DD/MM/YYYY') as date_created, 50 as val from dual union all

                                         select 2, to_date('02/02/2009','DD/MM/YYYY'), 25 from dual union all

                                         select 3, to_date('03/02/2009','DD/MM/YYYY'), 30 from dual)

                                  ,x as (select 1 as id, 'Fred' as name from dual union all

                                         select 2, 'Bob' from dual union all

                                         select 3, 'Tim' from dual)

                               

                              or by providing create table and insert statements e.g.

                               

                              create table t (id number,

                                              date_created date,

                                              val number)

                              /

                              create table x (id number,

                                              name varchar2(20))

                              /

                               

                              insert into t values (1, to_date('01/01/2009','DD/MM/YYYY'), 50);

                              insert into t values (2, to_date('02/02/2009','DD/MM/YYYY'), 25);

                              insert into t values (3, to_date('03/02/2009','DD/MM/YYYY'), 30);

                              insert into x values (1, 'Fred');

                              insert into x values (2, 'Bob');

                              insert into x values (3, 'Tim');

                               

                              8) Expected Output
                              Provide us with an example of the expected output. You can show this in a table, as with the sample data, or just as formatted output.