crazyitguy
IS-IT--Management
Can someone help me convert this SQL server SP to ORACLE. I am mostly interested in the "WITH a AS" and "OVER (ORDER BY..." statements.
Thanks
Thanks
Code:
ALTER PROCEDURE [dbo].[GetAccountsSubset]
@startRowIndex int,
@maximumRows int,
@sortExpression nvarchar(100) = null
AS
WITH a AS
(
SELECT accounts.accountId, accounts.description, accounts.active,
accounts.accountNumber, accounts.accountTypeId,accountTypes.accountType,accountTypes.increaseOnDebit, ROW_NUMBER()
OVER (
ORDER BY CASE WHEN @sortExpression = 'accountId' THEN CONVERT(varchar(50), accounts.accountId)
WHEN @sortExpression = 'description' THEN accounts.description
WHEN @sortExpression = 'active' THEN CONVERT(varchar(50), accounts.active)
WHEN @sortExpression = 'accountNumber' THEN CONVERT(varchar(50), accounts.accountNumber)
WHEN @sortExpression = 'accountTypeId' THEN CONVERT(varchar(50), accounts.accountTypeId)
ELSE CONVERT(varchar(50), accountId)
END
) AS 'rowNumber'
FROM accounts,accountTypes
WHERE accounts.accountTypeId = accountTypes.accountTypeId
)
SELECT *
FROM a
WHERE rowNumber between @startRowIndex and (@startRowIndex + @maximumRows) - 1