Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2010
    Posts
    9

    Unanswered: Subselect query not working only on DB2 V9.1

    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.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Add AS s, like this...
    db2 "select ..... DBPARTITIONNUM) AS s"

    It was suggested in the error message text.
    SQL0104N ... Expected tokens may include: "AS". ...

  3. #3
    Join Date
    Feb 2010
    Posts
    9
    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

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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 [, ...] )]

  5. #5
    Join Date
    Feb 2010
    Posts
    9
    Thanks for the explanation. Cheers!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •