Re: SQL Query

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 Received on Mon Mar 25 2002 - 15:12:03 CST