Functions in free format Data source

[Pavan]

Hi,
I am trying to run the below query to just display the jobs in MSGW :

SELECT JOB_NAME,SUBSYSTEM,AUTHORIZATION_NAME, 054 JOB_TYPE,JOB_STATUS,FUNCTION,CPU_TIME 055 FROM TABLE(QSYS2.ACTIVE_JOB_INFO(JOB_NAME_FILTER => '*ALL')) A 056 WHERE JOB_STATUS = 'MSGW' 057 ORDER BY ORDINAL_POSITION ;

However, it throws an error - the keyword 'Table' is not recognized.
Is there a way to get this function executed?

Valence Version: 5.1.20180118.0

Regards,
Pavan

[sean.lanktree]

Hi Pavan -

Did you verify that this statement runs on your IBM i?

[Pavan]

Hi Sean,
Sorry, looks like the line numbers also got copied while pasting the SQL.

Here is the actual SQL that I was trying to execute:
SELECT JOB_NAME,SUBSYSTEM,AUTHORIZATION_NAME,
JOB_TYPE,JOB_STATUS,FUNCTION,CPU_TIME FROM
TABLE(QSYS2.ACTIVE_JOB_INFO(JOB_NAME_FILTER => '*ALL')) A WHERE
JOB_STATUS = 'MSGW' ORDER BY ORDINAL_POSITION

And yes, this works on IBM i.

[sean.lanktree]

Hi Pavan, unfortunately this type of statement is not currently supported within NAB. We have created an internal ticket for this.

In the meantime, you can do the following:

- Create a new file with the same fields that your sql statement will produce.
- Create a new RPG program that runs your statement and inserts the results into your new file.
- Create a new data source over the new file that you created.
- When saving the data source, enter in the name of your new RPG program as the "Pre-Execution Program".

[Pavan]

Hi Sean,
Thanks for the update. Will follow this process.
Thanks,
Pavan

[Pavan]

Hi Sean,
The idea you suggested above worked.
Thanks.

[sean.lanktree]

Glad to hear that. Thanks for letting us know.

[Johnny Major]

Pavan,

In the next Valence 5.2 update the NAB SQL parser will support the TABLE syntax.

Thanks