Query Showing Different result in SQLRPGLE and STRSQL(DB2)

[Iceberg]

Hi All,
Am using Partition by in query to fetch last record from each group. but same query giving me different result when using in SQLRPGLE and STRSQL.

Query in SQLRPGLE:-

Select Count(*) into :Wk9Count from FileA where
Product = :Wk9Prod and Customer_Id1 in (
Select Customer_Id2 from(Select Customer_Id2,Customer_Status,ROW_NUMBER() OVER
(PARTITION BY Customer_Id2
ORDER BY Date_1 Desc,Date_2 Desc,Date_3 Desc)
As row_number from File2)
as testfile where ROW_NUMBER=1 and Customer_Status = 'T' )
and SubString(Customer_State,1,2) = :Wk9State and Cust_info_date = 0;

Above query giving me count = 1.

Query in STRSQl:-

Select Count(*) from FileA where
Product = 'Prod1' and Customer_Id1 in (
Select Customer_Id2 from(Select Customer_Id2,Customer_Status,ROW_NUMBER() OVER
(PARTITION BY Customer_Id2
ORDER BY Date_1 Desc,Date_2 Desc,Date_3 Desc)
As row_number from File2)
as testfile where ROW_NUMBER=1 and Customer_Status = 'T' )
and SubString(Customer_State,1,2) = 'ST' and Cust_info_date = 0

This query giving count = 2.

Please suggest why am getting different result.

Thanks

[Iceberg]

Though output of second query (in Strsql) in correct

[vazymimil]

Hi,

did you check the values of Wk9Prod and Wk9State are 'Prod1' and 'ST' ?
did you check the libraries where fileA and file2 are found are the same in both environments ?
if ORDER BY Date_1 Desc,Date_2 Desc,Date_3 Desc is not "unique" then maybe try to use a unique order

[Iceberg]

Both the values are same and even am using same session with same library list. and ORDER BY Date_1 Desc,Date_2 Desc,Date_3 Desc is not "unique" but if i check in STRSQL its giving different row number value even if Date_1 ,Date_2 ,Date_3 is not unique. Also to remove that duplicate records am using Customer_Status = 'T' i.e we cant have multiple records for single customer with same Customer_Status so we can say its unique record.

[Iceberg]

Yeah its looks like it is cause of duplicate records so both SQLRPGLE/STRSQL fetching different record if 2 records a have same Customer_Id2/Date_1 /Date_2/Date_3. So can we handle this scenario in Partition by or we have some alternative (Another query to fetch last record from a file considering duplicate records just like SETGT in case of SETGT i think it will move the cursor to latest RRN if record is duplicate)?

[Scott M]

You should be able to add the RRN() to the order by in the Partition statement, using desc to get that last one. If you are reusing deleted records then the largest RRN may not be the last record added. Or add some other field to the end of the order by so it always picks the record you want,

[B.Hauser]

Did you check you library list, may be you are accessing different tables

[vazymimil]

Both the values are same and even am using same session with same library list..

Sure the way SQL finds table is the same (NAMING=SQL/SYS, CURRENT SCHEMA) ?

and ORDER BY Date_1 Desc,Date_2 Desc,Date_3 Desc is not "unique" but if i check in STRSQL its giving different row number value even if Date_1 ,Date_2 ,Date_3 is not unique. Also to remove that duplicate records am using Customer_Status = 'T' i.e we cant have multiple records for single customer with same Customer_Status so we can say its unique record.

I made that comment about the order but thought libraries difference would be the problem, I would be surprised that same query on exact same data would give different results.
Maybe you DATFMT are different and there is a date that cannot be expressed in a format where years have only two digits.