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 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
> >
> >
>
>