Error in Valence AI sql scripts

[Bruce.Wilson]

Hello,
I copied a data source and used Valence AI to modify the existing script that added a "with" section. It looked great but when I click next, I get "ERROR" Expected Semicolon or end of input but "(" found. I told the AI that the code had an error and it gave me a new version... The new verison of the SQL script gave me this "ERROR" SQL0104 - Token + was not valid Valid tokens:,FROM INTO.. I tried this two more time and still received errors each time. When I copy the code and run it directly on the IBMi thru Run SQL Scripts all four versions run without any errors. How can I fix this?
Thanks,
Bruce

[Johnny Major]

Can you provide us with the exact SQL statement you are trying to enter for the data source? Thanks

[Bruce.Wilson]

Here it is.. Works in "Run SQL Scripts"

with dt as (
select
date('20' concat substr(digits(g.gdsdte),1,2) concat '-' concat substr(digits(g.gdsdte),3,2) concat '-' concat substr(digits(g.gdsdte),5,2)) as trueDate,
min(m.glname) as glname,
sum(g.gdamt) as dailyAmt,
min(m.glbbal) as initGlbbal
from glf.gldtlp g
join glf.glmstrp m on g.gdacct = m.glact#
where g.gdacct = 9551500
group by date('20' concat substr(digits(g.gdsdte),1,2) concat '-' concat substr(digits(g.gdsdte),3,2) concat '-' concat substr(digits(g.gdsdte),5,2))
)
select
dt.trueDate as trueDate,
dt.glname as glname,
dt.dailyAmt as dailyAmt,
sum(dt.dailyAmt) over (order by dt.trueDate rows unbounded preceding) + first_value(dt.initGlbbal) over (order by dt.trueDate rows unbounded preceding) as ARBAL
from dt
order by dt.trueDate

[Bruce.Wilson]

I ended up creating an SQL view and referenced it as my data source and it worked. Guessing that some of the advanced SQL functions aren't support by Valence yet.

[Johnny Major]

In the latest 6.3 update, "6.3.20250708", released yesterday, window functions, like the ones you were attempting to perform, are now supported. Thanks for letting us know.