convert this sql server SP to ORACLE SP

[crazyitguy]

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

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

 

[Welshbird]

From this really helpful website:

http://www.techonthenet.com/oracle/index.php

Case statement:

CASE expression
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result END

and as an example

select table_name,
CASE owner
WHEN 'SYS' THEN 'The owner is SYS'
WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value' END
from all_tables;

For the over (order by... are you aiming to count, rank sum?? I can't work that out from your post I'm afraid. If you can post what your eventual aim is I'll be happy to help some more.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Dagon]

With" and windowing functions are both supported from Oracle 9.2 onwards. "With" is supported as a method of factoring out subqueries but not for recursive statements.

 

[crazyitguy]

The purpose is to fill a .Net gridview with Paging and sorting enabled.

"Count, rank sum"

I do not know what that means.

" over (order by..." is just to sort the records.

Thanks

 

[Dagon]

There are lots of resources explaining analytic functions. I found this one quite useful:

http://www.orafaq.com/node/55

 

[crazyitguy]

Dagon: Are you saying that the WITH statement should work?

Here is what I have so far.. What needs to change?

Thanks

create or replace PROCEDURE GetAccountsSubset
(
     startRowIndex 		int,
     maximumRows		int,
     sortExpression     varchar2(50)
)
AS
BEGIN
     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  accounts.accountId
                 WHEN sortExpression = 'description' THEN accounts.description
                 WHEN sortExpression = 'active' THEN accounts.active
                 WHEN sortExpression = 'accountNumber' THEN accounts.accountNumber
                 WHEN sortExpression = 'accountTypeId' THEN accounts.accountTypeId
                 ELSE accountId
                 END;
                ) AS 'rowNumber'
           FROM accounts,accountTypes
           WHERE accounts.accountTypeId = accountTypes.accountTypeId;
      )
      SELECT *
      FROM a
      WHERE rowNumber between startRowIndex
      AND (startRowIndex + maximumRows) - 1;
END GetAccountsSubset;

 

[Welshbird]

CrazyItGuy:

Do you get an error message or are the results not what you expect?

(If you need to see the error then type SHOW ERRORS after the compilation fails.)

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Dagon]

You have a couple of semi-colons and quotes which Oracle doesn't like. If I remove those, the syntax works on my 9.2 system:

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  accounts.accountId
                 WHEN sortExpression = 'description' THEN accounts.description
                 WHEN sortExpression = 'active' THEN accounts.active
                 WHEN sortExpression = 'accountNumber' THEN accounts.accountNumber
                 WHEN sortExpression = 'accountTypeId' THEN accounts.accountTypeId
                 ELSE accountId
                 END
                ) AS rowNumber
           FROM accounts,accountTypes
           WHERE accounts.accountTypeId = accountTypes.accountTypeId
      )
      SELECT *
      FROM a
      WHERE rowNumber between startRowIndex
      AND (startRowIndex + maximumRows) - 1

 

[crazyitguy]

Thanks...

Here is the code so far. I had to convert each sort expr into chars.

I am now getting an error:

9/5      PLS-00428: an INTO clause is expected in this SELECT statement

create or replace PROCEDURE GetAccountsSubset
(
     startRowIndex    in     int,
     maximumRows    in    int,
     sortExpression in     varchar2
)
AS
BEGIN
    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  TO_CHAR(accounts.accountId)
                 WHEN sortExpression = 'description' THEN accounts.description
                 WHEN sortExpression = 'active' THEN TO_CHAR(accounts.active)
                 WHEN sortExpression = 'accountNumber' THEN TO_CHAR(accounts.accountNumber)
                 WHEN sortExpression = 'accountTypeId' THEN TO_CHAR(accounts.accountTypeId)
                 ELSE TO_CHAR(accountId)
                 END
                ) AS rowNumber
           FROM accounts,accountTypes
           WHERE accounts.accountTypeId = accountTypes.accountTypeId
      )
      SELECT *
      FROM a
      WHERE rowNumber between startRowIndex
      AND (startRowIndex + maximumRows) - 1;
END GetAccountsSubset;

 

[Dagon]

That is correct. PL/SQL expects you to select into a variable. The syntax is:

select x
into y
from dual;

However, this only works if you are selecting a single row. To loop through an entire result set from a table, you must use something called a cursor (I don't know if sql*server has the equivalent concept).

It might be a good idea for you to peruse the PL/SQL manuals. These can be found at:

http://download-uk.oracle.com/docs/cd/A91202_01/901_doc/nav/docindex.htm

 

[Dagon]

Those are 9i manuals but, if you hunt around there, they also have the 10g versions.