Hi Sybase guys,
Please solve my problem. I am creating a procedure where using a dynamic select query. Procedure is like this
-----------------------------
Create or Replace Procedure Test_proc3(
@Zip_Code varchar(12),
@CollectiondateFROM varchar(12),
@CollectiondateTo varchar(12),
@RatePlan varchar(10),
@avgFlag integer,
@ProductType varchar(10),
@drms varchar(10),
@incomeRange integer ) AS
BEGIN
DECLARE @MaxActualUsage real
DECLARE @MaxNormalUsage real
DECLARE @MaxPriceInCent real
DECLARE @MinActualUsage real
DECLARE @MinNormalUsage real
DECLARE @MinPriceInCent real
DECLARE @select_query varchar(5000)
DECLARE @where_clause varchar(5000)
DECLARE @group_select varchar(1000)
DECLARE @newrange varchar(5)
DROP TABLE DBA.tempData4max
BEGIN
set @newrange = @incomeRange
IF @avgFlag = 0
--SUM
set @select_query = 'SELECT UsageDate="date"(end_date),Hour=datepart(hh,end_Date),minutes=datepart(mi,end_Date),ActualUsage=sum(Usage),NormalUsage=Sum(Normal_usage),PriceInCent=SUM(Usage*case when mcpe < 0 then mcpe*-1 else Mcpe end/10),meterCount=COUNT(distinct(meter_id)),P_OR_OP=case when(datepart(hh,end_Date) >=1 and datepart(hh,end_Date) <= 8) then ''P'' when(datepart(hh,end_Date) = 23 and datepart(mi,end_Date) = 59) then ''P'' else ''OP'' end,MaxActualUsage=9999.99,MaxNormalUsage=9999.999,MaxPriceInCent=999999999999999.99,MinActualUsage=9999.99,MinNormalUsage=9999.999,MinPriceInCent=99999.99 into tempData4max FROM MDMDataForAnalyticsold where end_date >= convert(datetime,'''+@CollectiondateFROM+''',105) and end_date <= convert(datetime,'''+@CollectiondateTo+''',105) and @drms = case when '''+@drms+''' = ''YES'' then ''YES'' when '''+@drms+'''=''NO'' then ''NO'' else drms end and IncomeRange_id = case when '+@newrange+' is null then IncomeRange_id else '+@newrange +' end'
Else
-- AVERAGE
set @select_query = 'SELECT UsageDate="date"(end_date),Hour=datepart (hh,end_Date),minutes=datepart(mi,end_Date),ActualUsage=AVG(Usage),NormalUsage=AVG(Normal_usage),PriceInCent=AVG(Usage*case when mcpe < 0 then mcpe*-1 else Mcpe end/10),meterCount=COUNT(distinct(meter_id)),P_OR_OP=case when(datepart(hh,end_Date) >=1 and datepart(hh,end_Date) <= 8) then ''P'' when(datepart(hh,end_Date) = 23 and datepart(mi,end_Date) = 59) then ''P'' else ''OP'' end,MaxActualUsage=9999.99,MaxNormalUsage=9999.999,MaxPriceInCent=999999999999999.99,MinActualUsage=9999.99,MinNormalUsage=9999.999,MinPriceInCent=99999.99 into tempData4max FROM MDMDataForAnalyticsold where end_date >= convert(datetime,'''+@CollectiondateFROM+''',105) and end_date <= convert(datetime,'''+@CollectiondateTo+''',105) and @drms = case when '''+@drms+''' = ''YES'' then ''YES'' when '''+@drms+''' = ''NO'' then ''NO'' else drms end and IncomeRange_id = case when '+@newrange+' is null then IncomeRange_id else '+@newrange +' end'
IF @ProductType IS NOT NULL
set @where_clause = @where_clause + ' and Retail_Product = ''' + @ProductType +''''
IF @Zip_Code IS NOT NULL
set @where_clause = @where_clause + ' and "zip code" = case when '''+@Zip_Code+''' <> ''ALL'' then '''+@Zip_Code+''' else "zip code" end'
set @group_select = ' group by "zip code",end_date order by end_date asc'
IF @RatePlan IS NOT NULL
set @where_clause = @where_clause + ' and "T&D Rate" = '''+ @RatePlan +''''
set @group_select = ' group by "T&D Rate",end_date order by end_date asc'
--MAKING RESULTANT QUERY
set @select_query = @select_query + @where_clause + @group_select
-- RUN THE QUERY
execute immediate @select_query
--with quotes off
END
SELECT
@MaxActualUsage = Max(ActualUsage),
@MaxNormalUsage = MAX(NormalUsage),
@MaxPriceInCent = SUM(PriceInCent),
@MinActualUsage = Min(ActualUsage),
@MinNormalUsage = Min(NormalUsage),
@MinPriceInCent = Min(PriceInCent)
FROM tempData4max
UPDATE tempData4max
SET
MaxActualUsage = @MaxActualUsage,
MaxNormalUsage = @MaxNormalUsage,
MaxPriceInCent = @MaxPriceInCent,
MinActualUsage = @MinActualUsage,
MinNormalUsage = @MinNormalUsage,
MinPriceInCent = @MinPriceInCent
select * FROM tempData4max
DROP TABLE DBA.tempData4max
END
-----------------------------------------
It is compiling successfully. but when I run this using
execute Test_Proc3 'ALL','10-11-2009','15-11-2009','R1-04',0,NULL,'NO',2
It is saying immediate not found.
I tried using with result set on but then it is saying error near result. This dynamic select statement is preparing correctly as I have made the script also which returns only select statement as string. when I executed that select statement, it is running fine.
Please help me what is going wrong with execute immediate statement.
Please solve my problem. I am creating a procedure where using a dynamic select query. Procedure is like this
-----------------------------
Create or Replace Procedure Test_proc3(
@Zip_Code varchar(12),
@CollectiondateFROM varchar(12),
@CollectiondateTo varchar(12),
@RatePlan varchar(10),
@avgFlag integer,
@ProductType varchar(10),
@drms varchar(10),
@incomeRange integer ) AS
BEGIN
DECLARE @MaxActualUsage real
DECLARE @MaxNormalUsage real
DECLARE @MaxPriceInCent real
DECLARE @MinActualUsage real
DECLARE @MinNormalUsage real
DECLARE @MinPriceInCent real
DECLARE @select_query varchar(5000)
DECLARE @where_clause varchar(5000)
DECLARE @group_select varchar(1000)
DECLARE @newrange varchar(5)
DROP TABLE DBA.tempData4max
BEGIN
set @newrange = @incomeRange
IF @avgFlag = 0
--SUM
set @select_query = 'SELECT UsageDate="date"(end_date),Hour=datepart(hh,end_Date),minutes=datepart(mi,end_Date),ActualUsage=sum(Usage),NormalUsage=Sum(Normal_usage),PriceInCent=SUM(Usage*case when mcpe < 0 then mcpe*-1 else Mcpe end/10),meterCount=COUNT(distinct(meter_id)),P_OR_OP=case when(datepart(hh,end_Date) >=1 and datepart(hh,end_Date) <= 8) then ''P'' when(datepart(hh,end_Date) = 23 and datepart(mi,end_Date) = 59) then ''P'' else ''OP'' end,MaxActualUsage=9999.99,MaxNormalUsage=9999.999,MaxPriceInCent=999999999999999.99,MinActualUsage=9999.99,MinNormalUsage=9999.999,MinPriceInCent=99999.99 into tempData4max FROM MDMDataForAnalyticsold where end_date >= convert(datetime,'''+@CollectiondateFROM+''',105) and end_date <= convert(datetime,'''+@CollectiondateTo+''',105) and @drms = case when '''+@drms+''' = ''YES'' then ''YES'' when '''+@drms+'''=''NO'' then ''NO'' else drms end and IncomeRange_id = case when '+@newrange+' is null then IncomeRange_id else '+@newrange +' end'
Else
-- AVERAGE
set @select_query = 'SELECT UsageDate="date"(end_date),Hour=datepart (hh,end_Date),minutes=datepart(mi,end_Date),ActualUsage=AVG(Usage),NormalUsage=AVG(Normal_usage),PriceInCent=AVG(Usage*case when mcpe < 0 then mcpe*-1 else Mcpe end/10),meterCount=COUNT(distinct(meter_id)),P_OR_OP=case when(datepart(hh,end_Date) >=1 and datepart(hh,end_Date) <= 8) then ''P'' when(datepart(hh,end_Date) = 23 and datepart(mi,end_Date) = 59) then ''P'' else ''OP'' end,MaxActualUsage=9999.99,MaxNormalUsage=9999.999,MaxPriceInCent=999999999999999.99,MinActualUsage=9999.99,MinNormalUsage=9999.999,MinPriceInCent=99999.99 into tempData4max FROM MDMDataForAnalyticsold where end_date >= convert(datetime,'''+@CollectiondateFROM+''',105) and end_date <= convert(datetime,'''+@CollectiondateTo+''',105) and @drms = case when '''+@drms+''' = ''YES'' then ''YES'' when '''+@drms+''' = ''NO'' then ''NO'' else drms end and IncomeRange_id = case when '+@newrange+' is null then IncomeRange_id else '+@newrange +' end'
IF @ProductType IS NOT NULL
set @where_clause = @where_clause + ' and Retail_Product = ''' + @ProductType +''''
IF @Zip_Code IS NOT NULL
set @where_clause = @where_clause + ' and "zip code" = case when '''+@Zip_Code+''' <> ''ALL'' then '''+@Zip_Code+''' else "zip code" end'
set @group_select = ' group by "zip code",end_date order by end_date asc'
IF @RatePlan IS NOT NULL
set @where_clause = @where_clause + ' and "T&D Rate" = '''+ @RatePlan +''''
set @group_select = ' group by "T&D Rate",end_date order by end_date asc'
--MAKING RESULTANT QUERY
set @select_query = @select_query + @where_clause + @group_select
-- RUN THE QUERY
execute immediate @select_query
--with quotes off
END
SELECT
@MaxActualUsage = Max(ActualUsage),
@MaxNormalUsage = MAX(NormalUsage),
@MaxPriceInCent = SUM(PriceInCent),
@MinActualUsage = Min(ActualUsage),
@MinNormalUsage = Min(NormalUsage),
@MinPriceInCent = Min(PriceInCent)
FROM tempData4max
UPDATE tempData4max
SET
MaxActualUsage = @MaxActualUsage,
MaxNormalUsage = @MaxNormalUsage,
MaxPriceInCent = @MaxPriceInCent,
MinActualUsage = @MinActualUsage,
MinNormalUsage = @MinNormalUsage,
MinPriceInCent = @MinPriceInCent
select * FROM tempData4max
DROP TABLE DBA.tempData4max
END
-----------------------------------------
It is compiling successfully. but when I run this using
execute Test_Proc3 'ALL','10-11-2009','15-11-2009','R1-04',0,NULL,'NO',2
It is saying immediate not found.
I tried using with result set on but then it is saying error near result. This dynamic select statement is preparing correctly as I have made the script also which returns only select statement as string. when I executed that select statement, it is running fine.
Please help me what is going wrong with execute immediate statement.