Exists in cursor where condition is met

Question

I have created a cursor, that returns some data. I then wish to have an if statement in the returned data that checks if the field 'test_field' exists in the cursor where test_field2='A'. I am struggling to get my syntax correct on the if statement though, and can't find any pointers online.

CURSOR test_cur IS
      SELECT *
      FROM  test
      where name=pk_name;

BEGIN

FOR trec IN test_cur LOOP
    --The following line where my syntax is incorrect
    IF trec.test_field EXISTS IN test_cur WHERE trec.test_field2 = 'A' THEN
        {logic code to go here}
    END IF;
...

Example data:

name    | test_field    | test_field2
--------------------------------------
x       |  101          | ROL
x       |  101          | A
x       |  102          | ROL
x       |  103          | ROL

On the first loop of the cursor we have the following:

trec.name=x
trec.field=101
trec.field2=ROL 

What I want it to do though, is realise that there is another row (y) where where test_field=101 and test_field2='A' and therefor go into the if statement.

Answer 1

I'd rather suggest checking this condition within the query you use for he cursor, smth like this:

CURSOR test_cur IS
    SELECT TEST.*,
           CASE
              WHEN EXISTS
                      (SELECT 1
                         FROM TEST T
                        WHERE     T.TEST_FIELD = TEST.TEST_FIELD
                              AND T.TEST_FIELD2 = 'A')
              THEN
                 1
              ELSE
                 0
           END
              HAS_A
      FROM TEST
          WHERE NAME=PK_NAME;

BEGIN

FOR trec IN test_cur LOOP
    --The following line where my syntax is incorrect
    IF trec.has_a = 1 THEN
        {logic code to go here}
    END IF;
...

Answer 2

That syntax does not look logical to me, AFAIK you cannot do that

a solution would be

IF trec.test_field is not null and  trec.test_field2 = 'A'

Answer 3

I'm guessing here that when you say 'trec.test_fields exists where test_field = trec.test_field' you mean that you have a separate variable named test_field in your program somewhere. If that's the case, change the name of the variable to strTest_field to prevent naming collisions between the variable and the field in the table, and change your cursor to something like:

CURSOR test_cur IS
      SELECT *
      FROM  test t
      where t.name = pk_name AND
            t.test_field = strTest_field AND
            t.test_field2 = 'A'

Share and enjoy.

Answer 4

You can't peek ahead to later cursor records, and running the cursor multiple times would be expensive and possibly not helpful anyway.

You could order the data in the cursor such that your 'A' record always comes first, and then use tracking variables to see whether you have had an 'A' record for the current name/test_field combination:

DECLARE
    last_name test.name%TYPE := null;
    last_test_field test.test_field%TYPE := null;
    has_a BOOLEAN := false;
    CURSOR cur IS
        SELECT name, test_field, test_field2
        FROM  test
        WHERE name='x'
        ORDER BY name, test_field, test_field2;
        -- assumes 'A' actually comes first
BEGIN
    FOR rec IN cur LOOP
        -- reset tracking variables if major values change
        IF last_name IS NULL OR last_name != rec.name
            OR last_test_field != rec.test_field
        THEN
            last_name := rec.name;
            last_test_field := rec.test_field;
            has_a := (rec.test_field2 = 'A');
        END IF;
        IF has_a THEN
            DBMS_OUTPUT.PUT_LINE('name ' || rec.name || ' test_field '
                || rec.test_field || ' test_field2 ' || rec.test_field2
                || ' matched condition');
        END IF;
    END LOOP;
END;
/

name x test_field 101 test_field2 A matched condition
name x test_field 101 test_field2 ROL matched condition

PL/SQL procedure successfully completed.

A neater approach might be to add a flag to every record in the cursor, using analytic functions to to the 'peeking' ahead in the result set; again this assumes your 'A' value actually comes first alphabetically, if it doesn't then the function would need to be tweaked to get your actual value first:

SELECT name, test_field, test_field2,
    first_value(test_field2)
        over (partition by name, test_field
            order by test_field2) as has_a
            -- assumes 'A' actually comes first
FROM  test;

NAME       TEST_FIELD TEST_FIELD HAS_A
---------- ---------- ---------- ----------
x                 101 A          A
x                 101 ROL        A
x                 102 ROL        ROL
x                 103 ROL        ROL

... or even better, since this doesn't rely on the alphabetic order of your target value and also gives you a clearer result:

SELECT name, test_field, test_field2,
    MAX(CASE WHEN test_field2 = 'A' THEN 'true' END)
        OVER (PARTITION BY name, test_field) AS has_a
FROM test;

NAME       TEST_FIELD TEST_FIELD HAS_
---------- ---------- ---------- ----
x                 101 ROL        true
x                 101 A          true
x                 102 ROL
x                 103 ROL

Putting that in the cursor gives:

DECLARE
    CURSOR cur IS
        SELECT name, test_field, test_field2,
            MAX(CASE WHEN test_field2 = 'A' THEN 'true' END)
                OVER (PARTITION BY name, test_field) AS has_a
        FROM  test
        WHERE name='x'
        ORDER BY name, test_field, test_field2 DESC;
        -- forcing us to see ROL first, just to show it works
BEGIN
    FOR rec IN cur LOOP
        IF rec.has_a = 'true' THEN
            DBMS_OUTPUT.PUT_LINE('name ' || rec.name || ' test_field '
                || rec.test_field || ' test_field2 ' || rec.test_field2
                || ' matched condition');
        END IF;
    END LOOP;
END;
/

name x test_field 101 test_field2 ROL matched condition
name x test_field 101 test_field2 A matched condition

PL/SQL procedure successfully completed.

Note that this also doesn't force you to process the 'A' record first in the loop, which may or may not be important - probably not as you weren't ordering at all in the example.

Answer 5

If i understand your query correctly you are looking to peek ahead only. If so you must know the field that defines your order. I have given the template to do that below.

The specific statement of interest to solve your syntax question is:

FOR trec IN test_cur LOOP --The following line where my syntax is incorrect

SELECT COUNT(*) INTO l_count FROM  test
  where name= trec.test_field AND test_field2 = 'A' 
  and key > trec.key
   --this condition specifies if we need to look only further   
  ;

if l_count>0 then
    {logic code to go here}
END IF;

and below restated the full template:

CURSOR test_cur IS
  SELECT *
  FROM  test
  where name=pk_name

  ORDER by --<<whatever key that helps identify order>>

  ;

BEGIN

FOR trec IN test_cur LOOP
--The following line where my syntax is incorrect

SELECT COUNT(*) INTO l_count FROM  test
  where name= trec.test_field AND test_field2 = 'A' 
  and key > trec.key
   --this condition specifies if we need to look only further   
  ;

if l_count>0 then
    {logic code to go here}
END IF;

...