https://netsuiteprofessionals.com logo
Join Slack
Powered by
If I'm new to SQL, am I better off learning to con...
# suiteql
d
If I'm new to SQL, am I better off learning to construct queries via query.runSuiteQL() with whole SQL queries, or constructing the queries with query.create() and using the various Query Object methods (
autoJoin()
, 
createCondition()
, 
createColumn()
, etc)?
I have grabbed tdietrich/Tim Dietrich's NetSuite SuiteQL Query Tool, useful for learning =]
a
I can't imagine that using 
query.create
is any better.
c
query.create()
will only serve you for Netsuite, whereas learning SuiteQL will get you started on a skill set that's relevant in other domains
s
My own is experience is that 
query.create
is clunky and requires many more line of code compared to the same query in SuiteQL, and is even harder to read and maintain than a saved search. Having used both, I strongly prefer using an actual SuiteQL query.
💯 1
j
I’ve made a couple of tools for running SQLs. One is a quick-n-dirty straight up “run this SQL” tool, looks like this:
message has been deleted
the other is for SQLs I’ll want to run multiple times, where I might want to be able to adjust arguments. It has the option to define arguments and also reuse chunks of SQL (so say you had a WHERE clause that would appear in multiple places within one big query). For this tool I’ve made a custom record type called Saved SQL. Looks like this:
this one runs the SQL, spits out the results (with option to download of course) and also displays the complete SQL that it actually ran (with the REUSABLES and ARGUMENTS replaced)
s
@jen Saved SQL, I like that!
j
It has literally “saved” me a few times
d
yeah, so basically put the leg work in now to learn SQL properly, with the added bonus of neater code (
asMappedResults()
is a handy method)
@jen, I take it that 'saved SQL' tool/suitelet isn't public?
a
Check out tim dietrichs tool. Its really awesome.
I played around with it and now I have syntax highlighting, vim, and autoformatting.
Here it is
SQLSuitelet.js
Ctrl Shift F : Formats the SQL Ctrl Shift Q: Runs the query You can toggle vim with the button on top.
d
I have the non-beta version atm. I wonder if Tim would want those changes for his repo (can't find a public one)
a
I reached out to him but he was busy. Also ysk that it is a bit kludgy.
The beta version has a lot of new features. Plus I like living on the edge.
s
@jen and @tdietrich should join forces to make one querytool to rule them all!
j
LOL
t
I am hoping to release the 2021.2 version of the SuiteQL Query Tool very soon - maybe as early as Thursday. There are a few changes that I've made to it since the last beta, including an option to omit the total # of results in the response (which speeds some queries up considerably). But I think the most important change is that it adds support for what I've been referring to as "virtual views." Essentially, you can refer to saved queries in your queries, and the tool will resolve them as if they were real SQL views / tables. I have looked at @Alan Fitch’s enhancements to the tool, and they are pretty cool. And while I'm not going to be able to include them in the next release of the tool, this has made begin to think about adding support for plug-ins and add-ons.
Here's an example of the "virtual view" concept. Suppose you save this query to your File Cabinet, and name the file TestEmployee.sql:
Copy code
SELECT
	ID,
	LastName,
	FirstName,
	Phone,
	Email
FROM
	Employee
WHERE
	Email LIKE '%@test.com'
ORDER BY
	LastName,
	FirstName
You can then refer to it in another query, like this:
Copy code
SELECT
	TestEmployee.*,
	( Supervisor.LastName || ', ' || Supervisor.FirstName ) AS SupervisorName
FROM
	#TestEmployee
	INNER JOIN Employee AS Supervisor ON
		( Supervisor.ID = TestEmployee.Supervisor )
j
Cool
a
@tdietrich That sounds pretty cool. I would just suggest a way to export the query with the views substituted. After all, I presume most of use use suiteql for prototyping queries for suitescript.
One of the pitfalls of views is that it can encourage some pretty poorly written queries imho. Once the query is abstracted away into a view, little thought is given to optimizing it and it can lead to some very poorly written queries 4 layers up.
6 Views
Open in Slack
PreviousNext
Powered by