1 2 Previous Next 15 Replies Latest reply: Feb 14, 2012 1:50 PM by Mettemusens2 RSS

    A question on collections (or maybe pure SQL can do it??)

    Mettemusens2
      A colleaque of mine has this problem - and the data is in a string format coming in from a txt file.
      So I might need to convert the "produkter" variable into a table of numbers - or extract with maybe a connect by .... into x rows instead of on (a de-listagg kind of thingy)

      He has a table with incoming products and an id for each list of products.
      He needs to know which productgroups are represented with all its products in the incoming row:

      I have tried to explain it here:
      drop table metteregning;
      create table metteregning  (id number, produkter varchar2(4000));
      
      insert into metteregning values (1, '1,2,3,4,5');
      insert into metteregning values (7, '1,2,3,4,5,7');
      insert into metteregning values (8, '3,4,5,8');
      insert into metteregning values (9,'5,6,7,8,9');
      
      select * from metteregning;
      
      ---
      
      drop table prod_vg;
      create table prod_vg (vg number, prodno number);
      
      insert into prod_vg values (1,1);
      insert into prod_vg values (1,2);
      insert into prod_vg values (1,3);
      insert into prod_vg values (1,4);
      insert into prod_vg values (1,5);
      
      insert into prod_vg values (2,1);
      insert into prod_vg values (2,2);
      insert into prod_vg values (2,3);
      insert into prod_vg values (2,4);
      insert into prod_vg values (2,5);
      insert into prod_vg values (2,7);
      
      insert into prod_vg values (3,3);
      insert into prod_vg values (3,4);
      insert into prod_vg values (3,5);
      insert into prod_vg values (3,8);
      
      insert into prod_vg values (10,1);
      insert into prod_vg values (10,2);
      insert into prod_vg values (10,10);
      
      insert into prod_vg values (80,8);
      insert into prod_vg values (50,5);
      
      commit;
      
      drop table vg_inverts;
      create table vg_inverts as (
      select  vg, listagg(prodno,',') within group (order by prodno) prod_numre, count(*) antal
      from prod_vg
      group by vg
      );
      
      select * from vg_inverts;
      
      --- expected output
      --metteregning.id  member  of VG       with counts
      
      -- 1                     1                           5
      -- 1                     50                           1
      
      -- 7                      2                           6
      -- 7                      1                           5
      -- 7                    50                           1
      
      --8                      3                            4
      -- 8                    50                           1
      -- 8                    80                           1
      
      --9                     50                           1
      --9                     80                           1
      Oracle is 11.2, Std. Edition.

      I'm think on manipulation collections - but I cant seem to crack the inital nut on this one.

      regards
      Mette

      Edited by: Mettemusens2 on 2012-01-13 10:00

      Edited by: Mettemusens2 on 2012-01-13 10:03

      Edited by: Mettemusens2 on 2012-01-13 10:04

      Edited by: Mettemusens2 on 2012-01-13 10:22
        • 1. Re: A question on collections (or maybe pure SQL can do it??)
          Frank Kulash
          Hi,

          Will metteregning.produkter always be in order, as it is in your sample data?
          If so, you can do something like this:
          SELECT       m.id
          ,       i.vg
          ,       i.antal
          FROM       metteregning     m
          JOIN       vg_inverts     i  ON  ',' || REPLACE ( m.produkter
                                                         , ','
                                                , ',,'
                                                )
                                 || ','  LIKE '%,' || REPLACE ( i.prod_numre
                                                                           , ','
                                                         , ',%,'
                                                         )
                                             || ',%'
          ORDER BY  m.id
          ,            i.vg
          ;
          You may want to display the operands to LIKE, just to see how this works. You'll notice that the left operand to LIKE is similar to
          ,1,,2,,3,,4,,5,             and the right operand is similar to
          %,1,%,2,%,3,%,4,%,5,%     or
          %,5,%

          You could make this more efficient by storing the right operand in vg_inverts, along with prod_numre.

          If you can't count on metteregning.produkter being in order, then one way to do it in pure SQL sould be to dis-aggregate produkter into one produkt per row, then join and count.

          Edited by: Frank Kulash on Jan 13, 2012 1:32 PM
          • 2. Re: A question on collections (or maybe pure SQL can do it??)
            Mettemusens2
            Hello

            No, they might not be in order, and there may be more occurences of the same number as well.

            It's is customer byings given products - and checking if given producs area actually may be considered one or more packages of products, with additional products.
            Hard to explain

            But this could also be the case:
            1,2,3,4,5,6,7,9,10,50,10,10,5,10,2
            8,9,1,3,2,5,4,1,50,50

            So I need to se if a row in metteregning contains all number of any VG - and list the VG's where all the VG prodno's are in the metteregnigng row .

            I hva read a little on collections, mulitset minus operands ... but dont know how to get started on this ...
            http://www.oracle.com/technetwork/issue-archive/o53plsql-083350.html


            Mette

            PS But i like the "solution" - good to get clever from :-)

            Edited by: Mettemusens2 on 2012-01-13 11:36
            • 3. Re: A question on collections (or maybe pure SQL can do it??)
              Frank Kulash
              Hi, Mette,
              Mettemusens2 wrote:
              Hello

              No, they might not be in order, and there may be more occurences of the same number as well.
              That's too bad!
              It's is customer byings given products - and checking if given producs area actually may be considered one or more packages of products, with additional products.
              Hard to explain

              But this could also be the case:
              1,2,3,4,5,6,7,9,10,50,10,10,5,10,2
              8,9,1,3,2,5,4,1,50,50

              So I need to se if a row in metteregning contains all number of any VG - and list the VG's where all the VG prodno's are in the metteregnigng row .
              Include examples of that in your sample data and results. The sample data is supposed to show what your real data is like, including all the problems and special cases you might find in your real data.
              I hva read a little on collections, mulitset minus operands ... but dont know how to get started on this ...
              http://www.oracle.com/technetwork/issue-archive/o53plsql-083350.html
              Yes, it's confusing. Here's one way to do it:
              WITH     got_prodno     AS
              (
                   SELECT     m.id
                   ,     TO_NUMBER ( REGEXP_SUBSTR ( m.produkter
                                               , '[^,]+'
                                               , 1
                                               , c.column_value
                                               )
                               )      AS prodno
                   FROM             metteregning  m
                   CROSS JOIN     TABLE ( CAST ( MULTISET ( SELECT  LEVEL
                                                                 FROM       dual
                                              CONNECT BY     LEVEL     <= 1 + REGEXP_COUNT ( m.produkter
                                                                                    , ','
                                                                         )
                                            ) 
                                         AS sys.odcinumberlist
                                          ) 
                                   ) c
              )
              SELECT       gp.id
              ,       vi.vg
              ,       vi.antal
              FROM       got_prodno   gp
              JOIN       prod_vg      pv  ON   gp.prodno     = pv.prodno
              JOIN       vg_inverts   vi  ON   pv.vg          = vi.vg
              GROUP BY  gp.id
              ,       vi.vg
              ,       vi.antal
              HAVING       MIN (vi.antal)  = COUNT (DISTINCT gp.prodno)
              ORDER BY  gp.id
              ,            vi.vg
              ;
              Since vi.vg is in the GROUP BY clause, and vi.antal is functionally dependent on vi.vg, we could say "MAX (vi.antal)" or even "AVG (vi.antal)" instead of "MIN (vi.antal)" in the HAVING clause; all three functions will return the same number. We could also include vi.antal in the GROUP BY clause.

              Always say which version of Oracle you're using. I assume you have Oracle 11.2, since you're calling LISTAGG. The query above requires Oracle 11.1 (just for REGEXP_COUNT).
              • 4. Re: A question on collections (or maybe pure SQL can do it??)
                Mettemusens2
                Hello Frank

                I did state in my first post that I'm on 11.2 std. edition :-))

                Thank you very much .. now I have something to work with ! Great !
                I think I need to grab a manual and read more on collections :-) And on multicast's ....

                Another Question ... since this may have to be done on SQL Server as well, di you perhaps know how to split the string into individual rows on sql server?
                I'm a total newbee on SQL Server ....

                Regards
                Mette
                • 5. Re: A question on collections (or maybe pure SQL can do it??)
                  Frank Kulash
                  Hi, Mette,
                  Mettemusens2 wrote:
                  Hello Frank

                  I did state in my first post that I'm on 11.2 std. edition :-))
                  Sorry, I looked for a version before, but didn't see it. (I do see it now.)
                  Thank you very much .. now I have something to work with ! Great !
                  I think I need to grab a manual and read more on collections :-) And on multicast's ....

                  Another Question ... since this may have to be done on SQL Server as well, di you perhaps know how to split the string into individual rows on sql server?
                  I'm a total newbee on SQL Server ....
                  Me too.

                  I wouldn't be surprised if the only parts of "TABLE (CAST ( MULTISET ( SELECT LEVEL FROM dual ..." that SQL Server understands are "SELECT" and "FROM".
                  Here's one way to get the same results without that stuff:
                  WITH     got_prodno (id, ubrig_produkter, prodno)     AS
                  (
                       SELECT     id
                       ,     SUBSTR ( produkter
                                   , 1 + INSTR ( produkter || ','
                                                     , ','
                                         )
                                   )                    AS ubrig_produkter
                       ,     TO_NUMBER ( SUBSTR ( produkter
                                               , 1
                                         , INSTR ( produkter || ','
                                                   , ','
                                                    ) - 1
                                         )
                                   )                    AS prodno
                       FROM     metteregning
                      UNION ALL
                       SELECT     id
                       ,     SUBSTR ( ubrig_produkter
                                   , 1 + INSTR ( ubrig_produkter || ','
                                                     , ','
                                         )
                                   )                    AS ubrig_produkter
                       ,     TO_NUMBER ( SUBSTR ( ubrig_produkter
                                               , 1
                                         , INSTR ( ubrig_produkter || ','
                                                   , ','
                                                    ) - 1
                                         )
                                   )                    AS prodno
                       FROM     got_prodno
                       WHERE     ubrig_produkter          IS NOT NULL
                  )
                  SELECT       gp.id
                  ,       vi.vg
                  ,       vi.antal
                  FROM       got_prodno   gp
                  JOIN       prod_vg      pv  ON   gp.prodno     = pv.prodno
                  JOIN       vg_inverts   vi  ON   pv.vg          = vi.vg
                  GROUP BY  gp.id
                  ,       vi.vg
                  ,       vi.antal
                  HAVING       MIN (vi.antal)  = COUNT (DISTINCT gp.prodno)
                  ORDER BY  gp.id
                  ,            vi.vg
                  ;
                  What version of SQL Server are you using? (I apologize if you already said, but I can't find it anywhere.) I believe recursive WITH clauses were new in 2005.
                  I avoided regular expressions, too, but if your version of SQL Server has them, I say use them if you want to. The query above uses more fundamental string manipulation functions. I'm sure any version of SQL Server has equivalents, but they might not be identical to the Oracle functions.

                  The main query is exactly what I posted earlier; only got_prodno has been changed.
                  • 6. Re: A question on collections (or maybe pure SQL can do it??)
                    Mettemusens2
                    Hi Frank

                    SS version: I am not sure (its one of my colleagues customers system) - but I think it would be a 2005 :-)

                    I think I will try to test it out myself tomorrow - I have an SQL Server 2008 on a Vbox ....

                    It's 15 to midnight here now - I think I will head for bed.

                    Again thank you very much :-)

                    Mette
                    • 7. Re: A question on collections (or maybe pure SQL can do it??)
                      Mettemusens2
                      Hi Frank :-)

                      We are now getting a new requirement from the end users (so the count distinct in the having) wont work.
                      We can now have a product_group with x accourences of a give product in it.

                      That is vg 1 now could be (1,2,3,4,5,1) and thus "metteregning values (1, '1,2,3,4,5')" would not fiit this product.

                      Can you help me out again ? (it is sort of an oppsite BOM thing). We are stuck with a lot of parts ... can we build a Ferrari, a Pegeout or just a bike ?

                      regards
                      Mette
                      • 8. Re: A question on collections (or maybe pure SQL can do it??)
                        Frank Kulash
                        Hi, Mette,
                        Mettemusens2 wrote:
                        Hi Frank :-)

                        We are now getting a new requirement from the end users (so the count distinct in the having) wont work.
                        We can now have a product_group with x accourences of a give product in it.

                        That is vg 1 now could be (1,2,3,4,5,1) and thus "metteregning values (1, '1,2,3,4,5')" would not fiit this product.

                        Can you help me out again ? (it is sort of an oppsite BOM thing). We are stuck with a lot of parts ... can we build a Ferrari, a Pegeout or just a bike ?
                        You've proably been thinking about this problem for a while, so many of the details are second nature to you. Remember that most, if not all, of the people who want to help you are not as familiar with the problem as you are, so you have to explain in very precisely. Always post CREATE TABLE and INSERT statements for a little sample data, and post the results you want from that data.
                        Do you still need a solution that will work in SQL Server as well as Oracle?

                        If I understand the problem correctly, we can use the analytic ROW_NUMBER fucntion to add additional series numbers, so that the two 1's in your example above would become (1, 1) and (1, 2). We can then procede as before, but matching both numbers. (I realize this may be hard to visualize. I can show you exactly what I mean after I get some sample data and results.)
                        • 9. Re: A question on collections (or maybe pure SQL can do it??)
                          Peter vd Zwan
                          Hi,

                          I think this is very simulair to a previous question.

                          Please see:

                          Re: sql query

                          Regards,

                          Peter
                          • 10. Re: A question on collections (or maybe pure SQL can do it??)
                            Peter vd Zwan
                            Hi,

                            See below:
                            create type part_list as table of number
                            ;
                            with products as
                            (select part_list(1,2,3,4,5,1) part_list from dual
                            union all select part_list(1,1,2,3,4,6) from dual
                            )
                            , parts as
                            (select part_list(1,2,3,4,5) part_list from dual
                            union all select part_list(1,1,2,3,4,6,7,8,9) from dual
                            )
                            
                            select
                              parts.part_list parts
                              ,case when products.part_list submultiset parts.part_list then 'Make next product'
                                else 'Do NOT make next product' end test
                              ,products.part_list product
                            
                            from
                              products
                              ,parts
                            ;
                            
                            PVDZWAN.PART_LIST(1,2,3,4,5)     Do NOT make next product     PVDZWAN.PART_LIST(1,2,3,4,5,1)
                            PVDZWAN.PART_LIST(1,1,2,3,4,6,7,8,9)     Do NOT make next product     PVDZWAN.PART_LIST(1,2,3,4,5,1)
                            PVDZWAN.PART_LIST(1,2,3,4,5)     Do NOT make next product     PVDZWAN.PART_LIST(1,1,2,3,4,6)
                            PVDZWAN.PART_LIST(1,1,2,3,4,6,7,8,9)     Make next product          PVDZWAN.PART_LIST(1,1,2,3,4,6)
                            I think working with list is easier if you make a type outof it.

                            Regards,

                            Peter
                            • 11. Re: A question on collections (or maybe pure SQL can do it??)
                              Mettemusens2
                              Hi Frank

                              I know that I should have posted the create tables etc once again ... I was just too frustrated when I left work today - so sorry.
                              And Yes, It should be able to run on both SQL S and Oracle (with minimal changes - rather ANSI, like the last solution we got working with your help).

                              regards
                              Mette
                              drop table metteregning;
                              create table metteregning  (id number, produkter varchar2(4000));
                               
                              insert into metteregning values (1, '1,2,3,1,4,5');
                              insert into metteregning values (10, '1,2,3,4,5');
                              insert into metteregning values (11, '1,2,3,1,4,1,1,1,1,1,1,5');
                              insert into metteregning values (7, '1,2,3,4,5,7');
                              insert into metteregning values (8, '3,4,5,8');
                              insert into metteregning values (9,'5,6,7,8,9,17,5,3,1');
                               
                              select * from metteregning;
                               
                              ---
                               
                              drop table prod_vg;
                              create table prod_vg (vg number, prodno number);
                               
                              insert into prod_vg values (1,1);
                              insert into prod_vg values (1,2);
                              insert into prod_vg values (1,3);
                              insert into prod_vg values (1,4);
                              insert into prod_vg values (1,5);
                              insert into prod_vg values (1,1);
                               
                              insert into prod_vg values (11,1);
                              insert into prod_vg values (11,2);
                              insert into prod_vg values (11,3);
                              insert into prod_vg values (11,4);
                              insert into prod_vg values (11,5);
                              
                              insert into prod_vg values (2,1);
                              insert into prod_vg values (2,2);
                              insert into prod_vg values (2,3);
                              insert into prod_vg values (2,4);
                              insert into prod_vg values (2,5);
                              insert into prod_vg values (2,7);
                               
                              insert into prod_vg values (3,3);
                              insert into prod_vg values (3,4);
                              insert into prod_vg values (3,5);
                              insert into prod_vg values (3,8);
                               
                              insert into prod_vg values (10,1);
                              insert into prod_vg values (10,2);
                              insert into prod_vg values (10,10);
                               
                              insert into prod_vg values (42,8);
                              insert into prod_vg values (42,10);
                              
                              insert into prod_vg values (13,20);
                              insert into prod_vg values (13,21);
                              
                              insert into prod_vg values (80,8);
                              insert into prod_vg values (50,5);
                              insert into prod_vg values (60,6);
                               
                              commit;
                               
                              drop table vg_inverts;
                              create table vg_inverts as (
                              select  vg, listagg(prodno,',') within group (order by prodno) prod_numre, count(*) antal, count(distinct prodno) antal_dis
                              from prod_vg
                              group by vg
                              );
                              
                              
                              --- expected output
                              --metteregning.id  member  of VG       with counts
                              --1                       1                            6 
                              --1                       11                          5
                              -- 1                     50                           1
                              
                               
                              -- 7                      2                           6
                              -- 7                      11                       5
                              -- 7                    50                           1
                               
                              --8                      3                            4
                              -- 8                    50                           1
                              -- 8                    80                           1
                               
                              --9                     50                           1
                              --9                     80                           1
                              
                              --10                    11                          5
                              -- 10                   50                          1
                              
                              --11                    1                            6
                              --11                  11                            5
                              --11                   50                           1
                              And the more ANSI looking SQl we got going withput the new requirement was this one:
                              WITH    got_prodno (id, tilbage_produkter, prodno, produkter)    AS
                              (
                                  SELECT    id    ,    SUBSTR ( produkter , 1 + INSTR ( produkter || ','  , ','   ) )   AS tilbage_produkter
                                  ,    TO_NUMBER ( SUBSTR ( produkter , 1 , INSTR ( produkter || ','  , ',' ) - 1 ) )    AS prodno
                                  ,  produkter
                                  FROM    metteregning
                                UNION ALL
                                  SELECT    id ,    SUBSTR ( tilbage_produkter, 1 + INSTR ( tilbage_produkter || ',' ,  ',' )) AS tilbage_produkter
                                  ,    TO_NUMBER ( SUBSTR ( tilbage_produkter , 1 , INSTR ( tilbage_produkter || ',' , ',' ) - 1 )) AS prodno
                                  , produkter
                                  FROM    got_prodno
                                  WHERE    tilbage_produkter        IS NOT NULL
                              )
                              SELECT 
                              --gp.id, gp.prodno, vi.vg, PV.PRODNO vg_prodno,  vi.antal
                              gp.id,  vi.vg , vi.antal, gp.produkter prod_numre_i_linien, vi.prod_numre prod_numre_i_vg
                              , COUNT (DISTINCT gp.prodno) antal_ramt
                              FROM      got_prodno   gp
                               JOIN      prod_vg      pv  ON   gp.prodno    = pv.prodno
                               JOIN      vg_inverts   vi  ON   pv.vg        = vi.vg
                              GROUP BY  gp.id ,   vi.vg,  vi.antal, gp.produkter, vi.prod_numre
                              HAVING      MIN (vi.antal)  = COUNT (DISTINCT gp.prodno)
                              ORDER BY  gp.id,  vi.antal desc
                              ---order by id, vg, prodno
                              ;
                              Edited by: Mettemusens2 on 2012-02-13 11:41

                              Edited by: Mettemusens2 on 2012-02-13 11:44

                              Edited by: Mettemusens2 on 2012-02-13 11:50

                              Edited by: Mettemusens2 on 2012-02-13 11:51
                              • 12. Re: A question on collections (or maybe pure SQL can do it??)
                                Peter vd Zwan
                                Hi,
                                First make a type:
                                create or replace
                                type part_list as table of number;
                                Then I make a function to change the comma seperated list in to a part list.
                                create or replace
                                FUNCTION MAKE_part_LIST (plist IN VARCHAR2) RETURN part_list AS 
                                pl part_list;
                                s_part varchar2(4000);
                                
                                BEGIN
                                s_part := ',' || plist || ',';
                                
                                select
                                cast( multiset
                                      (SELECT
                                        to_number(TRIM( SUBSTR ( s_part , INSTR (s_part, ',', 1, level ) + 1 , INSTR (s_part, ',', 1, level+1 ) - INSTR (s_part, ',', 1, level) -1 ) )) AS Part
                                      FROM
                                        dual
                                      CONNECT BY
                                        level <= LENGTH(s_part)-LENGTH(REPLACE(s_part,',',''))-1
                                      ) as part_list 
                                  )
                                into pl
                                from dual;
                                
                                RETURN pl;
                                
                                END MAKE_part_LIST;
                                And then with your tables as per exsample:
                                with prod as
                                (select 
                                  vg
                                  ,cast(multiset(select prodno from prod_vg where vg = a.vg) as part_list) pl
                                from
                                  prod_vg a
                                group by
                                  vg
                                )
                                ,met as
                                (select
                                  id
                                  ,make_part_list(produkter) pl
                                from
                                  metteregning
                                )
                                select
                                  met.id
                                  ,met.pl met
                                  ,prod.vg
                                  ,prod.pl prod
                                
                                from
                                  met
                                  ,prod
                                where
                                  prod.pl submultiset met.pl
                                
                                
                                Result:
                                ID     MET                         VG     PROD
                                1     PVDZWAN.PART_LIST(1,2,3,1,4,5)          1     PVDZWAN.PART_LIST(1,2,3,4,5,1)
                                1     PVDZWAN.PART_LIST(1,2,3,1,4,5)          11     PVDZWAN.PART_LIST(1,2,3,4,5)
                                1     PVDZWAN.PART_LIST(1,2,3,1,4,5)          50     PVDZWAN.PART_LIST(5)
                                10     PVDZWAN.PART_LIST(1,2,3,4,5)          11     PVDZWAN.PART_LIST(1,2,3,4,5)
                                10     PVDZWAN.PART_LIST(1,2,3,4,5)          50     PVDZWAN.PART_LIST(5)
                                11     PVDZWAN.PART_LIST(1,2,3,1,4,1,1,1,1,1,1,5)     1     PVDZWAN.PART_LIST(1,2,3,4,5,1)
                                11     PVDZWAN.PART_LIST(1,2,3,1,4,1,1,1,1,1,1,5)     11     PVDZWAN.PART_LIST(1,2,3,4,5)
                                11     PVDZWAN.PART_LIST(1,2,3,1,4,1,1,1,1,1,1,5)     50     PVDZWAN.PART_LIST(5)
                                7     PVDZWAN.PART_LIST(1,2,3,4,5,7)          2     PVDZWAN.PART_LIST(1,2,3,4,5,7)
                                7     PVDZWAN.PART_LIST(1,2,3,4,5,7)          11     PVDZWAN.PART_LIST(1,2,3,4,5)
                                7     PVDZWAN.PART_LIST(1,2,3,4,5,7)          50     PVDZWAN.PART_LIST(5)
                                8     PVDZWAN.PART_LIST(3,4,5,8)               3     PVDZWAN.PART_LIST(3,4,5,8)
                                8     PVDZWAN.PART_LIST(3,4,5,8)               50     PVDZWAN.PART_LIST(5)
                                8     PVDZWAN.PART_LIST(3,4,5,8)               80     PVDZWAN.PART_LIST(8)
                                9     PVDZWAN.PART_LIST(5,6,7,8,9,17,5,3,1)     50     PVDZWAN.PART_LIST(5)
                                9     PVDZWAN.PART_LIST(5,6,7,8,9,17,5,3,1)     60     PVDZWAN.PART_LIST(6)
                                9     PVDZWAN.PART_LIST(5,6,7,8,9,17,5,3,1)     80     PVDZWAN.PART_LIST(8)
                                Regards,

                                Peter
                                • 13. Re: A question on collections (or maybe pure SQL can do it??)
                                  Frank Kulash
                                  Hi, Mettte,

                                  Here's one way to do it in pure SQL:
                                  WITH    got_prodno (id, tilbage_produkter, prodno, produkter)    AS
                                  (
                                      SELECT    id    ,    SUBSTR ( produkter , 1 + INSTR ( produkter || ','  , ','   ) )   AS tilbage_produkter
                                      ,    TO_NUMBER ( SUBSTR ( produkter , 1 , INSTR ( produkter || ','  , ',' ) - 1 ) )    AS prodno
                                      ,  produkter
                                      FROM    metteregning
                                    UNION ALL
                                      SELECT    id ,    SUBSTR ( tilbage_produkter, 1 + INSTR ( tilbage_produkter || ',' ,  ',' )) AS tilbage_produkter
                                      ,    TO_NUMBER ( SUBSTR ( tilbage_produkter , 1 , INSTR ( tilbage_produkter || ',' , ',' ) - 1 )) AS prodno
                                      , produkter
                                      FROM    got_prodno
                                      WHERE    tilbage_produkter        IS NOT NULL
                                  )
                                  ,     got_prodno_plus_r_num     AS
                                  (
                                       SELECT     got_prodno.*
                                       ,     ROW_NUMBER () OVER ( PARTITION BY  id, prodno
                                                                 ORDER BY          NULL
                                                         ) AS r_num
                                       FROM    got_prodno
                                  )
                                  ,     prod_vg_plus_r_num     AS
                                  (
                                       SELECT     prod_vg.*
                                       ,     ROW_NUMBER () OVER ( PARTITION BY  vg, prodno
                                                                 ORDER BY          NULL
                                                         ) AS r_num
                                       FROM    prod_vg
                                  )
                                  SELECT       gp.id
                                  ,       vi.vg
                                  ,       vi.antal
                                  FROM      got_prodno_plus_r_num   gp
                                  JOIN      prod_vg_plus_r_num      pv  ON   gp.prodno    = pv.prodno
                                                               AND  gp.r_num     = pv.r_num
                                  JOIN      vg_inverts               vi  ON   pv.vg        = vi.vg
                                  GROUP BY  gp.id
                                  ,       vi.vg
                                  ,       vi.antal
                                  HAVING       MIN (vi.antal)  = COUNT (*)
                                  ORDER BY  gp.id
                                  ,            vi.vg
                                  ;
                                  The output is what you requested, plus one row:
                                  `ID  VG ANTAL
                                  --- --- -----
                                    1   1     6
                                    1  11     5
                                    1  50     1
                                    7   2     6
                                    7  11     5
                                    7  50     1
                                    8   3     4
                                    8  50     1
                                    8  80     1
                                    9  50     1
                                    9  60     1
                                    9  80     1
                                   10  11     5
                                   10  50     1
                                   11   1     6
                                   11  11     5
                                   11  50     1
                                  I assume that you meant to include that extra row:
                                  `ID  VG ANTAL
                                  --- --- -----
                                    9  60     1
                                  in your desired results.

                                  In the original problem, we were looking for matching products. That is, if prod_vg called for prod_vg called for prodno 1, we were checking to see if metteregning contained porodno 1.
                                  Now we're concerened about duplicates. That is, if prod_vg calls for 2 prodno 1s, we need to see if metteregning contains (at least) 2 prodno 1s. The query above adds a sequence number to each row in prod_vg, so that (for example) vg=1 has a 1st prodno 1 and a 2nd prodno 1. The numbers 1 and 2 are assigned by the analytic ROW_NUMBER function.
                                  (An aside: the syntax of ROW_NUMBER requires an ORDER BY clause. In this case, we don't care which row is aassinged r_num 1 and which is assigned r_num 2; in fact, until we assign r_num, we can't even tell those two rows apart. But ROW_NUMBER requires an ORDER BY clause anayway, so we have to give one, as silly as it may look.)
                                  We use ROW_NUMBER the same way to assign unique numbers to each row in got_prodno. In the main query, we use those computed r_nums in the join conditions.

                                  As far as I know, this whould be easy to adapt for SQL Server.
                                  • 14. Re: A question on collections (or maybe pure SQL can do it??)
                                    Mettemusens2
                                    Hi Peter

                                    And thanks for this elegant solution (I need to read more on colletions) ... I will though not use this since I have to keep a SQL Server version as well - so I need an almost pure ANSI version. I'm going with the solution I got with help from Frank K, since I could migrate it into SS without too many problems - and withput implementing more types, procedures or other objects into the (customers) database.

                                    But I will definately take a closer look ... since it looks elegant

                                    regards
                                    Mette
                                    1 2 Previous Next