Re: SQL, with CTE

Hi Gerard,

You always need a table designator after a "...from table(...)", for example (using "XX" as the designator):

with ifsdirs (dir) as (
select PATH_NAME from table(QSYS2.IFS_OBJECT_STATISTICS('/tmp', 'NO', '*DIR')) XX
)

select PATH_NAME, count(*)

from table (QSYS2.IFS_OBJECT_STATISTICS(dir,'YES')) XX

group by PATH_NAME

You also had an extra comma after the CTE.

Tim.

________________________________
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Gerald Magnuson <gmagqcy.midrange@xxxxxxxxx>
Sent: 30 September 2020 22:12
To: midrange-l@xxxxxxxxxxxxxxxxxx <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: SQL, with CTE

I want to list some sub directories in the IFS, and how many files in them.
with ifsdirs (dir) as (
select PATH_NAME from table(QSYS2.IFS_OBJECT_STATISTICS('/tmp', 'NO',
'*DIR')) ) ,

select PATH_NAME, count(*)
from table (QSYS2.IFS_OBJECT_STATISTICS(dir,'YES'))
group by PATH_NAME
;

I have a list of "dirs" I am wanting to iterate through.
error is "PATH_NAME" was not valid (highlight the var next to the count(*))
--