Is “Rank” not a supported aggregate function in su...
# suiteqlc
Cory Weiner
11/02/2021, 9:24 PMIs “Rank” not a supported aggregate function in suiteql? I just get the error “Invalid or unsupported search”
c
Clay Roper
11/02/2021, 9:27 PMRANK and DENSE_RANK are both supported. It's probably mad at how you are trying to use it. Share your query?
Clay Roper
11/02/2021, 9:27 PMc
Cory Weiner
11/02/2021, 9:28 PMHere is a basic one that should work I would think:
select RANK(id), id from customer group by id
Cory Weiner
11/02/2021, 9:28 PMOr even:
select RANK(1)
does not work
Cory Weiner
11/02/2021, 9:29 PMBleh nevermind I think I found the correct syntax
RANK() OVER(ORDER BY id)
c
Clay Roper
11/02/2021, 9:30 PMYep! That looks more like it 😁
s
stalbert
11/02/2021, 9:50 PMit scares me to imagine y'all writing complex queries due to suiteql. Talk about hard to debug code 😕
c
Clay Roper
11/02/2021, 9:52 PMDon't worry about us @stalbert, the Netsuite error messages are SO HELPFUL
🤣 2
Clay Roper
11/02/2021, 9:52 PM😄
s
stalbert
11/02/2021, 9:53 PMsorry, I've always been a bit biased against complex SQL in code because there's generally little to no help from the main programming language or debugging tools. It's just a bunch of ad-hoc text that is actually database programming in disguise :)
w
Watz
11/02/2021, 9:59 PMIt's really convinient to replace several lines of code to join two searches and filter the result compared to writing at SuiteQL with all the joins.
👍 1
s
stalbert
11/02/2021, 10:42 PMagreed, that's a good use of SuiteQL IMHO. It's these hundreds of lines long queries people talk about with all sorts of SQL functions involved doing lot of heavy lifting that make me sad, both for the people authoring them and the people that support them years down the road.
stalbert
11/02/2021, 10:42 PMdoing multiple or ad-hoc joins is a strong point of SuiteQL and hence makes a lot of sense to me over coding it up.
stalbert
11/02/2021, 10:44 PMon the other hand, doing things in SQL that have better (easier to write and maintain) representation in TypeScript is troubling. I try to use code for what code is good at and sprinkle in SQL where SQL shines.
stalbert
11/02/2021, 10:44 PMI say typescript above because of all the benefits strong typing brings that SuiteQL doesn't enjoy (at least not in the typical IDE)
c
Clay Roper
11/03/2021, 2:24 PMOur team went all in on SuiteQL at first and crafted these lumbering behemoths of queries that would spit out exactly what we were looking for; that is, after sending Netsuite's servers into dozens of seconds of groaning, shuddering work, and the slightest change we'd need to make to our target result set would involve sacrifices I can't even admit to in a public forum.
We've since come to a place of running as lean queries as possible for datasets we can slice, dice, and saute in SuiteScript after the fact. Developer sanity is much better for it 😁
🎯 1
💯 1
s
stalbert
11/03/2021, 3:40 PMhaving libraries like
lodash3 Views