What to do when error 'Ordering by expressions of type STRUCT is not allowed' and ordering is in qualify row number over() statement?

I encountered a problem and since I'm not incredibly advanced in SQL, I fail to solve it. I wanted to use for ordering two conditions, first one dependent on the status, then dependent on the type. Since the whole code already existed and only the priorisation depending on the status changed, I only added the prioritisation depending on the status and thought it will work then. But it doesn't. :(

This is the CTE:

registrations AS 
( 
SELECT * 
FROM database 
QUALIFY ROW_NUMBER() OVER 
( PARTITION BY id 
ORDER BY ( 
CASE WHEN status = 'active' THEN 0 
WHEN status IN ('received', 'submitted') THEN 1 
WHEN status_ IN ('cancelled', 'rejected') THEN 2 
ELSE 3 
END, 
CASE WHEN type = 'xy' THEN 0 
WHEN type = 'yx' THEN 1 
ELSE 2 
END ), 
registration_date DESC ) = 1 )

The error I encounter is the following:

Ordering by expressions of type STRUCT is not allowed.

I truly don't know what to do. I see that the problem is due to my having two case when statements in the order by clause, but I fail to find a solution which doesn't screw the whole logic since the order by is in the qualify row_number over () clause. I would be immensely grateful if someone knows a solution for this kind of error.

Solution

You are ordering by (case, case), registration_date desc. The (case, case) is being interpreted as a struct. Try removing the set of parentheses around the case statements.