Hi Guys,

The subselect query mentioned below works for DB2 V9.7 and 9.5 databases but FAILS on DB2 V9.1 -
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>
db2 "select SUM(FSFreeSizeKB) from (select distinct SUM(FS_TOTAL_SIZE_KB)-SUM(FS_USED_SIZE_KB) FSFreeSizeKB from SYSIBMADM.CONTAINER_UTILIZATION where TBSP_NAME = 'USERSPACE1' AND ACCESSIBLE = 1 group by FS_ID, CONTAINER_ID, DBPARTITIONNUM)"
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>

The following error message is displayed for V9.1 -

SQL0104N An unexpected token "" was found following ")". Expected tokens may include: "AS". SQLSTATE=42601

Please let me know what could be the reason of failure on V9.1

Thanks,
Ronak.

Add AS s, like this...
db2 "select ..... DBPARTITIONNUM) AS s"

It was suggested in the error message text.

Thanks!!! I had initially tried using AS .. after SUM(FSFreeSizeKB) and not at the end. But still dont know why this depedency for V9.1

It is correlation-clause, and was mandatory until DB2 9.1 for LUW.
It is also mandatory on DB2 for z/OS and DB2 for iSeries.

You can see syntax in the manual "DB2 SQL Reference", like this...

nested-table-expression:
[LATERAL] ( fullselect ) correlation-clause

correlation-clause:
[AS] correlation-name [( column-name [, ...] )]

Thanks for the explanation. Cheers!!!