RE: 4GL merge
Hendrick-
Do you mean "Can I add more rows to a temp table I've already
loaded?" If you do then try this:
SELECT mycolumns FROM mytable
WHERE whatever = whateverelse
INTO TEMP my_temp_table
... lines of other code
SELECT mycolumns FROM mytable
WHERE whatever = whateverelse
INTO my_temp_table
Merge is not a keyword that Informix recognizes.
If you intend to rerun the same sql and add only rows not existing in
your temp table, be sure to exclude them on your second select:
SELECT mycolumns FROM mytable
WHERE whatever = whateverelse
INTO TEMP my_temp_table
... lines of other code
SELECT mycolumns FROM mytable
WHERE whatever = whateverelse
AND my_unique_column NOT IN (SELECT my_unique_column FROM
my_temp_table)
INTO my_temp_table
--EEM
> -----Original Message-----
> From: Hendrick [mailto:hendrickq@hotmail.com]
> Sent: Wednesday, July 19, 2006 9:51 AM
> To: informix-list@iiug.org
> Subject: 4GL merge
>
> Is there a merge function in I4GL? I am attempting to merge or append
> data to a temp table that gets created within the program. Example:
>
> select pe_id,
> dued8,
> inv_d8,
> pe_name from inmbap
> where inmbap.dued8 = vSTARTD8 and inmbap.paid_flag = 'N' and
> into temp the_temp_file>
> This creates "the_temp_file" now i would like to include another
select
> the loads data into the "the_temp_file".
>
> select pe_id,
> dued8,
> inv_d8,
> pe_name from inmbap
> where inmbap.dued8 = vSTARTD8 and inmbap.paid_flag = 'N' and
> merge into temp the_temp_file>
>
> Any help would be appreciated.
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
Everett Mills wrote: [snip] > Merge is not a keyword that Informix recognizes. From the fine Informix manual: >>-MERGE INTO--+-------------------------------+--+-target_table---+--+---------------+--> | (1) | +-target_view----+ '-+----+--alias-' '-| Optimizer Directives |------' '-target_synonym-' '-AS-' >--USING--+-source_table----+--+---------------+----------------> +-source_view-----+ '-+----+--alias-' '-source_subquery-' '-AS-' (2) >--ON--| Condition |--------------------------------------------> (3) >--WHEN MATCHED THEN UPDATE--| SET Clause |---------------------> .-,------. V | (4) >--WHEN NOT MATCHED THEN INSERT----column-+--| VALUES Clause |------>< PS - only XPS :-)
Everett Mills wrote:
> Hendrick-
>
> Do you mean "Can I add more rows to a temp table I've already
> loaded?" If you do then try this:
>
> SELECT mycolumns FROM mytable
> WHERE whatever = whateverelse
> INTO TEMP my_temp_table>
> ... lines of other code
>
> SELECT mycolumns FROM mytable
> WHERE whatever = whateverelse
> INTO my_temp_table<SNIP>
No, you cannot use INTO TEMP with an existing table. That clause wants to
create a new table. However, once the first statement runs and the temp
table exists, you can use INSERT INTO ... SELECT ... FROM; syntax:
INSERT INTO my_temp_table
SELECT mycolumns FROM mytable
WHERE whatever = whateverelse;
Art S. Kagel