• If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Announcement

Collapse
No announcement yet.

Please allow SQL lateral keyword in free sql

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Please allow SQL lateral keyword in free sql

    Hi,
    I want to use one of IBM's table functions to retrieve information on uploaded IFS files.

    I created the following (working) SQL. statement:

    PHP Code:
    WITH libifs (ifsfullnameifsoid) AS(
    SELECT
    trim
    (fullname),
    oid
    FROM
    LIBBES l2
    )
    SELECT
    l
    .*,
    z.*,
    FROM
    libifs i
    ,
    libbes l,
    LATERAL (
    SELECT
    *
    FROM
    TABLE
    (
    QSYS2.IFS_OBJECT_STATISTICS(
    START_PATH_NAME => ifsfullname,
    SUBTREE_DIRECTORIES => 'NO'
    )
    )
    ) AS 
    z
    WHERE
    i
    .ifsoid l.oid 
    Unfortunately the datasource validator doesn't accept this statement:
    PHP Code:
    Expected Semicolon or end of input but "(" found 
    Is there a way around this?

    Kind regards, Theo

  • #2
    Theo,

    Please remove the comma after z.* and use join lateral

    Screen Shot 2020-09-15 at 1.08.40 PM.png

    Thanks

    Comment


    • #3
      Hi Johnny,

      This is my statement now (sorry for the comma copy/paste error)

      Code:
      WITH libifs (ifsfullname, ifsoid) AS (
      SELECT
      trim(fullname),
      oid
      FROM
      LIBBES l2
      )
      SELECT
      l.*,
      z.*
      FROM
      libifs i,
      libbes l,
      join LATERAL (
      SELECT
      *
      FROM
      TABLE(
      QSYS2.IFS_OBJECT_STATISTICS(
      START_PATH_NAME => ifsfullname,
      SUBTREE_DIRECTORIES => 'NO'
      )
      )
      ) AS z
      WHERE
      i.ifsoid = l.oid
      Now I get 2 warnings
      Code:
      File libifs not found
      and
      Code:
      File  not found
      on join LATERAL

      When I press next I get
      Code:
      SQL5001 - Column qualifier or table Z undefined.

      Comment


      • #4
        Manually enter your columns instead of using the wildcard (l.*, z.*)

        Comment


        • #5
          I test my statement in dBeaver now.

          Just using LATERAL works. Using JOIN LATERAL gives error SQL0199 - Keyword LATERAL not expected.

          Just to be sure: The working statement isn't accepted in the datasource. The alternative using JOIN LATERAL isn't accepted by SQL.
          Last edited by Theo Kusters; 09-15-2020, 02:00 PM.

          Comment


          • #6
            For testing purposes I created a version that will run everywhere:

            Code:
            WITH libifs (ifsfullname) AS (
            SELECT
            '/QOpenSys/usr/bin/chown'
            FROM
            "SYSIBM".SYSDUMMY1
            )
            SELECT
            z.access_timestamp,
            z.data_change_timestamp,
            z.last_used_timestamp,
            z.days_used_count,
            z.allocated_size,
            z.data_size
            FROM
            libifs i,
            LATERAL (
            SELECT
            *
            FROM
            TABLE(
            QSYS2.IFS_OBJECT_STATISTICS(
            START_PATH_NAME => ifsfullname,
            SUBTREE_DIRECTORIES => 'NO'
            )
            )
            ) AS z
            This is a valid sql statement but rejected by the Valence datasource editor

            Comment


            • #7
              Thanks, this will be available in the next update to Valence 6

              Comment

              Working...
              X