| View previous topic :: View next topic |
| Author |
Message |
danm
Intermediate
Joined: 29 Jun 2004
Posts: 170
Topics: 73
|
 Posted: Wed Feb 08, 2012 3:16 pm Post subject: SQL Cursor in PL1 |
 |
|
I want to order by DESC or ASC depending the value of a Flag:
| Code: |
If Flag = '1'B then
EXEC SQL DECLARE CURTMP CURSOR FOR
SELECT COL1
FROM TABLE
WHERE COL2 = :VALUE2
ORDER BY COL1 DESC;
Else
EXEC SQL DECLARE CURTMP CURSOR FOR
SELECT COL1
FROM TABLE
WHERE COL2 = :VALUE2
ORDER BY COL1 ASC;
EXEC SQL OPEN CURTMP;
EXEC SQL FETCH CURTMP INTO :FLD:NULLFLD;
.......
.......
EXEC SQL CLOSE CURTMP;
|
When complied, an error : CURSOR "CURTEMP" PREVIOUSLY DECLARED OR REFERENCED.
I can fix the problem by declaring two cursors and duplicating the codes.
| Code: |
If Flag = '1'B then do;
EXEC SQL DECLARE CURTMP CURSOR FOR
SELECT COL1
FROM TABLE
WHERE COL2 = :VALUE2
ORDER BY COL1 DESC;
EXEC SQL OPEN CURTMP;
EXEC SQL FETCH CURTMP INTO :FLD:NULLFLD;
.......
.......
EXEC SQL CLOSE CURTMP;
End;
Else do;
EXEC SQL DECLARE CURTMP1 CURSOR FOR
SELECT COL1
FROM TABLE
WHERE COL2 = :VALUE2
ORDER BY COL1 ASC;
EXEC SQL OPEN CURTMP1;
EXEC SQL FETCH CURTMP1 INTO :FLD:NULLFLD;
.......
.......
EXEC SQL CLOSE CURTMP1;
End;
|
How can I avoid duplicate codes? |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Wed Feb 08, 2012 4:35 pm Post subject: |
|
|
danm,
what is the format of the column you are fetching? is it numeric?
Kolusu |
|
| Back to top |
|
|
CZerfas
Intermediate
Joined: 31 Jan 2003
Posts: 211
Topics: 8
|
| Posted: Thu Feb 09, 2012 9:18 am Post subject: |
|
|
Since the coding you want to change is part of an EXEC SQL statement, you can only perform this by using dynamic SQL. Within static SQL, as you are using it, this isn't possible.
regards
Christian |
|
| Back to top |
|
|
danm
Intermediate
Joined: 29 Jun 2004
Posts: 170
Topics: 73
|
| Posted: Thu Feb 09, 2012 9:36 am Post subject: |
|
|
Kolusu,
It is a small integer. |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Thu Feb 09, 2012 10:47 am Post subject: |
|
|
other than dynamic sql as CZerfas has stated
one way to do it so that you only have one imbedded cursor
would be to use as CASE clause in your select statement
CASE WHEN :FLAG = 1 THEN COL1 ELSE -1 * COL1 END
with a DESC ORDER BY clause.
but then you would also have to have PL1 code to convert the host variable for COL1 based on your flag.
using two cursors means two sets of logic processing the different cursors.
using one imbedded cursor with the CASE logic means having a compare and a multiply in your PL1 code.
if this is only an esoteric exercise, use the CASE logic.
but if this is for an actual program, use two cursors or dynamic sql.
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
danm
Intermediate
Joined: 29 Jun 2004
Posts: 170
Topics: 73
|
| Posted: Thu Feb 09, 2012 11:07 am Post subject: |
|
|
CZerfas and dbzTHEdinosauer,
It is for an actual program. I will use with the 2 cursors approach and static sql. Thanks. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Thu Feb 09, 2012 12:27 pm Post subject: |
|
|
danm,
If it was a numeric column I was going to suggest what DBZ have suggested i.e multiply the column with -1 and always use the ASC sequence which is the default. I guess 2 static sql's are better for understanding and at any point you will be dealing with just 1 active cursor.
Kolusu |
|
| Back to top |
|
|
danm
Intermediate
Joined: 29 Jun 2004
Posts: 170
Topics: 73
|
| Posted: Thu Feb 09, 2012 4:00 pm Post subject: |
|
|
| Thank you all. |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
