Row Number Error in MV Spark SQL

[nikhil_cr]

Hi,

I’m trying to generate a Row Number using the query below, but it seems that Incorta is not returning the correct results. I checked the same query in another SQL editor, and the results were correct there. It seems either I'm missing something or there might be a bug in Incorta. 

I have attached the Sample file below, Appreciate if you can help me with this. 

SQL Query : Select *, row_number() over( partition by company, Supplier_code_JOIN, Item_code, Warehouse_code order by ( case when Outstanding_Quantity = ExcessPOSTPONE then 1 when Outstanding_Quantity < ExcessPOSTPONE then 2 else 3 end, Outstanding_Quantity desc, cast(order_date as date) desc ) ) as RowNum from Table

[JoeM]

Is it possible you have the incorrect data type for orderdate? Perhaps it's ordering orderdate as a string descending when you expect it to be ordering based on a date datatype?

[nikhil_cr]

I thought the same initially, so i casted the orderdate to date and doubled checked the data type in the table but still i did not get the desired results.

Thanks for the response. 

[JoeM]

@nikhil_cr - That was the only possibility I could think could be the case. It might be worth opening a ticket with support since it might require a dive into the engine. 

Joe

[amit_kothari]

How are you running this sql? Is it via MV or a postgres sqli connection?