Untitled.sql

I use Tactical Connect instead of ODBC. It allows me to pull data out via saved searches. Could be worth a shot. www.tacticalconnect.com

I just convinced my company to pay 12 grand.

@Alan Fitch Could you load the results of the SuiteQL query into a custom record, and then pull from that instead?

I need it to be real time. And I really don't want the overhead of another custom record.

Just curious, did this work as a direct SuiteQL query? I have wondered what differences there might be between SuiteQL and ODBC with the NetSuite2.com data source. Do you have access to an intermediate database? You could run the individual CTE's, storing their results into temp tables, then combine the results externally. That's not ideal, but might work.

Yes. It runs in suiteql.

@Alan Fitch I just took a deeper look at that query, and it looks like you could write it without the CTEs. Sure, it would be tricky, but the hard work is already done.

I'm trying to write a python function that can compile queries with CTEs to subqueries.

I wasn't looking at the logic so much - just the structure of the query itself. It looks like you used CTEs primarily to make the query a little more manageable, which is why I think you could replace those and use subqueries instead. It would just be really ugly.