Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Posts
    12

    Unanswered: Dynamic queries SPL

    Hi everybody...

    I have an issue, i'm trying to resolve, but it doesn't seem i can´t get it.
    So, i have a table in which i put a procname and variable names that are parameters (some_spl|lv_var1,lv_var2). Inside a spl, I execute a cycle and construct a query. Here's an example:

    let lv_str = execute procedure pr_pap_gram_med_sp(lv_mill_cd,"PM4",lv_data_ini,lv _data_fim)

    This is on trace file.
    After that, I make:

    execute IMMEDIATE trim(lv_str);

    But then, I get this error:

    lv_mill_cd:Variable() not declared

    However this variable is declared.

    What do you think about this?

    Thanks

  2. #2
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Why don't you publish the stored procedure here? Please put the code between CODE-tags (the # button in the toolbar).

    Hans

  3. #3
    Join Date
    Jul 2009
    Posts
    12

    Dynamic queries SPL

    The code is the following:

    Code:
    	foreach cs_cod_var for
    		select cod_var
    		into lv_cod_var
    		from pr_rel_text
    		where mill_cd = av_mill_cd
    		and cod_rel = av_cod_rel
    
    
    		let lv_parameters, lv_procname = (select parameters, procname from get_proc
    							where mill_cd = av_mill_cd
    							and cod_rel = av_cod_rel
    							and cod_var = lv_cod_var);
    
    		let lv_str = 'execute procedure '||trim(lv_procname)||'(';
    							
    		if (lv_parameters is not null or lv_procname is not null) then
    
    			let lv_count = length(lv_parameters) - length(replace(lv_parameters,'|',''))+ 1;
    
    			FOR lv_index_var = 1 TO lv_count
    				if (lv_index_var != lv_count) then
    					let lv_str = trim(lv_str)||trim(gps_getkeyvalues_sp(lv_parameters,"|",lv_index_var)) ||',';
    				else
    					let lv_str = trim(lv_str)||trim(gps_getkeyvalues_sp(lv_parameters,"|",lv_index_var))||')';
    				end if;
    			END FOR
    						
    			execute IMMEDIATE trim(lv_str);
    		end if;
    
    		let lv_str = '';
    		
    	end foreach; -- cs_cod_var
    I choose another approach in a way I can reach it, but I still want to know if it's possible to do it that way.

    Thanks.

  4. #4
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Hi,
    But then, I get this error:
    lv_mill_cd:Variable() not declared
    However this variable is declared.
    But when I look at your code example I notice that the lv_mill_cd must be the returnvalue of your gps_getkeyvalues_sp function. I'm not familiar with a Informix stored function that can return a pointer, other than to a BLOB (or maybe an opaque type, I've no experience with that). So I guess lv_mill_cd has to be a literal and should be treated like that which means it should be quoted because it's definitely not a numeric value.

    Regards,
    Hans

Posting Permissions

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