Re: SQL Query

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:ai4v9uotrsa050jt4ei6p6aape6q7u58fm_at_4ax.com...
> On Mon, 25 Mar 2002 13:44:38 -0700, SUSOTT_at_amnutrition.com wrote:
>
> >Oracle 8.1.7 on Wk2000. Connected to database using SqlPlus.
> >
> >The following insert produces error
> >"ORA-02287: sequence number not allowed here"
> >
> >This format is similiar to those found in "Oracle SQL by Alex Morrison
and
> >Alice Rischert" (page 281) and other texts.
> >What is the problem(I know that the sequence is causing it, but why) and
> >how can I reformat this to a workable insert?
> >INSERT INTO BILL_OF_MATERIALS
> > (BOM_BILL_OF_MATERIAL_ID,
> > BOM_PARENT_ID,
> > BOM_INVENTORY_ITEM_ID,
> > BOM_INVENTORY_BASIC_UNITS,
> > BOM_WASTE_FACTOR,
> > BOM_PERCENT_OF_INVENTORY_CD,
> > BOM_HIGH_PERCENT_OF_IV_CD,
> > BOM_LOW_PERCENT_OF_IV_CD,
> > BOM_GRIND_SIZE_DESC,
> > BOM_INGREDIENT_TYPE
> > )
> >select
> > BILL_OF_MATERIALS_S.NEXTVAL,
> > c.BOM_BILL_OF_MATERIAL_ID,
> > b.IV_INVENTORY_ITEMS_ID,
> > MT_INVENTORY_BASIC_UNITS,
> > MT_WASTE_FACTOR,
> > MT_PERCENT_OF_INVENTORY_CODE,
> > MT_HIGH_PERCENT_OF_IV_CODE,
> > MT_LOW_PERCENT_OF_IV_CODE,
> > MT_GRIND_SIZE_DESCRIPTION,
> > MT_INGREDIENT_TYPE
> >from
> > stg_materials_file, INVENTORY_ITEMS a, INVENTORY_ITEMS b,
> >BILL_OF_MATERIALS c
> >where (RTRIM(MT_INVENTORY_CODE) = a.IV_INVENTORY_CD
> > AND a.IV_INVENTORY_TYPE = 'FP'
> > AND a.IV_INVENTORY_ITEMS_ID = c.BOM_INVENTORY_ITEM_ID
> > AND RTRIM(MT_MATERIAL_CODE) = b.IV_INVENTORY_CD)
> >order by mt_inventory_code
> >/
> >
>
> Can't explain it, but my gut feeling says sequences are incompatible
> with order by's
> Hopefully you did already look up the error message at
> http://tahiti.oracle.com if not I can only recommend to do it, because
> that should provide an explanation.
>
> Hth
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address

Thank You very much,
Removing the order by did fixed the insert. The "fix" for the error was just to move the sequence to where it would work. Received on Mon Mar 25 2002 - 15:24:51 CST