View previous topic :: View next topic |
Author |
Message |
danm Intermediate
Joined: 29 Jun 2004 Posts: 170 Topics: 73
|
Posted: Mon Nov 01, 2010 11:58 am Post subject: PL1 and SQL |
|
|
I have the following in the PL1 program:
Code: |
EXEC SQL SELECT FIELD_ID INTO :TmpFld
FROM TABLE1
WHERE ....
AND ....
FETCH FIRST 1 ROW ONLY;
|
The return code for the SQL component is 0 from the complier.
If "ORDER BY" clause is added:
Code: |
EXEC SQL SELECT FIELD_ID INTO :TmpFld
FROM TABLE1
WHERE ....
AND ....
ORDER BY FIELD_ID
FETCH FIRST 1 ROW ONLY;
|
Why the return code for the SQL component is 8?
[/code] |
|
Back to top |
|
|
dbzTHEdinosauer Supermod
Joined: 20 Oct 2006 Posts: 1411 Topics: 26 Location: germany
|
Posted: Mon Nov 01, 2010 12:01 pm Post subject: |
|
|
which version of db2? _________________ Dick Brenholtz
American living in Varel, Germany |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12369 Topics: 75 Location: San Jose
|
Posted: Mon Nov 01, 2010 12:11 pm Post subject: |
|
|
danm,
Are you calling DSNTIAR after the sql call? If so what is the reason code ?
Code: |
DCL DSNTIAR ENTRY OPTIONS (ASM,INTER,RETCODE);
DCL DATA_LEN FIXED BIN(31) INIT(132);
DCL DATA_DIM FIXED BIN(31) INIT(10);
DCL 1 ERROR_MESSAGE AUTOMATIC,
3 ERROR_LEN FIXED BIN(15) UNAL INIT((DATA_LEN*DATA_DIM)),
3 ERROR_TEXT(DATA_DIM) CHAR(DATA_LEN);
your sql call
CALL DSNTIAR ( SQLCA, ERROR_MESSAGE, DATA_LEN );
PUT SKIP LIST('Error Message IS : ',ERROR_TEXT);
|
Kolusu |
|
Back to top |
|
|
jsharon1248 Intermediate
Joined: 08 Aug 2007 Posts: 291 Topics: 2 Location: Chicago
|
Posted: Mon Nov 01, 2010 3:59 pm Post subject: |
|
|
Wouldn't a SELECT INTO and an ORDER BY be mutually exclusive? SELECT INTO demands that a single record will be returned, so the ORDER BY would have no meaning. Something like this untested SQL might work:
Code: | SELECT T.FIELD_ID INTO :TMPFLD
from (SELECT FIELD_ID
FROM TABLE1
WHERE ....
AND ....
ORDER BY FIELD_ID
FETCH FIRST 1 ROW ONLY) as t |
The performance might stink if there are a large number of rows that must be materialized for the sort. |
|
Back to top |
|
|
kolusu Site Admin
Joined: 26 Nov 2002 Posts: 12369 Topics: 75 Location: San Jose
|
Posted: Mon Nov 01, 2010 4:03 pm Post subject: |
|
|
jsharon1248 wrote: | Wouldn't a SELECT INTO and an ORDER BY be mutually exclusive? SELECT INTO demands that a single record will be returned, so the ORDER BY would have no meaning. |
Hmm isn't FETCH 1 ROW restricting the final output to just 1 row?
Kolusu |
|
Back to top |
|
|
dbzTHEdinosauer Supermod
Joined: 20 Oct 2006 Posts: 1411 Topics: 26 Location: germany
|
Posted: Mon Nov 01, 2010 4:44 pm Post subject: |
|
|
jsharon1248 wrote: | Wouldn't a SELECT INTO and an ORDER BY be mutually exclusive? SELECT INTO demands that a single record will be returned, so the ORDER BY would have no meaning. |
you can not mean that! _________________ Dick Brenholtz
American living in Varel, Germany |
|
Back to top |
|
|
dbzTHEdinosauer Supermod
Joined: 20 Oct 2006 Posts: 1411 Topics: 26 Location: germany
|
Posted: Mon Nov 01, 2010 5:16 pm Post subject: |
|
|
following up on my above post.
A SELECT INTO statement that results in more than a 1 row result set,
will return a -811 SQLCODE.
you can not use ORDER BY in a SELECT INTO statement for vsn 7 and lower
for DB2 VSN 7
order by is not included in the syntax for SQL REFERENCE FOR db2 vsn 7, z/os & 390, as shown here
in the DB2 UDB for OS/390 and z/OS V7 Application Programming and SQL Guide
at the bottom of the page,
it again shows that SELECT INTO with FETCH FIRST 1 ROW
is only to prevent -811s
now, vsn 8 and above allows the ORDER BY clause in a SELECT INTO statement
believe me, it is true, i recently fought this battle with a vsn6 to vsn7 upgrade trying to take care of -811's that would for vsn6 and lower, populate variables with -811.
as of VSN 7, there would is no population of host variables with a -811.
VSN 8 allowed you to ORDER BY in a SELECT INTO.
that is why I asked what db2 vsn is being used.
it matters.
the same rules apply to subselects, also _________________ Dick Brenholtz
American living in Varel, Germany |
|
Back to top |
|
|
dbzTHEdinosauer Supermod
Joined: 20 Oct 2006 Posts: 1411 Topics: 26 Location: germany
|
Posted: Tue Nov 02, 2010 4:59 am Post subject: |
|
|
The first answer/instruction that should have been given to the Topic Starter:
look in the output from the compiler and see what error messages were provided _________________ Dick Brenholtz
American living in Varel, Germany |
|
Back to top |
|
|
jsharon1248 Intermediate
Joined: 08 Aug 2007 Posts: 291 Topics: 2 Location: Chicago
|
Posted: Tue Nov 02, 2010 7:49 am Post subject: |
|
|
Brain cramp. Thanks for highlighting the version differences dbz. |
|
Back to top |
|
|
dbzTHEdinosauer Supermod
Joined: 20 Oct 2006 Posts: 1411 Topics: 26 Location: germany
|
Posted: Tue Nov 02, 2010 2:25 pm Post subject: |
|
|
jsharon1248
I apologize for being for being so aggressive with my response.
dbz _________________ Dick Brenholtz
American living in Varel, Germany |
|
Back to top |
|
|
jsharon1248 Intermediate
Joined: 08 Aug 2007 Posts: 291 Topics: 2 Location: Chicago
|
Posted: Wed Nov 03, 2010 8:15 am Post subject: |
|
|
dbz, aggressive is what we like about you. You backed it up with a detailed explanation, and quite frankly, my response was not well thought out. No apology necessary. |
|
Back to top |
|
|
dbzTHEdinosauer Supermod
Joined: 20 Oct 2006 Posts: 1411 Topics: 26 Location: germany
|
Posted: Wed Nov 03, 2010 10:07 am Post subject: |
|
|
jsharon1248,
we seem to have the problem well in hand,
i wonder about the TS?
His posting history indicates, start a thread and rarely respond.
definitely not a believer in feedback _________________ Dick Brenholtz
American living in Varel, Germany |
|
Back to top |
|
|
danm Intermediate
Joined: 29 Jun 2004 Posts: 170 Topics: 73
|
Posted: Thu Nov 04, 2010 8:51 am Post subject: |
|
|
Our DB2 is version 7 and somehow I missed the complied message ""ORDER BY" CLAUSE NOT PERMITTED". Thank you all. |
|
Back to top |
|
|
dbzTHEdinosauer Supermod
Joined: 20 Oct 2006 Posts: 1411 Topics: 26 Location: germany
|
Posted: Thu Nov 04, 2010 9:31 am Post subject: |
|
|
danm,
thx for the feedback. _________________ Dick Brenholtz
American living in Varel, Germany |
|
Back to top |
|
|
|
|