CODEWITHSUNDEEP
sqlnetsuite
Todd Grimm
Todd Grimm

Reputation: 544

Using Dense_Rank as criteria of a saved search in Netsuite

I am trying to create a search for a client that shows their opportunities along with the notes tied to each opportunity. They do not want the screen clogged up with multiple entries if there are more than 1 note tied to the opportunity. They want to see each opportunity once, and only the most recent note tied to that opportunity. I figured that using DENSE_RANK to rank the notes and request that only the note with a rank of 1 is returned. I know the formula works, but when adding it to criteria to limit results I receive an error. I have played around with using NVL in case of a null error to no avail.

The results formula which works to show only the latest note ( I still end up with a blank line for each additional note though) is below. Using Formula (Text):

CASE WHEN (DENSE_RANK() over (partition by {title} order by {usernotes.internalid} desc)) = '1' THEN {usernotes.note} ELSE END

The criteria formula which would limit the results is below. Im using Formula (Numeric) and the description is set to is 1:

DENSE_RANK() over (partition by {internalid} order by {usernotes.internalid} desc NULLS LAST)

The error message I receive when attempting to run the search after adding in the DENSE_RANK criteria is:

An unexpected error has occurred. Please click here to notify support and provide your contact information.

Upvotes: 0

Views: 2508

Answers (1)

felipechang
felipechang

Reputation: 924

Try making a simple search and use a formula to determine if you can actually use dense_rank. From what I suspect, Netsuite doesn't use SQL but the Oracle version of MySQL, which I think (not sure) is a subset of MySQL. So not all functions are available. Just use what you can find on the UI query.

Upvotes: 0

Related Questions