Suiteql Left join and Coalesce not working

AI generated answer (using internal Slack threads):

****** Disclaimer: This response was generated by an artificial intelligence assistant based on previous conversations from multiple Slack channels, and may contain inaccuracies or mistakes. ****** From the conversations, it seems that you are having trouble with using OUTER JOIN in SuiteQL. In NetSuite, the OUTER JOIN is not directly supported in SuiteQL. However, you can use LEFT JOIN or RIGHT JOIN as an alternative. Here is an example of how you can use LEFT JOIN in SuiteQL:

sql
SELECT TransactionLine.Transaction as tid
FROM TransactionLine
LEFT JOIN Item ON (TransactionLine.item=item.id) where item.id=134543

In the context of Saved Searches, all joins are OUTER joins by default. You can simulate other types of joins by using groups and result column formulas, but be aware that this can lead to a significant performance hit. As for the window functions in your SQL query, SuiteQL does not support window functions like

SUM() OVER()

,

ROW_NUMBER() OVER()

. You might need to adjust your logic or find a workaround to achieve the same result in SuiteQL. Remember, when writing SuiteQL queries, it's important to understand the limitations and differences between SQL and SuiteQL to ensure your queries run efficiently and return the expected results.