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.
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
Converter Procedure SQL Server para Oracle
-
- Rank: Estagiário Pleno
- Mensagens: 3
- Registrado em: Ter, 18 Abr 2006 8:42 am
- Localização: Brasilia - DF
-
- Rank: Estagiário Pleno
- Mensagens: 3
- Registrado em: Ter, 18 Abr 2006 8:42 am
- Localização: Brasilia - DF
Bom, Feliz 2008 a todos, como ninguém consegui fazer a conversão, fiz por conta própria agora, disponibilizo o Package para que todos possam "estudar" e quem sabe, numa próxima vez ajudar...
CREATE OR REPLACE PACKAGE "Schema".PKG_ESTATISTICA_GERAL
AS
TYPE CURSOR_RESULTS IS REF CURSOR;
PROCEDURE PC_Estatistica_Geral (rowValue IN VARCHAR2 DEFAULT NULL,
ColValue IN VARCHAR2 DEFAULT NULL,
sAggregate IN VARCHAR2 DEFAULT NULL,
FromExpression IN VARCHAR2 DEFAULT NULL,
colOrderValue IN VARCHAR2 DEFAULT null,
Title IN VARCHAR2 DEFAULT '_',
SortBy IN VARCHAR2 DEFAULT 'arow asc',
RowSort IN VARCHAR2 DEFAULT null,
O_Results OUT CURSOR_RESULTS
);
END PKG_ESTATISTICA_GERAL;
CREATE OR REPLACE PACKAGE BODY "Schema".PKG_ESTATISTICA_GERAL
AS
PROCEDURE PC_Estatistica_Geral (rowValue IN VARCHAR2 DEFAULT NULL,
ColValue IN VARCHAR2 DEFAULT NULL,
sAggregate IN VARCHAR2 DEFAULT NULL,
FromExpression IN VARCHAR2 DEFAULT NULL,
colOrderValue IN VARCHAR2 DEFAULT null,
Title IN VARCHAR2 DEFAULT '_',
SortBy IN VARCHAR2 DEFAULT 'arow asc',
RowSort IN VARCHAR2 DEFAULT null,
O_Results OUT CURSOR_RESULTS
) IS
SQL_ADV VARCHAR2(4000);
Command VARCHAR2(4000);
cur_handle INTEGER;
texto VARCHAR2(80);
temp NUMBER;
icursor sys_refcursor;
BEGIN
open icursor for 'SELECT trim(' || ColValue ||') as pivot '||FromExpression ||' group by '||Colvalue||' order by max('||NVL(colOrderValue,ColValue)||')';
LOOP
FETCH icursor INTO texto;
EXIT WHEN icursor%NOTFOUND;
Command:=Command||'sum(case trim(acol) when '''||texto||''' then adata else 0 end) as "'||texto||'",';
END LOOP;
CLOSE icursor;
SQL_ADV:=NVL(SQL_ADV,'SELECT case when arow is null then ''Total'' else to_char(arow) end as "'||Title||'" ,'||Command);
SQL_ADV:=SQL_ADV||' sum( adata ) as Total
from (select '||RowValue||' as arow,'
||ColValue||' as acol,'
||sAggregate||'as adata,
max('||NVL(RowSort, RowValue)||') as asort '||FromExpression||' GROUP BY '||RowValue||', '||ColValue||' )f
group by rollup(arow) order by grouping(arow),'||NVL(SortBy, RowValue);
open O_Results for SQL_ADV;
--close O_Results;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, 'Unexpected error:' || sqlerrm);
END PC_Estatistica_Geral;
END PKG_ESTATISTICA_GERAL;
-
- Informação
-
Quem está online
Usuários navegando neste fórum: Nenhum usuário registrado e 4 visitantes