Cursor names must be unique within the source.
... but you can define to independent cursor with different names and then
add an if statement around.
If the same fields are returned, but the difference between the 2 cursors is
an additional join, 2 independent cursors are necessary.
if var1 <> *blank;
exec sql
declare myCursor1 cursor for
select FIELD2
from FILE1
where FIELD1 = :var1;
else;
exec sql
declare myCursor2 cursor for
select FIELD2
from FILE1
inner join FILE2
on FILE1.FIELD3 = FILE2.FIELD4
where FIELD5 = var2;
endif;
....
If var1 <> *Blanks;
Exec SQL Open myCursor1;
Else;
Exec SQL Open myCursor2;
EndIf;
DOU 1=0;
If Var1 <> *Blanks;
Exec SQL Fetch next from MyCursor1 into :YourDS;
Else;
Exec SQL Fetch next from MyCursor2 into :YourDS:
Endif;
.....
If the difference is within the WHERE or ORDER BY clause, you might be able
to use a single cursor with case clauses.
Exec SQL Declare yourCursor Cursor For
Select Fld1, Fld2, ... FldN
From YourFile ....
Where FldX = Case When :Var1 > '' then :Var1 Else FldX End
And FldY = Case When :Var2 <> 0 Then :Var2 Else FldY End
....
Order By Case When :Sort = 1 Then Fld1 End, Case When :Sort = 1 Then
Fld3 End,
Case When :Sort = 2 Then Fld2 End, Case When :Sort =
2 Then Fld1 End Desc,
...
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Ursprüngliche Nachricht-----
Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von David
Gibbs
Gesendet: Wednesday, 30.11 2016 17:52
An: rpg400-l@xxxxxxxxxxxx
Betreff: SQL cursor defined two different ways?
Folks:
I'm wondering if this is possible ... so far the compiler is saying no.
I have a two different queries that return the same type of data. I would
like to be able declare a cursor for the two different queries, conditional
on a parameter, and then just spin through the results.
Here's a mock-up of what I would like to do...
if var1 <> *blank;
exec sql
declare myCursor cursor for
select FIELD2
from FILE1
where FIELD1 = :var1;
else;
exec sql
declare myCursor cursor for
select FIELD2
from FILE1
inner join FILE2
on FILE1.FIELD3 = FILE2.FIELD4
where FIELD5 = var2;
endif;
When I compile, I get a message about a duplicate cursor.
Is there any way to accomplish what I want?
Thanks!
david
--
David Gibbs
midrange.com
* IBM Power Systems Champion
IBM i on Power Systems: For when you can't afford to be out of business!
I'm riding a metric century (100 km / 65 miles) in the American Diabetes
Association's Tour de Cure to raise money for diabetes research, education,
advocacy, and awareness. You can make a tax deductible donation to my ride
by visiting
http://lsteml.diabetessucks.net. My goal is $6000 but any
amount is appreciated.
See where I get my donations from ... visit
http://lsteml.diabetessucks.net/map for an interactive map (it's a geeky
thing).
I may have diabetes, but diabetes doesn't have me!
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
midrange.com
