Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    23

    Unanswered: convert sql server stored procedure to oracle

    Hi,

    Can anyone help me convert this sql server stored procedure to oracle?

    Thanks.

    Code:
    CREATE PROCEDURE TransferSummary
    	@startDate datetime,
    	@endDate datetime
    AS
    SELECT  'Payment Range' = 
    	CASE
    		When cast(BlFundsTransfer.amount as int) <= 2500 Then '1/ $0 -> $25.00'
    		When cast(BlFundsTransfer.amount as int) <= 5000 Then '2/ $25.01 -> $50.00'
    		When cast(BlFundsTransfer.amount as int) <= 10000 Then '3/ $50.01 -> $100.00'
    		when cast(BlFundsTransfer.amount as int) <= 50000 Then '4/ $100.01 -> $500.00'
    		When cast(BlFundsTransfer.amount as int) >= 50001 Then '5/ > $500.00'
    		Else 'Gap in range'
    	End,
    	BlFundsTransfer.channel, YEAR(BlFundsTransfer.postedDate) AS TranYear, MONTH(BlFundsTransfer.postedDate) AS TranMonth, pstatus, SUM(amount * 1) AS SumAmount, COUNT(BlFundsTransfer.id) AS CountOfid
    FROM         BlFundsTransfer
    WHERE     (BlFundsTransfer.postedDate >= @startDate) AND (BlFundsTransfer.postedDate <= @endDate)
    	AND (BlFundsTransfer.pstatus <> 'x')
    GROUP BY BlFundsTransfer.channel, YEAR(BlFundsTransfer.postedDate), MONTH(BlFundsTransfer.postedDate), BlFundsTransfer.pstatus,
    	CASE
    		When cast(BlFundsTransfer.amount as int) <= 2500 Then '1/ $0 -> $25.00'
    		When cast(BlFundsTransfer.amount as int) <= 5000 Then '2/ $25.01 -> $50.00'
    		When cast(BlFundsTransfer.amount as int) <= 10000 Then '3/ $50.01 -> $100.00'
    		when cast(BlFundsTransfer.amount as int) <= 50000 Then '4/ $100.01 -> $500.00'
    		When cast(BlFundsTransfer.amount as int) >= 50001 Then '5/ > $500.00'
    		Else 'Gap in range'
    	End
    ORDER BY BlFundsTransfer.channel, YEAR(BlFundsTransfer.postedDate), MONTH(BlFundsTransfer.postedDate), BlFundsTransfer.pstatus
    GO

  2. #2
    Join Date
    May 2003
    Location
    France
    Posts
    112
    Hi, you can try to use SwissSQL to convert T-SQL to PL/SQL; it's a shareware but limitation is 2k programs.

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Your sql looks ok bar the sql server specific functions like YEAR() but you'll need to use a ref cursor or pipelined function to return the result set to the calling app.

    Alan

  4. #4
    Join Date
    Nov 2003
    Posts
    23
    Thanks for the tip on SwissSQL, I'll try it out.

    I've done most of the conversion:

    Code:
    CREATE OR REPLACE PROCEDURE TransferSummary AS
    	startDate date;
    	endDate date;
    BEGIN
    SELECT  
    		(CASE
    		When to_number(BlFundsTransfer.amount) <= 2500 Then '1/ $0 -> $25.00'
    		When to_number(BlFundsTransfer.amount) <= 5000 Then '2/ $25.01 -> $50.00'
    		When to_number(BlFundsTransfer.amount) <= 10000 Then '3/ $50.01 -> $100.00'
    		when to_number(BlFundsTransfer.amount) <= 50000 Then '4/ $100.01 -> $500.00'
    		When to_number(BlFundsTransfer.amount) >= 50001 Then '5/ > $500.00'
    		Else 'Gap in range' 
    		END) as "Payment Range",
    	BlFundsTransfer.channel, to_char(BlFundsTransfer.postedDate, 'YYYY') AS TranYear, to_char(BlFundsTransfer.postedDate, 'mm') AS TranMonth, pstatus, SUM(amount * 1) AS SumAmount, COUNT(BlFundsTransfer.id) AS CountOfid
    FROM         BlFundsTransfer
    WHERE     (BlFundsTransfer.postedDate >= @startDate) AND (BlFundsTransfer.postedDate <= @endDate)
    	AND (BlFundsTransfer.pstatus <> 'x')
    GROUP BY BlFundsTransfer.channel, to_char(BlFundsTransfer.postedDate, 'YYYY'), to_char(BlFundsTransfer.postedDate, 'mm'), BlFundsTransfer.pstatus,
    	CASE
    		When to_number(BlFundsTransfer.amount) <= 2500 Then '1/ $0 -> $25.00'
    		When to_number(BlFundsTransfer.amount) <= 5000 Then '2/ $25.01 -> $50.00'
    		When to_number(BlFundsTransfer.amount) <= 10000 Then '3/ $50.01 -> $100.00'
    		when to_number(BlFundsTransfer.amount) <= 50000 Then '4/ $100.01 -> $500.00'
    		When to_number(BlFundsTransfer.amount) >= 50001 Then '5/ > $500.00'
    		Else 'Gap in range'
    	END
    ORDER BY BlFundsTransfer.channel, to_char(BlFundsTransfer.postedDate, 'YYYY'), to_char(BlFundsTransfer.postedDate, 'mm'), BlFundsTransfer.pstatus;
    END;
    /
    But I get the following error in SQL*Plus, have I defined the variables correctly?

    Code:
    SQL> CREATE OR REPLACE PROCEDURE TransferSummary AS
      2   startDate date;
      3   endDate date;
      4  BEGIN
      5  SELECT  
      6    (CASE
      7    When to_number(BlFundsTransfer.amount) <= 2500 Then '1/ $0 -> $25.00'
      8    When to_number(BlFundsTransfer.amount) <= 5000 Then '2/ $25.01 -> $50.00'
      9    When to_number(BlFundsTransfer.amount) <= 10000 Then '3/ $50.01 -> $100.00'
     10    when to_number(BlFundsTransfer.amount) <= 50000 Then '4/ $100.01 -> $500.00'
     11    When to_number(BlFundsTransfer.amount) >= 50001 Then '5/ > $500.00'
     12    Else 'Gap in range' 
     13    END) as "Payment Range",
     14   BlFundsTransfer.channel, to_char(BlFundsTransfer.postedDate, 'YYYY') AS TranYear, to_char(BlFu
    ndsTransfer.postedDate, 'mm') AS TranMonth, pstatus, SUM(amount * 1) AS SumAmount, COUNT(BlFundsTran
    sfer.id) AS CountOfid
     15  FROM         BlFundsTransfer
     16  WHERE     (BlFundsTransfer.postedDate >= @startDate) AND (BlFundsTransfer.postedDate <= @endDat
    e)
     17   AND (BlFundsTransfer.pstatus <> 'x')
     18  GROUP BY BlFundsTransfer.channel, to_char(BlFundsTransfer.postedDate, 'YYYY'), to_char(BlFundsT
    ransfer.postedDate, 'mm'), BlFundsTransfer.pstatus,
     19   CASE
     20    When to_number(BlFundsTransfer.amount) <= 2500 Then '1/ $0 -> $25.00'
     21    When to_number(BlFundsTransfer.amount) <= 5000 Then '2/ $25.01 -> $50.00'
     22    When to_number(BlFundsTransfer.amount) <= 10000 Then '3/ $50.01 -> $100.00'
     23    when to_number(BlFundsTransfer.amount) <= 50000 Then '4/ $100.01 -> $500.00'
     24    When to_number(BlFundsTransfer.amount) >= 50001 Then '5/ > $500.00'
     25    Else 'Gap in range'
     26   END
     27  ORDER BY BlFundsTransfer.channel, to_char(BlFundsTransfer.postedDate, 'YYYY'), to_char(BlFundsT
    ransfer.postedDate, 'mm'), BlFundsTransfer.pstatus;
     28  END;
     29  /
    
    Warning: Procedure created with compilation errors.
    
    SQL> show errors
    Errors for PROCEDURE TRANSFERSUMMARY:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    6/4      PLS-00103: Encountered the symbol "CASE" when expecting one of
             the following:
             ( - + mod not null others <an identifier>
             <a double-quoted delimited-identifier> <a bind variable> avg
             count current exists max min prior sql stddev sum variance
             execute forall time timestamp interval date
             <a string literal with character set specification>
             <a number> <a single-quoted SQL string>

  5. #5
    Join Date
    Nov 2003
    Posts
    23
    I used SwisSQL to covert the procedure, the sql part runs OK in sql*plus, but if I try to compile the procedure I get an error complaining about the "CASE" expression, is CASE not allowed in a procedure?

    PLS-00103: Encountered the symbol "CASE" when expecting one of
    the following:
    ( * - + all mod null <an identifier>
    <a double-quoted delimited-identifier> <a bind variable>
    table avg count current distinct max min prior sql stddev sum
    unique variance execute the forall time timestamp interval
    date <a string literal with character set specification>
    <a number> <a single-quoted SQL string>

    Code:
    SQL>  /* Stored Procedure to extract Funds Transfer summary data */ 
    SQL> /* extract 12+ months summary data from BlFundsTransfer table */ 
    SQL> /* Start and end dates are parameters */ 
    SQL> CREATE OR REPLACE PROCEDURE TransferSummary
      2  (
      3   startDate                  IN       DATE DEFAULT NULL,
      4   endDate                    IN       DATE DEFAULT NULL,
      5   RCT1                        IN  OUT  GLOBALPKG.RCT1
      6  )
      7  AS
      8  BEGIN 
      9   
     10   OPEN RCT1 FOR 
     11   SELECT
     12      
     13      CASE
     14        When CAST(BlFundsTransfer.amount as int)  <= 2500 Then '1/ $0 -> $25.00'
     15        When CAST(BlFundsTransfer.amount as int)  <= 5000 Then '2/ $25.01 -> $50.00'
     16        When CAST(BlFundsTransfer.amount as int)  <= 10000 Then '3/ $50.01 -> $100.00'
     17        when CAST(BlFundsTransfer.amount as int)  <= 50000 Then '4/ $100.01 -> $500.00
     18        When CAST(BlFundsTransfer.amount as int)  >= 50001 Then '5/ > $500.00'
     19        Else 'Gap in range'
     20       End "Payment Range",
     21      BlFundsTransfer.channel,
     22      TO_NUMBER(TO_CHAR(BlFundsTransfer.postedDate, 'YYYY')) AS TranYear,
     23      TO_NUMBER(TO_CHAR(BlFundsTransfer.postedDate, 'MM')) AS TranMonth,
     24      pstatus,
     25      SUM(amount * 1) AS SumAmount,
     26      COUNT(BlFundsTransfer.id) AS CountOfid
     27   FROM  BlFundsTransfer 
     28   WHERE  (BlFundsTransfer.postedDate  >= TransferSummary.startDate)
     29    AND (BlFundsTransfer.postedDate  <= TransferSummary.endDate)
     30    AND (BlFundsTransfer.pstatus  <> 'x')
     31   GROUP BY BlFundsTransfer.channel,
     32     TO_NUMBER(TO_CHAR(BlFundsTransfer.postedDate, 'YYYY')),
     33     TO_NUMBER(TO_CHAR(BlFundsTransfer.postedDate, 'MM')),
     34     BlFundsTransfer.pstatus,
     35      
     36     CASE
     37       When CAST(BlFundsTransfer.amount as int)  <= 2500 Then '1/ $0 -> $25.00'
     38       When CAST(BlFundsTransfer.amount as int)  <= 5000 Then '2/ $25.01 -> $50.00'
     39       When CAST(BlFundsTransfer.amount as int)  <= 10000 Then '3/ $50.01 -> $100.00'
     40       when CAST(BlFundsTransfer.amount as int)  <= 50000 Then '4/ $100.01 -> $500.00'
     41       When CAST(BlFundsTransfer.amount as int)  >= 50001 Then '5/ > $500.00'
     42       Else 'Gap in range'
     43      End 
     44   ORDER BY BlFundsTransfer.channel,
     45     TO_NUMBER(TO_CHAR(BlFundsTransfer.postedDate, 'YYYY')),
     46     TO_NUMBER(TO_CHAR(BlFundsTransfer.postedDate, 'MM')),
     47     BlFundsTransfer.pstatus;
     48  END;
     49  /
    
    Warning: Procedure created with compilation errors.
    
    SQL> show errors
    Errors for PROCEDURE TRANSFERSUMMARY:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    13/5     PLS-00103: Encountered the symbol "CASE" when expecting one of
             the following:
             ( * - + all mod null <an identifier>
             <a double-quoted delimited-identifier> <a bind variable>
             table avg count current distinct max min prior sql stddev sum
             unique variance execute the forall time timestamp interval
             date <a string literal with character set specification>
             <a number> <a single-quoted SQL string>

  6. #6
    Join Date
    Jun 2009
    Posts
    1
    I have a same issue...
    Can anybody help me with Case into Stored procedure?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •