1 2 Previous Next 19 Replies Latest reply: Feb 7, 2013 10:08 PM by Royal_A RSS

    pl/sql procedure

    Royal_A
      hello der....

      plz help me in resolving a code..

      my table name is item_info, it has column: item_type,item_no,doc_no,matching_grp,ship_to_loc, qty. For every item_type (S) there will b a row in the table with item type(R). Now i hv to match each row in that table and find out the matched rows, on the basis of matching grp , qty, doc_no n other columns. Its sort of a shipment n receipt type of matching.

      plz help,, m stuck in this code...

      any help will b appreciated....
        • 1. Re: pl/sql procedure
          user346369
          hi der... itd b nice f u wud wright usn pln wrds.

          <pre>
          Begin
          For R in
          (Select item_type,item_no,doc_no,matching_grp,ship_to_loc, qty
          From item_info
          Where item_type='S')
          Loop
          For M in
          (Select item_type,item_no,doc_no,matching_grp,ship_to_loc, qty
          From item_info T
          Where T.item_type='R'
          and T.grp = R.grp
          and T.qty = R.qty
          and T.doc_no = R.doc_no
          -- and other columns
          )
          Loop
          Message('Found a match: Item='||M.item_no||' Doc_no='||M.doc_no
          ||' Grp='||M.matching_grp||' etc. etc.');
          End Loop;
          End Loop;
          End;</pre>
          • 2. Re: pl/sql procedure
            Christian Erlinger
            Steve Cosner wrote:
            hi der... itd b nice f u wud wright usn pln wrds.
            LOL :p

            It might be just me, but wouldn't a self-join be more efficient?
            Select  r.item_type,r.item_no,r.doc_no,r.matching_grp,r.ship_to_loc, r.qty
            From item_info R,  item_info T
            Where r.item_type='S'
            and T.item_type='R'
            and T.grp      = R.grp
            and T.qty      = R.qty
            and T.doc_no   = R.doc_no
            -- and other columns
            instead of the nested cursor-for loops, or am I missing something here?

            cheers
            • 3. Re: pl/sql procedure
              Royal_A
              Hii :)

              i tried ur solutions bt unfortunately not giving the desired output. Actually, i am working on the matching systm for item entries in the table. for eg:

              item type item no qty ship_to_loc documnt_no matching grp
              R 11 10 T1 D1 DMO
              R 12 5 T2 D2 SOF
              S 11 10 T1 D1 DMO

              Nw, whn it compare the rows it gvs the matched data and unmatched data with title matched or unmatched... can it b possible to fetch the data in such way...;)
              i hope it will help u guyz for resolving this one....

              Thx for ur efforts......... :)
              • 4. Re: pl/sql procedure
                Christian Erlinger
                You'd have to use an outerjoin instead an equijoin in my sample above.

                cheers
                • 5. Re: pl/sql procedure
                  Royal_A
                  Hii chris :)

                  in above sample of urs, u hv used self join...not equi join... and i hv to compare the rows on one table, so how can i use outr join in this buddy.... and in my data, condition is if for every S der is a entry of R with similar fields names, and all fields value matches then show them as Matched else unmatched....

                  Also,,can i use, rownum in this case, like, first retrieve the data from the table n den compare the first value of rownum with the data to all other value of table n likewise for 2 n 3 n so on values... wht u say...

                  cheers :)
                  • 6. Re: pl/sql procedure
                    Christian Erlinger
                    Oh my god, I am getting eyecancer...Please use proper english words instead IM speak as it is very difficult to read for us non-native-englishspeakers (and I guess for the nativespeakers as well) and after reading 3 words I decided it's too much trouble, so please rephrase your question in proper english.

                    cheers
                    • 7. Re: pl/sql procedure
                      Royal_A
                      hahaha... sorry for that Chris.. so here i go again.... :)



                      I was saying that in above sample of yours, you have used self join...not equi join... and i have to compare the rows on one table, so how can i use outer join in this buddy.... and in my data, condition is if for every 'S' (i.e, item_type) there is a another row of 'R'(i.e, item_type) with similar fields names, and all fields value matches to each other, then show data as Matched else unmatched....
                      Also,,can i use, rownum in this case, like, first retrieve the data from the table n den compare the first value of rownum with the data to all other value of table n likewise for 2 n 3 n so on values... what you say about this buddy..

                      cheers.............
                      • 8. Re: pl/sql procedure
                        Royal_A
                        hii Steve,, :)

                        Thanks for your effort,,

                        i worked on your code, its giving me the some output bt not the desired one. Actually, i want that when rows get matched ,it gives me output like ::

                        matched

                        matched row1
                        matched row 2
                        likewise

                        unmatched
                        unmatched row1
                        unmatched row2...

                        can u help me in this one.... :)
                        • 9. Re: pl/sql procedure
                          Christian Erlinger
                          Well, then maybe a correlated subquery:
                          with data as (
                            select 'R' item_type, 1 grp, 'a' val from dual
                            union all
                            select 'S' item_type, 1 grp, 'a' val from dual
                            union all
                            select 'R' item_type, 2 grp, 'b' val from dual
                            union all
                            select 'S' item_type, 2 grp, 'c' val from dual
                            union all
                            select 'S' item_type, 3 grp, 'd' val from dual
                          )
                          select item_type, grp, nvl((select 'MATCH' from data b where a.grp = b.grp and a.val = b.val and b.item_type = 'R'), 'NO MATCH')
                          from data a
                          where a.item_type = 'S';
                          You maybe need to use a view for that one depending on your forms version.

                          cheers
                          • 10. Re: pl/sql procedure
                            Royal_A
                            ahhh..... actually, i have to code it into a procedure and i have data in a table named item_info, so i cant use the view in this. its sort of a shipping and getting a receipt of the data shipped. n i have to match or unmatched the receipt and shipped data(rows). All these data of receipt and shipped are in rows of a table item_info and denoted by 'R' for receipt and 'S' for shipped. In my previous sample i have also used the example about how my table is look like..

                            item_type item_no qty ship_to_loc document_no matching grp
                            R 11 10 T1 D1 DMO
                            R 12 5 T2 D2 SOF
                            S 11 10 T1 D1 DMO

                            when first row compare with the 3rd one it gives then under matched section n 2 row as unmatched.

                            can it b done by using cursors ?

                            I hope it may help you further..................
                            Cheers :)
                            • 11. Re: pl/sql procedure
                              user346369
                              I dunno.... this might work:

                              <pre>SELECT * FROM
                              (
                              Select 'MATCHED' as match_type,
                              S.item_type,S.item_no,S.doc_no,S.matching_grp,
                              S.ship_to_loc, S.qty
                              From item_info S
                              Where item_type='S'
                              AND EXISTS
                              (select null from item_info R where R.item_type='R'
                              and S.grp = R.grp
                              and S.qty = R.qty
                              and S.doc_no = R.doc_no )
                              union all
                              Select 'UNMATCHED' as match_type,
                              S.item_type,S.item_no,S.doc_no,S.matching_grp,
                              S.ship_to_loc, S.qty
                              From item_info S
                              Where item_type='S'
                              AND NOT EXISTS
                              (select null from item_info R where R.item_type='R'
                              and S.grp = R.grp
                              and S.qty = R.qty
                              and S.doc_no = R.doc_no )
                              )
                              Order by match_type,item_no,etc;</pre>
                              • 12. Re: pl/sql procedure
                                user346369
                                Maybe another approach:

                                <pre>SELECT decode(V1.count_match,0,'NO MATCH','MATCH') as match_type,
                                V1.item_type, V1.item_no, V1.doc_no, V1.matching_grp,
                                V1.ship_to_loc, V1.qty
                                FROM
                                (
                                SELECT S.item_type,S.item_no,S.doc_no,S.matching_grp,
                                S.ship_to_loc, S.qty,
                                (select count(*) from item_info R
                                where R.item_type='R'
                                and R.grp = S.grp
                                and R.qty = S.qty
                                and R.doc_no = S.doc_no ) AS count_match
                                FROM item_info S
                                WHERE S.item_type='S'
                                ) V1
                                ORDER BY match_type,item_no;
                                </pre>
                                • 13. Re: pl/sql procedure
                                  Royal_A
                                  :)
                                  • 14. Report 6 i
                                    Royal_A
                                    Hello there... :)

                                    can anyone help me know that how to get the report 6i output into excel files.

                                    Thanks in advance :)
                                    1 2 Previous Next