Need your advice guys!
I am doing a migration of SQL queries from Oracle to Sql Server and observed a weird behavior of one of them. Each execution of the below query may give me results sorted different way and the wrong calculation of BAR_CURR_CODE column. While the same query run in Oracle always returns the same, stable results.
SELECT PROD_ID, UOM_ID, FIRST_VALUE(BAR_CURR_CODE) over(partition BY prod_id order by case when UOM_ID in ('IT') then 1 when UOM_ID in ('MP') then 2 when UOM_ID in ('BP') then 3 when UOM_ID in ('CS') then 4 when UOM_ID in ('SW') then 5 else 99 end ) AS BAR_CURR_CODE , BAR_CURR_CODE AS org_BAR_CURR_CODE FROM [BSR_STG_Q].[Cdl_Prod_Uom_Fct] WHERE curr_ind = 'Y' and BAR_CURR_CODE is not null and prod_Id = '00267461'
Here is the proof. First execution in SQL Server. BAR_CURR_CODE is different than in second execution.
Second execution in SQL Server. Made a couple of seconds after first one. Data were not updated during this time.
Here is the example of the same query from Oracle. The result is always the same with each execution
I do not understand why the behaviour is different in SQL Server and Oracle and how can I fix it.