Converter Procedure SQL Server para Oracle

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
FernandoFonseca
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 3
Registrado em: Ter, 18 Abr 2006 8:42 am
Localização: Brasilia - DF

Preciso converte o codigo abaixo para PL/SQl, ele esta em T-SQL do SQL Server funciona que é uma maravilha, é uma procedure generica para montar estatistica, tipo cubo de decisao.
Se alguém puder ajudar.

Selecionar tudo

set ANSI_NULLS ON 
set QUOTED_IDENTIFIER ON 
GO 
ALTER PROCEDURE [dbo].[spDynamicCrossTab] 

@RowValue VARCHAR(255), --what is the SQL for the row title 
@ColValue VARCHAR(255), --what is the SQL for the column title 
@Aggregate VARCHAR(255), --the aggregation value to go in the cells 
@FromExpression VARCHAR(8000), --the FROM, ON and WHERE clause 
@colOrderValue VARCHAR (255)=NULL, --how the columns are ordered 
@Title VARCHAR(80)='_', --the title to put in the first col of first row 
@SortBy VARCHAR(255)='row asc', --what you sort the rows by (column heading) 
@RowSort VARCHAR(80)=NULL, 
@ReturnTheDDL INT=0,--return the SQL code rather than execute it 
@Debugging INT=0 --debugging mode 
/* 
e.g. 
Execute spDynamicCrossTab 
@RowValue='firstname+'' ''+lastname', 
@ColValue='Year(OrderDate)', 
@Aggregate= 'count(*)', 
@FromExpression='FROM Employees INNER JOIN Orders 
ON (Employees.EmployeeID=Orders.EmployeeID)', 
@ColOrderValue='Year(OrderDate)', 
@Title ='No. Sales per year', 
@SortBy ='total desc' --what you sort the rows by (column heading) 

Execute spDynamicCrossTab 
@RowValue='firstname+'' ''+lastname', 
@ColValue='DATENAME(month,orderDate)', 
@Aggregate= 'sum(subtotal)', 
@FromExpression='FROM Orders 
INNER JOIN "Order Subtotals" 
ON Orders.OrderID = "Order Subtotals".OrderID 
inner join employees on employees.EmployeeID =orders.EmployeeID', 
@ColOrderValue='datepart(month,orderDate)', 
@Title ='Customers orders per month ' 

EXECUTE spDynamicCrossTab 
@RowValue='country', 
@ColValue='datename(quarter,orderdate) 
+case datepart(quarter,orderdate) 
when 1 then ''st'' 
when 2 then ''nd'' 
when 3 then ''rd'' 
when 4 then ''th'' end', 
@Aggregate= 'sum(subtotal)', 
@FromExpression='FROM Orders 
INNER JOIN "Order Subtotals" 
ON Orders.OrderID = "Order Subtotals".OrderID 
inner join customers on customers.customerID =orders.customerID', 
@ColOrderValue='datepart(quarter,orderDate)', 
@sortby='total desc', 
@Title ='value of orders per quarter' 

*/ 
AS 
SET nocount ON 
DECLARE @Command NVARCHAR(MAX) 
DECLARE @SQL VARCHAR(MAX) 
DECLARE @SQL1 VARCHAR(MAX) 

--make sure we have sensible defaults for orders 
SELECT @ColOrderValue=COALESCE(@ColOrderValue, @ColValue), 
@Sortby=COALESCE(@SortBy,@RowValue), 
@rowsort=COALESCE(@RowSort,@RowValue) 



--first construct tha SQL which is used to calculate the columns in a 
--string 
SELECT @Command='select @SQL=coalesce(@SQL,''SELECT 
['+@Title+']=case when row is null then ''''Sum'''' 
else convert(Varchar(80),[row]) end , 
'')+ 
''[''+convert(varchar(100),' 
+@ColValue+')+''] =sum( CASE col WHEN ''''''+convert(varchar(100),' 
+@ColValue+')+'''''' THEN data else 0 END ), 
'' '+@FromExpression+' 
GROUP BY '+@ColValue+' 
order by max('+@ColorderValue+')' 




--Now we execute the string to obtain the SQL that we will use for the 
--crosstab query 
EXECUTE sp_ExecuteSQL @command,N'@SQL VARCHAR(MAX) OUTPUT',@SQL OUTPUT 
IF @@error > 0 --display the string if there is an error 
BEGIN 
RAISERROR ( 'offending code was ...%s', 0, 1, @command ) 
RETURN 1 
END 
IF @debugging <>0 SELECT @Command 
--we now add the rest of the SQL into the string 



SELECT @SQL=@SQL+' [Total]= sum( data ) 
from 
(select [row]='+@RowValue+', 
[col]='+@ColValue+', 
[data]='+@Aggregate+', 
[sort]=max('+@rowsort+') 
'+@FromExpression+' 
GROUP BY '+@RowValue+', '+@ColValue+' 
)f 
group by row with rollup 
order by grouping(row),'+@Sortby 

--and execute it 
IF @ReturnTheDDL<>0 SELECT @SQL ELSE EXECUTE (@SQL) 
IF @@error > 0 
BEGIN 
RAISERROR ( 'offending code was ...%s', 0, 1, @sql ) 
RETURN 1 
END 
A parte negritada é o principal problema
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Trancado
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 8 visitantes