RE: why must I declare the table name?
Hi, I've occasionally had similar problem if I have a program variable named
identically to a column name used in the select, although usually on the
'where' side of statement. May be related?
Regards,
Bryce Stenberg
(Harness Racing New Zealand computer dept.)
-----Original Message-----
From: PaulS [mailto:psemenick@computer-systems.com]
Sent: Thursday, 13 May 2004 7:47 a.m.
To: informix-list@iiug.org
Subject: Re: why must I declare the table name?
Sorry, the word document was removed by newsgroup apparently. here is the
program etc:
Table: month_adj
Column name Type Nulls
mj_sort smallint yes
mj_end_date date yes
mj_amount money(16,2) yes
mj_comment char(53) yes
select * from month_adj
mj_sort 1
mj_end_date 04/30/2004
mj_amount $30.00
mj_comment COFFEE FUND
mj_sort 2
mj_end_date 04/30/2004
mj_amount $65.00
mj_comment BIRTHDAY FUND
DATABASE jts
MAIN
database jts
call get_adj_rows()
END MAIN
FUNCTION get_adj_rows()
define
mj_sort like month_adj.mj_sort,
mj_amount like month_adj.mj_amount,
mj_comment like month_adj.mj_comment,
mj_end_date like month_adj.mj_end_date,
m_string char(100)
#TEST1 - FAILS
declare mj_curs cursor for
select mj_sort,mj_amount,mj_end_date
from month_adj
#TEST2 - WORKS
# declare mj_curs cursor for
# select month_adj.mj_sort,month_adj.mj_amount,month_adj.mj_end_date
# from month_adj
#TEST3 - WORKS
# prepare mj_qry2 from
# "select mj_sort,mj_amount,mj_end_date from month_adj"
# declare mj_curs cursor for mj_qry2
#TEST4 - WORKS
# let m_string="select mj_sort,mj_amount,mj_end_date from month_adj"
# prepare mj_qry2 from m_string
# declare mj_curs cursor for mj_qry2
foreach mj_curs into mj_sort,mj_amount,mj_end_date
display mj_sort," ",mj_amount," ",mj_end_date sleep 5
end foreach
END FUNCTION
#TEST1 ERROR:
Program stopped at "m_test.4gl", line number 17. #17=declare
mj_curs cursor
SQL statement error number -201.
A syntax error has occurred.
"PaulS" <psemenick@computer-systems.com> wrote in message
news:10a4v87kn5hmt99@corp.supernews.com...
> (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
>
>
>
DISCLAIMER: http://www.hrnz.co.nz/eDisclaimer.htm
sending to informix-list