why must I declare the table name?
PaulS (Informix SE 7.23, 4GL) found a declared cursor on month_adj failed unless the table name was given. June Hunt suggested that program variables named like columns cause the problem. Renaming the variables fixed it. Dave Griffen explained that 4GL substitutes the variable's value into the statement instead of the column name.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
(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 begin 666 ~$nth_adj error.doc M#5!A=6P@4V5M96YI8VL````````````````````````````````````````` M````````````#0!0`&$`=0!L`" `4P!E`&T`90!N`&D`8P!K``````!N```` M"P````L````(````%@````L````!`"A%A0`!@!0`,$6%``& % !,184``8 4 ;`&A%A0`!@!0`A$6%``& % ",184``8 4`*A% ` end
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
>
>
>
PaulS wrote:
> Sorry, the word document was removed by newsgroup apparently.
>here is the program etc:
I've trimmed this a bit to save space...
> 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>
> [other tests clipped]
>
> 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.
>
I am going from memory here, but I seem to remember occasional problems when
using a variable that is defined with the same name as a column. Try making
a minor change to your variable names to see if that doesn't fix the first
test case.
>
> "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
> >
--
June Hunt
Thanks June,
Yes, it works if i change the variable names. Anyone know why?
In any case I'll try to always make the variable names a little different.
Paul
"June C. Hunt" <june_c_hunt@hotmail.com> wrote in message
news:2gffthF28og7U1@uni-berlin.de...
> PaulS wrote:
> > Sorry, the word document was removed by newsgroup apparently.
> >here is the program etc:
>
> I've trimmed this a bit to save space...
>
> > 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> >
> > [other tests clipped]
> >
> > 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.
> >
>
> I am going from memory here, but I seem to remember occasional problems
when
> using a variable that is defined with the same name as a column. Try
making
> a minor change to your variable names to see if that doesn't fix the first
> test case.
>
> >
> > "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
> > >
>
> --
> June Hunt
>
>
4gl fills in the value of the program variable instead of the field name
into the declare statement before submitting the query to the database for
execution.
"PaulS" <psemenick@computer-systems.com> wrote in message
news:10a54ffrl8eot62@corp.supernews.com...
> Thanks June,
> Yes, it works if i change the variable names. Anyone know why?
> In any case I'll try to always make the variable names a little different.
> Paul
> "June C. Hunt" <june_c_hunt@hotmail.com> wrote in message
> news:2gffthF28og7U1@uni-berlin.de...
> > PaulS wrote:
> > > Sorry, the word document was removed by newsgroup apparently.
> > >here is the program etc:
> >
> > I've trimmed this a bit to save space...
> >
> > > 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> > >
> > > [other tests clipped]
> > >
> > > 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.
> > >
> >
> > I am going from memory here, but I seem to remember occasional problems
> when
> > using a variable that is defined with the same name as a column. Try
> making
> > a minor change to your variable names to see if that doesn't fix the
first
> > test case.
> >
> > >
> > > "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
> > > >
> >
> > --
> > June Hunt
> >
> >
>
>