Union clause in Informix Stored Procedure 1

[SteveRemington]

A question to the collective wisdom of the group about Informix stored procedures and union queries.

After a bit of searching and fiddling I have written a simple Informix stored procedure that returns the required record set. See skeleton of working stored procedure below:

CREATE PROCEDURE owner.procname (PerStartDate DATE, PerEndDate DATE)

RETURNING
CHAR(5),
...
FLOAT(15);

DEFINE col_1 CHAR(5);
...
DEFINE col_15 FLOAT(15);

FOREACH
SELECT
value_1,
...
value_15
INTO
col_1,
...
col_15
FROM
...
WHERE
...
AND (table.rec_date >= PerStartDate)
AND (table.rec_date <= PerEndDate)
RETURN
col_1,
...
col_15
WITH RESUME;
END FOREACH;
END PROCEDURE;

The Select statement in the SP above is only half of a Union query that is required. The union query works perfectly outside the SP with the values of PerStartDate and PerEndDate hard-coded into each part of the union query.

My question is: What changes do I have to make to the above stored procedure code in order to include the second half of the union query.

Thanks in advance to all who reply.

Regards,
Steve Remington
Wave Business Solutions
steve.remington@wavebusiness.com.au

 

[Gragi]

Steve,
I have the same kind of problem, if you find any solution can you please post the solution.

Thanks in Advance,
Gragi

 

[mberni]

Hi!

The syntax for the select-statement should be like that:

FOREACH
select field1, ..., lastfield
into var1, ..., lastvar
from sometables
where someconditions
union
select field1, ..., lastfield
from someothertables
where somemoreconditions

your code

END FOREACH;

Please notice that

- the second select has no INTO clause
- both selects get the same number and type of fields corresponding to your vars from the INTO-clause

This way you should be happy.

Have fun,

Michael

 

[Gragi]

mberni,
Thanks for the solution.

Gragi