英文:
Replacement for rowid in SQL Server
问题
我有一个需要在SQL Server中执行的Oracle select语句（该表从Oracle数据库导出到SQL Server数据库）。我可以用isnull替换nvl，用case替换decode，但在这种特定情况下如何处理rowid呢？
select sum(
isnull(
(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 = case when a.reskfunknr = 31 then a.rowid else b.rowid end
and isnull(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 isnull(c.attfort,-1) = -1)
),0
)
) as restsaldo from reskontro.erkrysskid a
where 1=1
and a.kundenr = 1
and a.resknr = 1
这是将nvl替换为isnull，decode替换为case的修改后的查询语句。对于rowid，我使用了一个case语句来处理，如果a.reskfunknr等于31，则使用a.rowid，否则使用b.rowid。
英文:
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
答案1
得分: 2
SQL Server没有ROWID伪列。在Oracle中，这在自连接的上下文中用于确定被连接的两行是否是_同一行_。在SQL Server中，只需比较表的关键列即可。
例如，如果表在Id列上有一个键，请使用
and a.Id = case when a.reskfunknr = 31 then a.Id else b.Id end
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论