This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Feb 7, 2013 8:08 PM by Royal_A RSS

pl/sql procedure

Royal_A Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    You'd have to use an outerjoin instead an equijoin in my sample above.

    cheers
  • 5. Re: pl/sql procedure
    Royal_A Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    :)
  • 14. Report 6 i
    Royal_A Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points