Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Immediate not found error when running this procedure. Please help!

Status
Not open for further replies.

vipinhcl

Programmer
Apr 21, 2010
30
US
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.

 
Replace
execute immediate @select_query
with
execute (@select_query)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top