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.
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'
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.
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.
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;
...
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;
...
