Re: Trouble with Oracle Lite v5.0.2

"DA Morgan" <damorgan_at_exesolutions.com> wrote in message news:3E346F9C.3B7302A2_at_exesolutions.com...
> Princess Morgiah wrote:
>
> > Hi everybody,
> >
> > I don't know whether or not this is the correct group to ask, but this
seems
> > the most likely.
> >
> > I'm working with Oracle Lite v5.0.2 and I am stuck with a simple insert
> > statement. Whenever I execute the query in msql, I get an error
(pol-5233):
> > sequence not allowed here. If I run the insert with values instead of
> > subqueries, everything works fine.
> >
> > Could anybody please shed some light on this? I have no idea why this
does
> > not work.
> >
> > --- insert ---
> > insert into tumarch.tag (seq, tag, tagtype, collection, saved)
> > values ((select tumarch.seq_tumarch.nextval from dual), 'Leuven', 2,
(Select
> > seq from tumarch.collection where name = 'TestCollection'), 1);
> > [POL-5233] sequence not allowed here
> >
> > --- table tag ---
> > describe tag
> > Name Null? Type
> > ------------------------------- ----- -------
> > SEQ N NUMBER(10)
> > COLLECTION N NUMBER(10)
> > TAG N VARCHAR(250)
> > TAGTYPE N NUMBER(1)
> > SAVED N BIT(1)
> >
> > --- table collection ---
> > describe collection
> > Name Null? Type
> > ------------------------------- ----- -------
> > SEQ N NUMBER(10)
> > COLLECTION N NUMBER(10)
> > TAG N VARCHAR(250)
> > TAGTYPE N NUMBER(1)
> > SAVED N BIT(1)
> > ---
> >
> > Thanks in advance,
> >
> > Princess Morgiah
>
> Correct your code to:
>
> insert into tumarch.tag
> (seq, tag, tagtype, collection, saved)
> values
> (tumarch.seq_tumarch.NEXTVAL, 'Leuven', 2, ...
>
> Do don't do a select from dual in an insert statement.

Thanks for your advice, but it turns out that that is not the problem.

I switched the select from dual to tumarch.seq_tumarch.NEXTVAL, and I still receive the error. Then I switched the second select (select seq from collection where ...) with a fixed known sequence (one I knew existed), and the insert works perfectly. Now this I really do not understand.

• this works --- insert into tumarch.tag (seq, tag, tagtype, collection, saved) values ((tumarch.seq_tumarch.nextval), 'Leuven', 2, 26, 1);
• this does not (?) --- insert into tumarch.tag (seq, tag, tagtype, collection, saved) values ((tumarch.seq_tumarch.nextval), 'Leuven', 2, (Select seq from tumarch.collection where name = 'TestCollection'), 1); ---

Thanks in advance,

Princess Morgiah Received on Sun Jan 26 2003 - 17:55:07 CST