topic Re: Row Number Error in MV Spark SQL in Data & Schema Discussions

Row Number Error in MV Spark SQL

nikhil_cr — Mon, 02 Dec 2024 09:49:10 GMT

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

Re: Row Number Error in MV Spark SQL

JoeM — Wed, 04 Dec 2024 19:05:31 GMT

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?

Re: Row Number Error in MV Spark SQL

nikhil_cr — Thu, 05 Dec 2024 07:48:42 GMT

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.

Re: Row Number Error in MV Spark SQL

JoeM — Fri, 06 Dec 2024 19:28:29 GMT

@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

Re: Row Number Error in MV Spark SQL

amit_kothari — Mon, 09 Dec 2024 22:00:19 GMT

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

Re: Row Number Error in MV Spark SQL

nikhil_cr — Mon, 13 Jan 2025 13:27:43 GMT

@amit_kothari : Its via MV.