MVSFORUMS.com

danm · Intermediate Joined: 29 Jun 2004 Posts: 170 Topics: 73

I have the following in the PL1 program:

dbzTHEdinosauer · Posted: Mon Nov 01, 2010 12:01 pm Post subject:

which version of db2?
_________________
Dick Brenholtz
American living in Varel, Germany

kolusu · 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 ?

jsharon1248 · 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:

kolusu · Posted: Mon Nov 01, 2010 4:03 pm Post subject:

dbzTHEdinosauer · Posted: Mon Nov 01, 2010 4:44 pm Post subject:

dbzTHEdinosauer · 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

dbzTHEdinosauer · 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

jsharon1248 · Posted: Tue Nov 02, 2010 7:49 am Post subject:

Brain cramp. Thanks for highlighting the version differences dbz.

dbzTHEdinosauer · 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

jsharon1248 · 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.

dbzTHEdinosauer · 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

danm · Intermediate Joined: 29 Jun 2004 Posts: 170 Topics: 73

Our DB2 is version 7 and somehow I missed the complied message ""ORDER BY" CLAUSE NOT PERMITTED". Thank you all.

dbzTHEdinosauer · Posted: Thu Nov 04, 2010 9:31 am Post subject:

danm,

thx for the feedback.
_________________
Dick Brenholtz
American living in Varel, Germany