FW: why must I declare the table name?
Paul-
Your examples didn't go through, but I have a pretty good idea
of what the problem may be. I'm assuming you're using 4gl. If you have
a variable declared with the same name as a field name used in a where
clause, Informix gets confused.
This will work:
FUNCTION example1()
DEFINE
local_field1 SMALLINT,
local_field2 SMALLINT
LET local_field1 = 4
DECLARE get_field_curs CURSOR FOR
SELECT field2 FROM some_table
WHERE field1 = local_field1
FOREACH get_field_curs INTO local_field2
...
END FUNCTION
This will return nothing:
FUNCTION example2()
DEFINE
field1 SMALLINT,
field2 SMALLINT
LET field1 = 4
DECLARE get_field_curs CURSOR FOR
SELECT field2 FROM some_table
WHERE field1 = field1
FOREACH get_field_curs INTO field2
...
END FUNCTION
To make it work, you would have to do this:
FUNCTION example3()
DEFINE
field1 SMALLINT,
field2 SMALLINT
LET field1 = 4
DECLARE get_field_curs CURSOR FOR
SELECT some_table.field2 FROM some_table
WHERE some_table.field1 = field1
FOREACH get_field_curs INTO field2
...
END FUNCTION
Or this:
FUNCTION example4()
DEFINE
field1 SMALLINT,
field2 SMALLINT,
sql_txt CHAR(200)
LET field1 = 4
LET sql_txt = "SELECT field2 FROM some_table ",
" WHERE field1 = ? "
PREPARE get_it FROM sql_txt
DECLARE get_field_curs CURSOR FOR get_it
OPEN get_field_curs USING field1
FOREACH get_field_curs INTO field2
...
END FUNCTION
Moral of the story: save the local versions in records or in variables
with different names from the fields you're selecting.
Hope it helps
--EEM
-----Original Message-----
From: PaulS [mailto:psemenick@computer-systems.com]
Sent: Wednesday, May 12, 2004 2:33 PM
To: informix-list@iiug.org
Subject: why must I declare the table name?
(Informix SE 7.23.UC13 and several others)
I have a cursor which selects fields from a single table.
Why do I have to specify the table name for the select
to work?
TEST1 fails w/o table name
TEST2-TEST4 work
There are no views or synonyms named month_adj.
The system tables for this table look ok to me.
Can anyone explain why the prepare statement works
with & w/o the table name but the declare statement doesn't.
Thanks,
Paul
sending to informix-list