0

Am working on upgradation of Sybase 15.7 from 12.5 where I encountered a peculiar problem.Below query

select  rs.EmpId,   rs.Date, rs.Currency, rs.Salary,   
  from  #Results rs, #EmpSort es
  where rs.EmpId = es.EmpId  
        order by     es.EmpCode, rs.Currency

while executing, result is grouped as currency and sorted in according to salary desc in Sybase 15.7, where as in Sybase 12.5 result is grouped as currency and sorted according to date asc. Am not sure why this happening.

Mike Gardner
  • 6,611
  • 5
  • 24
  • 34
WENzER
  • 205
  • 1
  • 5
  • 15
  • Makes no sense do you mean grouped by empcode, then currency and then sorted by date. If so all that's happened is the query plan has changed. If you want order by date, then you should put that in your order by. Nothing to do with upgrade as such, could have happened anytime you changed the schema. – Tony Hopkinson May 17 '14 at 13:19
  • Ok, As per Sybase 15.7 recommendations where ever we are using group by it should be followed order by. So I have changed my existing store Proc(12.5) accordingly. And after changing all code, last piece of code is above query which should work as 12.5, which is not working. In rest code I have used group by for date and currency followed by order by. – WENzER May 17 '14 at 17:50
  • Huh? Are you talking about some other query? There's no group by in this one and it has never has never and never will order by date. At best the data was already ordered by date. If the above was ordering by date in 12.5, 15.6, or even 8.75678 that's because you were lucky. – Tony Hopkinson May 17 '14 at 18:02
  • In above query temp table #results is final table and in which data inserted from existing table and grouped by date and currency followed by order by date currency – WENzER May 17 '14 at 18:13
  • 1
    The query you posted never ordered by date, so either you have posted the wrong query, or you are not listening to what I'm saying. The data just happened to be in date order when it was selected for inserting. You cannot rely on that remaining true, ever. Only order by date or perhaps group by date will guarantee it being in date order. – Tony Hopkinson May 17 '14 at 18:20

1 Answers1

0

I detected the following issues

1.- Remove the last comma from the first line

select  rs.EmpId,   rs.Date, rs.Currency, rs.Salary  (,)

2.- You are ordering by es.EmpCode but the the fields that you are listing are 

 rs.EmpId,   rs.Date, rs.Currency, rs.Salary,

add es.EmpCode to the fields you are listing.

jcromanu
  • 1,171
  • 2
  • 13
  • 31