PL/SQL CURSOR COMPARISON/LOOKUP 1

[acct98]

Desired output:

Doe, John E 1234567891234567 Yes
Doe, Johnnie R 1234567891234567 No


Im trying to look up/loop to see it the credit card number in table_A can be found in table_B -- If
I can find the credit card nubmer then Yes otherwise No


DECLARE
CURSOR CUR_CREDIT_CARD_CK IS
select z.lastname||', '||z.firstname||' '||z.m, z.credit_card,
from TABLE_A Z, TABLE_B c
where z.ssn = c.ssn
having count(c.cardnumber) = 1
group by z.lastname||', '||z.firstname||' '||z.m, z.credit_card
order by z.lastname||', '||z.firstname||' '||z.m, z.credit_card;
CUR_CREDIT_CARD_CK%ROWTYPE;
BEGIN
OPEN CUR_CREDIT_CARD_CK;
LOOP
FETCH CUR_CREDIT_CARD
INTO MATCH;
EXIT WHEN CUR_CREDIT_CARD_CK%NOTFOUND;
IF Z.CREDIT_CARD = C.CARDNUMBER
THEN DBMS_OUTPUT.PUT_LINE
(CUR_CREDIT_CARD_CK||'YES');
ELSE DBMS_OUTPUT.PUT_LINE
(CUR_CREDIT_CARD_CK||'NO');
END IF;
END LOOP;
CLOSE CUR_CREDIT_CARD_CK;
END;
/


I'm not able to get this cursor to work. Any suggestions on how I can get this cursor to work?

 

[MPeccorini]

You need to add an outer join, add

z.ssn = c.ssn (+)
or
z.ssn (+) = c.ssn

depending on where the missing row could be.

Hope this helps

 

[carp]

Well, to begin with, the SQL in your cursor is wrong. The HAVING clause should follow the GROUP BY clause.

Secondly, if you want to find credit cards that are in TABLE_A as well as TABLE_B, you might try the INTERSECT operator:

SELECT cardnumber FROM table_a
INTERSECT
SELECT cardnumber FROM table_b;

Elbert, CO
1045 MDT

 

[SantaMufasa]

Acct98,

I suggest you try the following much-simplified code on your two tables (implementing the outer-join that MPeccorini suggest):

set serveroutput on
declare
begin
	for z in (select a.credit_card,a.lastname,a.firstname,
                  decode(b.credit_card,null,'No ','Yes')yn
			from table_a a, table_b b
			where a.credit_card = b.credit_card(+)
			order by a.credit_card) loop
		dbms_output.put_line(substr(z.lastname||', '||z.firstname||
                '                 ',1,25)
			||substr(z.credit_card||'     ',1,20)||z.YN);
	end loop;
end;
/

Garcia, Carmen           12345678901231     Yes
Ngao, LaDoris            12345678901232     Yes
Nagayama, Midori         12345678901233     Yes
Quick-To-See, Mark       12345678901234     Yes
Chang, Eddie             12345678901256     No
Patel, Radha             12345678901257     No
Dancs, Bela              12345678901258     No
Schwartz, Sylvie         12345678901259     Yes

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:47 (07Jun04) UTC (aka "GMT" and "Zulu"), 10:47 (07Jun04) Mountain Time)

 

[acct98]

I tried the your suggestion Mufasta however, I'm not getting the desired results. The Pl/SQL procedure executes successfully when I include I put in one specific SSN.
Furthermore when the card number is null I would expect it to come back with "No" but nothing comes back.
In addition, there are more that 300K records that I would like to evaluate.

I would like to evaluate the cardnumber (if any) from table A (Output it name and null card number if necessary) to find out it the cardnumber exists in table b.

Do you thing the FOR LOOP is the correct one to use in this case?

 

[carp]

acct98 -
A null value will not show up unless you substitute a non-null value for it. Something like (to use Mufasa's code):

set serveroutput on
declare
begin
    for z in (select 
                  NVL(a.credit_card,-1),
                  a.lastname,a.firstname,
                  decode(b.credit_card,null,'No ','Yes')yn
            from table_a a, table_b b
            where NVL(a.credit_card,-1) = b.credit_card(+)
            order by a.credit_card) loop
        dbms_output.put_line(substr(z.lastname||', '||z.firstname||
                '                 ',1,25)
            ||substr(z.credit_card||'     ',1,20)||z.YN);
    end loop;
end;
/

This is also the first time you've mentioned SSNs. What is it you're really trying to do?

As to your question regarding the FOR LOOP - if you wish to traverse a cursor that contains more than one row, than yes, you definitely want to use a LOOP.

 

[carp]

Disregard last. Now I see the SSNs.

 

[carp]

So it appears from your original post that you are interested in finding SSNs that are common to both table. This means you need to modify Mufasa's code accordingly:

set serveroutput on
declare
begin
    for z in (select 
                 a.credit_card,
                 a.lastname,a.firstname,
                 decode(b.ssn,null,'No ','Yes')yn
            from table_a a, table_b b
            where NVL(a.ssn,-1) = b.ssn(+)
            order by 2,3) loop
        dbms_output.put_line(substr(z.lastname||', '||z.firstname||
                '                 ',1,25)
            ||substr(z.credit_card||'     ',1,20)||z.YN);
    end loop;
end;
/

 

[SantaMufasa]

Acct98,

As usual, Carp lives up to the name his friends know him by, "Quick-Draw McGraw" ! Also, as usual, Carp is correct on all counts. He properly corrected my code to join on SSN instead of credit-card number. The FOR-LOOP with implicit CURSOR definition is certainly the most economical method to achieve what you want within PL/SQL. If you change the code as Carp mentions (joining on SSN), it should work fine for you.

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:33 (07Jun04) UTC (aka "GMT" and "Zulu"), 14:33 (07Jun04) Mountain Time)

 

[acct98]

Thanks Carp and SantaMufasa! It works but one more problem:


declare
*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 34
ORA-06512: at "SYS.DBMS_OUTPUT", line 118
ORA-06512: at "SYS.DBMS_OUTPUT", line 81
ORA-06512: at line 10


Any ideas?

 

[carp]

set serveroutput on size 1000000

Elbert, CO
0726 MDT