11 Replies Latest reply on Apr 25, 2012 7:05 AM by Etbin

    How to Avoid repeating of query(rewrite query)

    743620
      Have the follwoing select statement in cursor. How can i avoid repeating the same select statement but with different columns n the where clause conditions.

      for cur1 in (SELECT d.ID,
      name
      type,
      code
      FROM tableA ,c
      tableB d
      WHERE c.id = d.id
      AND d.vin_id IN
      -- select statement
      (SELECT b.id
      FROM tableA a,
      TableB b
      WHERE a.id = b.id
      GROUP BY b.id,
      b.code
      HAVING COUNT(1) = 1
      )
      AND d.code IN
      --same select stmt used before with diff col
      (SELECT b.id
      FROM tableA a,
      TableB b
      WHERE a.id = b.id
      GROUP BY b.id,
      b.code
      HAVING COUNT(1) = 1
      ))
        • 1. Re: How to Avoid repeating of query(rewrite query)
          €$ħ₪
          for cur1 in (SELECT d.ID,
          name
          type,
          code
          FROM tableA ,c
          tableB d
          WHERE c.id = d.id
          AND (d.vin_id,d.code) IN
          -- select statement
          (SELECT b.id,b.id
          FROM tableA a,
          TableB b
          WHERE a.id = b.id
          GROUP BY b.id,
          b.code
          HAVING COUNT(1) = 1
          )


          like this ?
          • 2. Re: How to Avoid repeating of query(rewrite query)
            743620
            Hi thanks for your reply .
            but if i put both columns in the where clause they take optional condition like if 'id or cod'e exists the condition
            becomes true. I have to meet both the conditions' id and code'.
            • 3. Re: How to Avoid repeating of query(rewrite query)
              Frank Kulash
              Hi,

              Here's one way:
              WITH     sub_query     AS
              (
                   SELECT DISTINCT  b.id
                   FROM              tableA     a
                   ,           TableB b
                   WHERE           a.id     = b.id
                   GROUP BY      b.id
                   ,                b.code
                   HAVING           COUNT (1)     = 1
              )
              SELECT  d.id
              ,     name     AS type
              ,     code 
              FROM      tableA          c
              ,     tableB           d
              ,     sub_query     sv
              ,     sub_query     sc
              WHERE      c.id          = d.id
              AND      d.vin_id      = sv.id
              AND     d.code          = sc.id
              ;
              If you'd care to post CREATE TABLE and INSERT statements for a little sample data, and the results you want from that data, then I could test it.
              • 4. Re: How to Avoid repeating of query(rewrite query)
                743620
                thanks for reply.

                Here are the create and insert stmts and result set

                create tableA (id number,tname varchar2(50), type varchar2(10));

                create tableB (id number, tcode varchar2(10), code varchar2(10))



                insert into tableA(id,tname,type) values (1,'tt','ts');

                insert into tableA(id,tname,type) values (2,'ss','st');
                insert into tableA(id,tname,type) values (3,'uu,'vv');

                insert into tableB(id,tcode,code) values(1,'AB','XY')
                insert into tableB(id,tcode,code) values(1,'BC','YZ')
                insert into tableB(id,tcode,code) values(1,'BC','ZZ')
                insert into tableB(id,tcode,code) values(1,'DE','YY')
                insert into tableB(id,tcode,code) values(2,'TL','MD')
                insert into tableB(id,tcode,code) values(2,'MS','PG')
                insert into tableB(id,tcode,code) values(3,'TL','TL')
                insert into tableB(id,tcode,code) values(3,'TL','TT')

                Resultset:

                is name type tcode code
                1 TT TS AB XY
                1 TT TS DE ZZ
                2 SS ST TL MD
                2 SS ST MS PG

                Thanks,
                • 5. Re: How to Avoid repeating of query(rewrite query)
                  Paulie
                  >

                  Here are the create and insert stmts and result set
                  Which don't work - see bottom of this post for correct DDL and DML.
                  create tableA (id number,tname varchar2(50), type varchar2(10));
                  You should not use the keyword TYPE as a fieldname - I used ttype.

                  Resultset:
                  is name type tcode code
                  1 TT TS AB XY
                  1 TT TS DE ZZ
                  2 SS ST TL MD
                  2 SS ST MS PG
                  I assumed that you wanted id = 3 as well - if not just select 1,2.

                  select ta.id, upper(ta.tname) AS "Type", upper(ta.ttype) AS "A_Code", tb.tcode, tb.code AS B_Code
                  from tableA ta, tableb tb
                  where ta.id = tb.id(+)
                  -- add further conditions here as needed.


                  A variant of this SQL should be able to do what you want.


                  Paul...


                  ========= correct DDL and DML =====================

                  create table tableA (id number, tname varchar2(50), ttype varchar2(10));
                  create table tableB (id number, tcode varchar2(10), code varchar2(10))


                  insert into tableA(id,tname,ttype) values (1,'tt','ts');

                  insert into tableA(id,tname,ttype) values (2,'ss','st');
                  insert into tableA(id,tname,ttype) values (3,'uu','vv');

                  insert into tableB(id,tcode,code) values(1,'AB','XY');
                  insert into tableB(id,tcode,code) values(1,'BC','YZ');
                  insert into tableB(id,tcode,code) values(1,'BC','ZZ');
                  insert into tableB(id,tcode,code) values(1,'DE','YY');
                  insert into tableB(id,tcode,code) values(2,'TL','MD');
                  insert into tableB(id,tcode,code) values(2,'MS','PG');
                  insert into tableB(id,tcode,code) values(3,'TL','TL');
                  insert into tableB(id,tcode,code) values(3,'TL','TT');

                  commit;
                  • 6. Re: How to Avoid repeating of query(rewrite query)
                    Paulie
                    >
                    Resultset:
                    is name type tcode code
                    1 TT TS AB XY
                    1 TT TS DE ZZ
                    2 SS ST TL MD
                    2 SS ST MS PG
                    Can you explain exactly how you derive your resultset from your original data -
                    I modified Frank's code to work - but it doesn't give the correct answer.
                    Here it is

                    WITH sub_query AS
                    (
                    SELECT DISTINCT b.id
                    FROM tableA a
                    , TableB b
                    WHERE a.id = b.id
                    GROUP BY b.id
                    , b.code
                    HAVING COUNT(b.id) = 1
                    )
                    SELECT d.id
                    , Upper(tname) AS "Type"
                    , Upper(code) AS "Code"
                    FROM tableA c
                    , tableB d
                    , sub_query sv
                    , sub_query sc
                    WHERE c.id = d.id
                    AND d.id = sv.id
                    AND d.id = sc.id
                    ;

                    But it doesn't give the correct answer either and I've tried experimenting with
                    it and so far, there's no way to derive a resultset that I can't understand.


                    Paul....
                    • 7. Re: How to Avoid repeating of query(rewrite query)
                      Etbin
                      Maybe
                      select number,tname,ttype,tcode,code
                        from (select a.number,a.tname,a.ttype,b.tcode,b.code,
                                     row_number() over (partition by a.number order by b.tcode) min_row,
                                     row_number() over (partition by a.number order by b.tcode desc) max_row
                                from tablea a,
                                     tableb b
                               where a.number = b.number
                             )
                       where min_row = 1
                         and max_row = 1
                         and number < 3 
                      Regards

                      Etbin
                      • 8. Re: How to Avoid repeating of query(rewrite query)
                        Paulie
                        >

                        Maybe
                        select number,
                        Maybe not - errors start here - using a fieldname of "number" confuses Oracle no-end ;)

                        The corrected query returns the empty set - use my DDL and DML - works on Oracle 10 XE.


                        Paul...

                        Etbin
                        • 9. Re: How to Avoid repeating of query(rewrite query)
                          O.Developer
                          no, you enclude both column in condition and in values set also mention id column towice.

                          It is working like and And

                          TRy
                          • 10. Re: How to Avoid repeating of query(rewrite query)
                            Paulie
                            >
                            Maybe
                            Finally! I should have known better than to doubt you Etbin ;)

                            This was my first queryt which worked (with apologies to Frank!)

                            WITH sub_query AS
                            (
                            SELECT a.id,a.tname,a.ttype,b.tcode,b.code,
                            row_number() over (partition by a.id order by b.tcode) min_row,
                            row_number() over (partition by a.id order by b.tcode desc) max_row
                            FROM tablea a,
                            tableb b
                            WHERE a.id = b.id
                            )
                            SELECT qs.id, qs.tname, qs.ttype, qs.tcode, qs.code
                            FROM sub_query qs
                            WHERE ((qs.max_row = 1) OR (qs.min_row = 1 ))
                            AND qs.id < 3
                            ORDER BY qs.id, qs.tcode

                            And then I looked at your syntax again

                            select id,upper(tname),upper(ttype),tcode,code FROM
                            (select a.id,a.tname,a.ttype,b.tcode,b.code,
                            row_number() over (partition by a.id order by b.tcode) min_row,
                            row_number() over (partition by a.id order by b.tcode desc) max_row
                            from tablea a,
                            tableb b
                            where a.id = b.id
                            )
                            where ((min_row = 1) OR (max_row = 1))
                            and id < 3
                            order by id, tcode


                            So, success - and relief,


                            Paul...

                            Etbin
                            • 11. Re: How to Avoid repeating of query(rewrite query)
                              Etbin
                              A nice way :( to start another day (after forty years of programming): *... the least AND the gratest tcode values must be included ...*
                              Might be time to retire ;)

                              Regards

                              Etbin

                              Edited by: Etbin on 25.4.2012 9:04
                              or get Oracle installed on my home computer