CODEWITHSUNDEEP
sql-serverrowid
Johnny Gunn
Johnny Gunn

Reputation: 51

Replacement for rowid in SQL Server

I have an Oracle select that I need to execute in SQL Server (the table is exported from an Oracle database to a SQL Server database). I can replace nvl with isnull and decode with case I guess, but how to deal with the rowid in this specific case?

select sum(
  nvl(
    (select sum(b.restsaldo) from reskontro.erkrysskid b
    where 1=1
    and b.fakturanr = a.fakturanr
    and b.kundenr = a.kundenr
    and b.resknr = b.resknr
    and a.rowid = decode(a.reskfunknr,31,a.rowid,b.rowid)
    and nvl(b.restsaldo,0) <> 0
    and b.krysskidid <= a.krysskidid
    and not exists (select * from reskontro.erkrysskid c
      where b.kundenr = c.kundenr
      and b.resknr = c.resknr
      and a.resklinr < c.resklinr
      and a.krysskidid < c.krysskidid
      and b.fakturanr = c.fakturanr
      and c.reskfunknr in (31,75)
      and nvl(c.attfort,-1) = -1)
      ),0
    )
) as restsaldo from reskontro.erkrysskid a 
where 1=1
and a.kundenr = 1 
and a.resknr = 1

Upvotes: 0

Views: 234

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89071

SQL Server doesn't have a ROWID pseudo column. In Oracle this is being used in the context of a self-join to determine if the two rows being joined are the same row. In SQL Server simply compare the table's key columns instead.

eg, if the table has a key on a Id column, use

and a.Id = case when a.reskfunknr = 31 then a.Id else b.Id end

Upvotes: 2

Related Questions