MVSFORUMS.com

[mangsk]

Hi,
I've following query in my COBOL-DB2 program.

EXEC SQL
SELECT COUNT(*)
INTO :WS-GLSSVLD1-CO-DES-CNT
FROM TABLE_NAME
GROUP BY SYS_ID
ORDER BY 1 DESC
FETCH FIRST 1 ROWS ONLY
END-EXEC.

wherein the variable in bold is declared in working storage and I'm using it as host variable.

It gives me the following error during compilation:
"ORDER BY" CLAUSE NOT PERMITTED
I executed same query in spuffi(of course,after removing INTO clause) and that gave me the output without any error.

So,any idea about possible reasons of getting the error in the COBOL-DB2 program? Is it because of the INTO clause ?
Thanks in advance.
_________________
Regards,
Mangsk

[kolusu]

Mangsk,

You cannot have an ORDER by Clause with FETCH FIRST clause unless you define it as a cursor. so define a cursor for the sql statement and you should not have any problem.

Hope this helps...

Cheers

kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

[Cathygomez]

mangsk,
Try this,
EXEC SQL
SELECT COUNT(*)
INTO :WS-GLSSVLD1-CO-DES-CNT
FROM TABLE_NAME
GROUP BY SYS_ID
FETCH FIRST 1 ROWS ONLY
ORDER BY 1 DESC
END-EXEC.

Here ORDER BY Clause comes next to FETCH

[CZerfas]

Using a singleton select instead of defining a cursor means, that youn KNOW, that there is only one row qualifying for your query. Why would you want to sort?

Issuing that singleton select from Spufi means, that the Spufi program turns your select statement into a cursor definition and executes it. Therefor ordering is possible.

regards
Christian