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