https://netsuiteprofessionals.com logo
Join Slack
Powered by
`SELECT column_a, (SELECT column_b FROM table_b OR...
# suitescript
j
SELECT column_a, (SELECT column_b FROM table_b ORDER BY column_c LIMIT 1) AS referenced_column FROM table_a
s
I haven't tried that but would also like to know if it's supported!
j
I feel like I was able to do this in MySQL but it's throwing an error in SuiteQL
s
I do get the feeling that SuiteQL is a limited subset of full Oracle SQL, though I'd try to refer to Oracle SQL docs as a rough guide for what's possible (i.e. rather than MySQL docs)
j
I have about 12 years' experience with MySQL and 9 with PostGRESQL, hard to break old habits 😉
I have a sneaking suspicion I'm going to have to do something more like this:
Copy code
SELECT column_a, (
	SELECT column_b
	FROM table_b 
	WHERE table_b.column_c = (
		SELECT MAX(column_c) 
		FROM table_b
	)
) AS referenced_column 
FROM table_a
s
I have never loved SQL, but I know others here do. I'd be quite curious to hear if you can get any sort of nested query working in SuiteQL
j
Oh yeah nesting works.
In fact I have that second method working now.
s
if SuiteQL is rich enough, I can see it replacing all other search mechanisms as a universal solution.... despite my lack of love for SQL. 🙂
though a lack of any intellisense will be hard to swallow
j
I love SQL ❤️
here's what I've been doing all morning:
message has been deleted
👎 1
👍 1
facepalm 1
t
I'm a SQL fan as well, and the SuiteQL support in SuiteTalk REST has been a game-changer for me. It definitely has some limitations and takes getting used to. I keep coming across weird stuff - like that it doesn't support DISTINCT. And you have to be insanely detailed about the account you're using and the permissions it has. Also, the errors that it kicks back are less than helpful at times.
s
I am so glad that's NOT what I've been working on all morning, but I do kinda envy folks that can love SQL.
c
@tdietrich it does support DISTINCT, but if you use that or any other aggregate or analytic method on one column, you have to do something similar with your other columns in the SELECT clause or GROUP BY the others.
@jen there's no 
LIMIT x
support that I know of - instead, you need to add 
WHERE rownum <= x
t
@Clay Roper Interesting. I can't get DISTINCT to work even with the most basic query. For example, 
SELECT DISTINCT Type FROM Transaction
returns an "INVALID_PARAMETER" error ("Invalid search query Search error occurred: Invalid or unsupported search.").
c
@tdietrich I use DISTINCT all the time to get the shape of tables due to the paucity of documentation in this area. Are you using 
N/query
in Suitescript or the Connect Service?
@tdietrich also I was wrong to conflate DISTINCT with analytic/aggregate functions -- late in the day for me here 🙄
t
I'm executing the SQL via SuiteTalk REST call.
I ran across this issue not too long ago, and mentioned in a blog post yesterday: https://timdietrich.me/blog/netsuite-suiteql-transaction-statuses/ So I ended up doing: 
SELECT Transaction.Status FROM Transaction GROUP BY Transaction.Status
which works, but just feels... wrong.
c
I use 
query.runSuiteQL()
dozens of times a day, and I often run a version of 
SELECT DISTINCT type FROM transaction
to probe for column details with no issue. I just ran that exact query and got the results I expected. I wonder if there's a difference under the hood in terms of implementation between queries run via SuiteTalk and N/query, but I can't imagine why that would be. There are plenty of odd differences between the various data models / schemas available depending on whether you're using ODBC vs saved search vs N/query, so I can't count anything out...
t
Yeah, there are most definitely differences. SuiteTalk can give back some very odd errors and responses at times, especially for SuiteQL queries.
One example: If you don't have permissions on certain Transaction types, and you query for one of them, instead of returning a permission-related error, there's a good change it will just return an empty response. So the role of the account you're using when making the SuiteTalk calls is very important.
💯 1
Regardless, I'm still finding it to be incredibly useful. It's nice to be able to run ad-hoc SQL queries against the back end.
c
Regarding your blog post: one thing I've noted when it comes to dealing with the transaction table and querying by type is that you might need to concatenate the transaction type into your status query. For example, if I want to rule out closed Opportunities, I can't use
Copy code
SELECT id, status
FROM transaction
WHERE type = 'Opprtnty' AND
status NOT IN ('C', 'D')
because I'll get back results that include status of 'C' and 'D'. Instead, I need to include the type in the WHERE clause like
Copy code
SELECT id, status
FROM transaction
WHERE type = 'Opprtnty' AND
status NOT IN (type||'C', type||'D')
even though the status column only returns the letter (A, B, C,...)
t
Yikes. I don't know that I've seen that one yet.
c
regarding the ad-hoc queries, I agree. I made a dead-simple Suitelet to run these kinds of queries, and also make heavy use of Chrome's devtools to spelunk through the database and sketch out the tables and their relationships as part of the dev process
t
I've thought about doing it that way, too. I'm curious to see if there is any difference with the response times.
c
There just might be...
j
Because you guys seem to know a lot more about SuiteQL than I do (having only started using it….yesterday) I’ll pose a question here. I’m trying to leverage LAST_VALUE to “fill in the blanks” as it were. To do so, I need to use IGNORE NULLS but as soon as I use that I get an error. Is IGNORE NULLS not supported?
s
what editor are you all using for NetSuite SQL, and does it support any sort of intellisense/highlighting both for the appropriate SQL dialect and the schema in NS?
t
@jen I've never tried it myself, but I think it will work. The syntax would probably look like this: 
SELECT time_id, product, LAST_VALUE(quantity IGNORE NULLS) OVER (PARTITION BY product ORDER BY time_id) quantity FROM...
@stalbert I wish I had a nice SQL tool that did those things. For ad-hoc queries and to help with dev work, I developed a really simple PHP-based app. It takes a query, cleans it up a bit, sends it to SuiteTalk REST, and displays the response. I wrote a little about it here: https://timdietrich.me/blog/netsuite-suiteql-query-tool/
👍 1
j
IGNORE NULLS isn't ignoring nulls....sigh
message has been deleted
t
@jen What are you going to do with the data? If it's only going to be used for display purposes, could you handle the "Last_Value" logic in the display layer?
j
This is just a tiny chunk of a huge SQL I need to run where I have to fill in those 'missing' values with the previous non-null value, before doing other things like GROUPING and SUMMING other columns
basically it should copy 2400 down to where the 2401 starts, then 2401 down to where the 1702 starts, and so on.
c
@jen have you figured out the 
IGNORE NULLS
issue?
j
nope
I don't think it's necessarily a NetSuite issue, just my unfamiliarity with LAST_VALUE (which I haven't used before)
c
I have been futzing with it a bit, and I also can't get it to work. In my testing, I replaced 
LAST_VALUE
with 
FIRST_VALUE
including 
IGNORE NULLS
and since the first value was a null, I got an entire column of nulls. Nothing about that seems right 😄
j
As far as I can tell, SuiteQL just flat out ignores the "IGNORE NULLS"
which really sucks
as this would be super helpful for what I'm trying to achieve
c
maybe you can use something like 
NVL(loc.id,LAG(loc.id,x) OVER (ORDER by tx.id, tl.linesequencenumber))
- the key is to make the lag amount (
x
) dynamic so that it's able to pull from the last non-null row. You might be able to leverage 
ROW_NUMBER()
somehow, or otherwise accumulate a count of contiguous 
null
rows to get that offset.
j
Yeah I thought about that, but couldn't figure out how to make the x the right dynamic value.
c
On further fiddling, I'm pretty sure that's out, as LAG doesn't appear to accept analytic results as an offset value
Open in Slack
PreviousNext
Powered by