This topic contains 3 replies, has 0 voices, and was last updated by nic kcd 6 years, 8 months ago.
-
AuthorPosts
-
nic kcd- Contributions: 0
- Level 1
- ☆
I am trying to use
RANK
DENSE_RANK
andKEEP
aggregate SQL Expressions. I have a saved search that is looking at the SO. I want to pull in the first IF created off of the SO. I know can justGROUP
the sales order andMINIMUM
on the Fulfilling Receiving transaction, but because of some alternative factors I can't do it this way.In the results of the search I am able to get the desired results. If you see the last column in the 'Results from running search' image, I use the
DENSE_RANK
function in order to rank which IF came in first. Now I want to use that formula in order to filter over the summary criteria of the search.I tried to wrap the around in a case statement like this
CASE WHEN (DENSE_RANK() OVER (PARTITION BY {tranid} ORDER BY {fulfillingtransaction} ASC)=1) THEN 1 ELSE 0 END
(see image 4). This works in the results section.Why doesn't this work when I do it in the summary criteria, or how else would I use it? Up for any combo of KEEP or DENSE_RANK that works. Thanks!
Criteria
Results — last formula
CASE WHEN (DENSE_RANK() OVER (PARTITION BY {tranid} ORDER BY {fulfillingtransaction} ASC) = 1) THEN 1 ELSE 0 END
Results from running search — Formula works, see last column
Summary Criteria — this causes it to error out and please contact NS. I tried sum as well as count. Value is equal to 1. And putting in on the regular criteria
This is a cached copy. Click here to see the original post. -
khultquist- Contributions: 0
- Level 1
- ☆
A few thoughts:
In the non-summary, check to see if there are any values with errors
Try with a smaller results set. I can't read your screenshot but you appear to have hundreds of results
Try wrapping your statement in TO_NUMBER in case it's returning text
-
nic kcd- Contributions: 0
- Level 1
- ☆
khultquist all solid suggestions but no luck
-
nic kcd- Contributions: 0
- Level 1
- ☆
Now I see. This is not possible to use the aggregate function in the criteria or results when doing a summary function over the data.
-
AuthorPosts
You must be logged in to reply to this topic.