Hi all, anyone knows if suiteql supports WITH oper...
# suiteqld
Dmitry Masanov
12/17/2021, 3:37 PMHi all, anyone knows if suiteql supports WITH operator? (and how to use it)
Dmitry Masanov
12/17/2021, 3:42 PMI try the following:
with t1 as (select * from transactionline where transaction = 1791277) select t1.* from t1
but get
Search error occurred: Unsupported type: class com.netledger.sqlquery.model.SqlFactoredSourceReference : t1
s
Selcuk Dogru
12/17/2021, 3:48 PMd
Dmitry Masanov
12/17/2021, 3:59 PMThanks @Selcuk Dogru it's working! 🙂
a
Alan Fitch
12/17/2021, 7:22 PMBut not
WITH RECURSIVEr
redfishdev
12/18/2021, 1:05 PMwhat is the advantage of with? instead of inline like so :
redfishdev
12/18/2021, 1:05 PMSELECT
ID,
FullName
FROM
(SELECT
ID,
( LastName || ', ' || FirstName ) AS FullName,
Phone,
Email
FROM
Employee) as TestEmployee
ORDER BY
FullName
a
Alan Fitch
12/18/2021, 1:10 PMCan reference the table multiple times. Plus you can have many with statements, each referencing an earlier with. I think you are right that logically there is no query that you can do with a
WITHWITH RECURSIVEr
redfishdev
12/18/2021, 10:26 PMthx @Alan Fitch makes sense, like a cursor I think
redfishdev
12/19/2021, 2:57 AMAlan, did you remove the comment?
redfishdev
12/19/2021, 2:57 AMlooked like a function
a
Alan Fitch
12/19/2021, 2:58 AMI did. It's a function that gets the fifo status using suiteql it was formatted all wrong.
r
redfishdev
12/19/2021, 3:00 AMthis was a NS function?
redfishdev
12/19/2021, 3:00 AMif so then I have a lot to learn
a
Alan Fitch
12/19/2021, 3:00 AMIn a restlet.
r
redfishdev
12/19/2021, 3:05 AMits going to take a min to unpack this but thank you for the challange.
redfishdev
12/19/2021, 3:06 AMwhat does run2dQuery do?
redfishdev
12/19/2021, 3:29 AM@Alan Fitch that is impressive.. nested queries that build upon each other.. also learned about dense_rank from it.
a
Alan Fitch
12/19/2021, 4:12 AM Copy code
def run2dquery(q){
return query.runSuiteQL(q).asMappledResults()}Alan Fitch
12/19/2021, 4:13 AMIt also does paging but essentially that is what it does.
5 Views