How do I Handle Variables in Informix SQL Select statemensts?
A user wanted a stored function that loops (FOREACH) over a metadata table holding table and column names, then builds an INSERT...SELECT using those names as the actual table/column references. The SQL literally treated the variables (varptabname, varhtabname, etc.) as table names rather than substituting their values. After some back-and-forth clarifying the intent, the only advice given was that this isn't possible directly in a server function — you'd need a UDR, or in 4GL build the statement as dynamic SQL (PREPARE/EXECUTE). No worked solution or confirmation from the poster is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I want to use a table with entries called : dwh_nc_collect_avail_data
with the following columns:
ptabname,htabname,pjoinobj_id,hjoinobj_id,groupobj_id.
The values in these columns should be used to genereate the resulting table
in an availability list.
so the cursor is creating the following variables with the statement
FOREACH
SELECT ptabname,htabname,pjoinobj_id,hjoinobj_id,groupobj_id
INTO varptabname,varhtabname,varpjoinobj_id,varhjoinobj_id,vargroupobj_id
from 'metrica'.dwh_nc_collect_avail_data
.....
.....
varptabname,
varhtabname,
varpjoinobj_id,
varhjoinobj_id,
vargroupobj_id
--------------------------------
I want to use the value in these variables to execute the selection below:
insert into dwh_availability_day_result
select unique max(varptabname.day::utime::date),varptabname,
varhtabname.vargroupobj_id,
avg(varptabname.data_coverage_pc)
from varhtabname ,outer varptabname
where varptabname.day = utimetointegercast(datetoutimecast((TODAY - WANN )
::date ))
and varhtabname.VARHJOINOBJ_ID = varptabname.varpjoinobj_id
group by varhtabname.vargroupobj_id
---------------------------------------------------
This execution does not work as it takes the varptabname,varhtabname .....
as names instead of the values which are stored in these variables.
An Analogon in Unix Shells you use the $ to get what is in a variable:
example : $MYVAR='tablexyz'
what is the correct syntax for the selection so that the select statement
works:
select unique max( ????varptabname.day::utime::date),????varptabname,......
Does anyone has an idea how to handle this?
Thanx
Maximilian
-------------------------- This is the complete Sceleton of the
query --------------------------
CREATE FUNCTION dwh_xx_collect_avail_data(wann int)
RETURNING INT;--
-- max
-- Date:
-- Version 1.0
-- WANN 1 2 3 days ago we want to know the Availability
-- ERFOLG success 0 fail > 0
--
-- Allgemeine Variablen
--
DEFINE varptabname varchar(250);
DEFINE varhtabname varchar(250);
DEFINE varpjoinobj_id varchar(250);
DEFINE varhjoinobj_id varchar(250);
DEFINE vargroupobj_id varchar(250);
DEFINE rows INT;
DEFINE nrows INT;
DEFINE temprows INT;
DEFINE return_code INT8;
DEFINE err_num INT;
DEFINE block varchar(100);
DEFINE timediff INT;
DEFINE start_time INT;
DEFINE end_time INT;
DEFINE session_id varchar(255);
DEFINE system_time varchar(255);
DEFINE log_message_id varchar(255);
DEFINE isam_err integer;
DEFINE error_text varchar(255);
DEFINE err_object_id varchar(255);
DEFINE pdqpriority INT;
DEFINE explain boolean;
DEFINE p337 varchar(255);
--
-- ERROR HANDLING
--
FOREACH
SELECT ptabname,htabname,pjoinobj_id,hjoinobj_id,groupobj_id
INTO varptabname,varhtabname,varpjoinobj_id,varhjoinobj_id,vargroupobj_id
from 'metrica'.dwh_nc_collect_avail_data
--
-- Availability list
--
insert into dwh_availability_day_result
select unique max(varptabname.day::utime::date),varptabname,
varhtabname.vargroupobj_id,
avg(varptabname.data_coverage_pc)
from varhtabname ,outer varptabname
where varptabname.day = utimetointegercast(datetoutimecast((TODAY - WANN )
::date ))
and varhtabname.VARHJOINOBJ_ID = varptabname.varpjoinobj_id
group by varhtabname.vargroupobj_id;
END FOREACH;
RETURN 1;
END FUNCTION;
I'm confused
DEFINE varptabname varchar(250);
...
SELECT ptabname,htabname,pjoinobj_id,hjoinobj_id,groupobj_id
INTO varptabname
...
where varptabname.day
where is the '.day' coming from??
ahhh varptabname is both a tablename and a locally defined variable?
now I worked it out you are trying to select the name of a table in the first SQL statement and then use it as a variable to the FROM part in the second query but it isn't working its trying to actually use a table called varptabname yes?
"scottishpoet" <dryburghj@yahoo.com> wrote in message news:1121087894.508491.306430@g47g2000cwa.googlegroups.com... > now I worked it out > > you are trying to select the name of a table in the first SQL statement > and then use it as a variable to the FROM part in the second query but > it isn't working its trying to actually use a table called varptabname > > yes? > exactly that is what I want. example: ptabname htabname pjoinobj_id hjoinobj_id groupobj_id bsc_table1 nc_abc nc_def nc_ghi nc_jkl
OK, is this 4GL? an engine function? Some other programing language?
OK, is this 4GL? an engine function? Some other programing language?
if its an engine function I don't think you can do it and you'd need to write a UDR to do it if its 4GL or similar you'll need to write some dynamic SQL