On Aug 2, 6:13 pm, Michael Moore <michaeljmo...@gmail.com> wrote:
> load each set of values into a collection and then do:
>
> If collection_a = collection_b THEN ...
>
> no looping required.
>
> Here is an example that you can adapt: use the SELECT shown at the bottom in
> place of the FROM DUAL parts.
>
> CREATE OR REPLACE type integer_table is table of integer;
> DECLARE
>
> vendor_key_table integer_table;
> vendor_key_table2 integer_table;
> CURSOR tst
> IS
> SELECT purch_order, SUM (dollars),
> CAST (COLLECT (TO_NUMBER (vendor_key)) AS integer_table)
> FROM (SELECT 1 purch_order, 3 dollars, 435235 vendor_key
> FROM DUAL
> UNION ALL
> SELECT 1 purch_order, 8 dollars, 123452 vendor_key
> FROM DUAL
> UNION ALL
> SELECT 2 purch_order, 7 dollars, 433738 vendor_key
> FROM DUAL
> UNION ALL
> SELECT 2 purch_order, 4 dollars, 383738 vendor_key
> FROM DUAL
> UNION ALL
> SELECT 2 purch_order, 5 dollars, 387118 vendor_key
> FROM DUAL)
> GROUP BY purch_order;
>
> CURSOR tst2
> IS
> SELECT purch_order, SUM (dollars),
> CAST (COLLECT (TO_NUMBER (vendor_key)) AS integer_table)
> FROM (SELECT 1 purch_order, 3 dollars, 435235 vendor_key
> FROM DUAL
> UNION ALL
> SELECT 2 purch_order, 4 dollars, 383738 vendor_key
> FROM DUAL
> UNION ALL
> SELECT 2 purch_order, 7 dollars, 433738 vendor_key
> FROM DUAL
> UNION ALL
> SELECT 2 purch_order, 5 dollars, 387118 vendor_key
> FROM DUAL)
> GROUP BY purch_order;
> v_purch_order NUMBER;
> v_dollars NUMBER;
>
> mystr VARCHAR2 (4000);
>
> v_purch_order2 NUMBER;
> v_dollars2 NUMBER;
>
> mystr2 VARCHAR2 (4000);
> BEGIN
> OPEN tst;
> open tst2;
>
> LOOP
> mystr := NULL;
> mystr2 := NULL;
>
> FETCH tst
> INTO v_purch_order, v_dollars, vendor_key_table;
>
> FETCH tst2
> INTO v_purch_order2, v_dollars2, vendor_key_table2;
>
> IF tst%NOTFOUND
> THEN
> EXIT;
> END IF;
>
> if vendor_key_table = vendor_key_table2 then
> dbms_output.put_line('equal');
> else
> dbms_output.put_line(' not equal');
> end if;
>
> -- loop throught the collection and build a string so that
> -- we can display it and prove that it works
> FOR cur1 IN (SELECT COLUMN_VALUE vendor_key
> FROM TABLE (vendor_key_table))
> LOOP
> mystr := mystr || ',' || cur1.vendor_key;
> -- /* based on the value of the sum, you can do something with each
> detail*/
> -- if v_dollars > 12 then
> -- UPDATE VENDOR SET paid_status = 'P' where vendor_key =
> cur1.vendor_key;
> -- end if;
> END LOOP;
>
> DBMS_OUTPUT.put_line ( 'Purchace Order-> '
> || TO_CHAR (v_purch_order)
> || ' dollar total-> '
> || TO_CHAR (v_dollars)
> || ' vendorkey list-> '
> || SUBSTR (mystr, 2));
>
> -- loop throught the collection and build a string so that
> -- we can display it and prove that it works
> FOR cur2 IN (SELECT COLUMN_VALUE vendor_key
> FROM TABLE (vendor_key_table2))
> LOOP
> mystr2 := mystr2 || ',' || cur2.vendor_key;
> END LOOP;
>
> DBMS_OUTPUT.put_line ( 'Purchace Order-> '
> || TO_CHAR (v_purch_order2)
> || ' dollar total-> '
> || TO_CHAR (v_dollars2)
> || ' vendorkey list-> '
> || SUBSTR (mystr2, 2));
>
> END LOOP;
> CLOSE tst;
> CLOSE tst2;
> END;
> /* using a SUM function but saving the detail from each row */
>
> /* this technique could be used instead of procedural logic which
> manually sums the desired column and then tests if the grouping
> column value has changed. There is no need for 'save previous values'
> or 'if first_time' or 'if last_time' code
> */
>
> -- CREATE OR REPLACE type integer_table is table of integer;
> -- the above statement must be given in order to create the integer_table
>
> SELECT EXTRACTVALUE (VALUE (d), '/g') AS rslt
> FROM TABLE
> (XMLSEQUENCE
> (EXTRACT
> (XMLTYPE.createxml ( '<row><g>'
> || REPLACE
>
> ('393938,9383739,93836,99977',
> ',',
> '</g><g>'
> )
> || '</g></row>'
> ),
> '/row/g'
> )
> )
> ) d;
> Mike
Nicely done.
David Fitzjarrell
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
