Results 1 to 4 of 4

Thread: sql procedure

  1. #1
    Join Date
    Jul 2003
    Location
    porto - Portugal
    Posts
    74

    Unanswered: sql procedure

    Hi people,

    I was trying to create an table (PROD2) with the same columns of PROD table...but i just want to insert 100 000 rows...
    I receive the folowing error:

    RMLEITE.CREATE_PROD: 271: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "as" was found following "ROM basereft.prod d)". Expected tokens may include: "FROM". LINE NUMBER=271. SQLSTATE=42601

    Take a look at this procedure:




    CREATE PROCEDURE RMLEITE.CREATE_PROD ( )
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN

    CREATE TABLE BASEREFT.PROD2 (
    "SKU_ID" INTEGER NOT NULL WITH DEFAULT 999999999,
    "SKU_NAME" CHAR(25) ,
    "CO_ID" SMALLINT ,
    "DIV_ID" SMALLINT WITH DEFAULT 9999,
    "DPT_ID" SMALLINT WITH DEFAULT 9999,
    "CAT_ID" CHAR(5) WITH DEFAULT '99999',
    "CAT_DESC" CHAR(50) ,
    "SCTN_ID" CHAR(3) ,
    "SCTN_DESC" CHAR(50) ,
    "STYLE_ID" CHAR(10) WITH DEFAULT '9999999999',
    "COLOUR_ID" CHAR(2) WITH DEFAULT '99',
    "COLOUR_DESC" CHAR(10) ,
    "SIZE" CHAR(4) ,
    "SIZE_DESC" CHAR(13) ,
    "FABRIC_TYPE_ID" CHAR(4) ,
    "FABRIC_TYPE_DESC" CHAR(50) ,
    "PRDCT_TYPE_ID" CHAR(1) ,
    "PRDCT_TYPE_DESC" CHAR(50) ,
    "PRI_GRP_PRDCT_TYPE_ID" CHAR(4) ,
    "PRI_PRDCT_TYPE_DESC" CHAR(50) ,
    "SUPP_ID" INTEGER ,
    "SUPP_DESC" CHAR(50) ,
    "SSN_ID" CHAR(2) ,
    "SSN_DESC" CHAR(50) ,
    "WKS_ON_SALE" INTEGER ,
    "CNTRY_OF_ORIGIN_ID" CHAR(3) ,
    "IFS_ID" CHAR(2) WITH DEFAULT '99',
    "IFS_DESC" CHAR(50) ,
    "CONT_FLG" CHAR(1) ,
    "CO_ATTR_VAL_ID_01" CHAR(3) ,
    "CO_ATTR_VAL_ID_02" CHAR(3) ,
    "CO_ATTR_VAL_ID_03" CHAR(3) ,
    "CO_ATTR_VAL_ID_04" CHAR(3) ,
    "CO_ATTR_VAL_ID_05" CHAR(3) ,
    "CO_ATTR_VAL_ID_06" CHAR(3) ,
    "CO_ATTR_VAL_ID_07" CHAR(3) ,
    "CO_ATTR_VAL_ID_08" CHAR(3) ,
    "CO_ATTR_VAL_ID_09" CHAR(3) ,
    "CO_ATTR_VAL_ID_10" CHAR(3) ,
    "CO_ATTR_VAL_ID_11" CHAR(3) ,
    "CO_ATTR_VAL_ID_12" CHAR(3) ,
    "CO_ATTR_VAL_ID_13" CHAR(3) ,
    "CO_ATTR_VAL_ID_14" CHAR(3) ,
    "CO_ATTR_VAL_ID_15" CHAR(3) ,
    "CO_ATTR_VAL_ID_16" CHAR(3) ,
    "CO_ATTR_VAL_ID_17" CHAR(3) ,
    "CO_ATTR_VAL_ID_18" CHAR(3) ,
    "CO_ATTR_VAL_ID_19" CHAR(3) ,
    "CO_ATTR_VAL_ID_20" CHAR(3) ,
    "CO_ATTR_VAL_ID_21" CHAR(3) ,
    "CO_ATTR_VAL_ID_22" CHAR(3) ,
    "CO_ATTR_VAL_ID_23" CHAR(3) ,
    "CO_ATTR_VAL_ID_24" CHAR(3) ,
    "CO_ATTR_VAL_ID_25" CHAR(3) ,
    "CO_ATTR_VAL_ID_26" CHAR(3) ,
    "CO_ATTR_VAL_ID_27" CHAR(3) ,
    "CO_ATTR_VAL_ID_28" CHAR(3) ,
    "CO_ATTR_VAL_ID_29" CHAR(3) ,
    "CO_ATTR_VAL_ID_30" CHAR(3) ,
    "DPT_ATTR_VAL_ID_01" CHAR(3) ,
    "DPT_ATTR_VAL_ID_02" CHAR(3) ,
    "DPT_ATTR_VAL_ID_03" CHAR(3) ,
    "DPT_ATTR_VAL_ID_04" CHAR(3) ,
    "DPT_ATTR_VAL_ID_05" CHAR(3) ,
    "DPT_ATTR_VAL_ID_06" CHAR(3) ,
    "DPT_ATTR_VAL_ID_07" CHAR(3) ,
    "DPT_ATTR_VAL_ID_08" CHAR(3) ,
    "DPT_ATTR_VAL_ID_09" CHAR(3) ,
    "DPT_ATTR_VAL_ID_10" CHAR(3) ,
    "DPT_ATTR_VAL_ID_11" CHAR(3) ,
    "DPT_ATTR_VAL_ID_12" CHAR(3) ,
    "DPT_ATTR_VAL_ID_13" CHAR(3) ,
    "DPT_ATTR_VAL_ID_14" CHAR(3) ,
    "DPT_ATTR_VAL_ID_15" CHAR(3) ,
    "DPT_ATTR_VAL_ID_16" CHAR(3) ,
    "DPT_ATTR_VAL_ID_17" CHAR(3) ,
    "DPT_ATTR_VAL_ID_18" CHAR(3) ,
    "DPT_ATTR_VAL_ID_19" CHAR(3) ,
    "DPT_ATTR_VAL_ID_20" CHAR(3) ,
    "ROW_STATUS" CHAR(1) )
    IN USERSPACE6 NOT LOGGED INITIALLY ;
    -- DDL Statements for indexes on Table "BASEREFT"."PROD"

    CREATE INDEX BASEREFT.PROD_05 ON "BASEREFT"."PROD"
    ("DPT_ID" ASC,
    "IFS_ID" ASC,
    "STYLE_ID" ASC,
    "COLOUR_ID" ASC);

    -- DDL Statements for indexes on Table "BASEREFT"."PROD"

    CREATE INDEX BASEREFT.PROD_06 ON "BASEREFT"."PROD"
    ("STYLE_ID" ASC,
    "COLOUR_ID" ASC,
    "SKU_ID" ASC) ALLOW REVERSE SCANS;

    -- DDL Statements for indexes on Table "BASEREFT"."PROD"

    CREATE UNIQUE INDEX BASEREFT.PROD_U2 ON "BASEREFT"."PROD"
    ("SKU_ID" ASC);

    INSERT INTO basereft.prod2
    SELECT SKU_ID,
    SKU_NAME ,
    CO_ID,
    DIV_ID,
    DPT_ID,
    CAT_ID,
    CAT_DESC ,
    SCTN_ID,
    SCTN_DESC ,
    STYLE_ID,
    COLOUR_ID,
    COLOUR_DESC,
    "SIZE" ,
    SIZE_DESC,
    FABRIC_TYPE_ID,
    FABRIC_TYPE_DESC,
    PRDCT_TYPE_ID,
    PRDCT_TYPE_DESC,
    PRI_GRP_PRDCT_TYPE_ID,
    PRI_PRDCT_TYPE_DESC ,
    SUPP_ID,
    SUPP_DESC,
    SSN_ID,
    SSN_DESC,
    WKS_ON_SALE,
    CNTRY_OF_ORIGIN_ID,
    IFS_ID ,
    IFS_DESC,
    CONT_FLG ,
    CO_ATTR_VAL_ID_01 ,
    CO_ATTR_VAL_ID_02 ,
    CO_ATTR_VAL_ID_03 ,
    CO_ATTR_VAL_ID_04 ,
    CO_ATTR_VAL_ID_05 ,
    CO_ATTR_VAL_ID_06 ,
    CO_ATTR_VAL_ID_07 ,
    CO_ATTR_VAL_ID_08 ,
    CO_ATTR_VAL_ID_09 ,
    CO_ATTR_VAL_ID_10 ,
    CO_ATTR_VAL_ID_11 ,
    CO_ATTR_VAL_ID_12 ,
    CO_ATTR_VAL_ID_13 ,
    CO_ATTR_VAL_ID_14 ,
    CO_ATTR_VAL_ID_15 ,
    CO_ATTR_VAL_ID_16 ,
    CO_ATTR_VAL_ID_17 ,
    CO_ATTR_VAL_ID_18 ,
    CO_ATTR_VAL_ID_19 ,
    CO_ATTR_VAL_ID_20 ,
    CO_ATTR_VAL_ID_21 ,
    CO_ATTR_VAL_ID_22 ,
    CO_ATTR_VAL_ID_23 ,
    CO_ATTR_VAL_ID_24 ,
    CO_ATTR_VAL_ID_25 ,
    CO_ATTR_VAL_ID_26 ,
    CO_ATTR_VAL_ID_27 ,
    CO_ATTR_VAL_ID_28 ,
    CO_ATTR_VAL_ID_29 ,
    CO_ATTR_VAL_ID_30 ,
    DPT_ATTR_VAL_ID_01 ,
    DPT_ATTR_VAL_ID_02 ,
    DPT_ATTR_VAL_ID_03 ,
    DPT_ATTR_VAL_ID_04 ,
    DPT_ATTR_VAL_ID_05 ,
    DPT_ATTR_VAL_ID_06 ,
    DPT_ATTR_VAL_ID_07 ,
    DPT_ATTR_VAL_ID_08 ,
    DPT_ATTR_VAL_ID_09,
    DPT_ATTR_VAL_ID_10,
    DPT_ATTR_VAL_ID_11,
    DPT_ATTR_VAL_ID_12,
    DPT_ATTR_VAL_ID_13,
    DPT_ATTR_VAL_ID_14,
    DPT_ATTR_VAL_ID_15 ,
    DPT_ATTR_VAL_ID_16,
    DPT_ATTR_VAL_ID_17,
    DPT_ATTR_VAL_ID_18,
    DPT_ATTR_VAL_ID_19,
    DPT_ATTR_VAL_ID_20 ,
    ROW_STATUS,
    (SELECT row_number() over() as rownum,
    d.SKU_NAME ,
    d.CO_ID,
    d.DIV_ID,
    d.DPT_ID,
    d.CAT_ID,
    d.CAT_DESC ,
    d.SCTN_ID,
    d.SCTN_DESC ,
    d.STYLE_ID,
    d.COLOUR_ID,
    d.COLOUR_DESC,
    "d.SIZE" ,
    d.SIZE_DESC,
    d.FABRIC_TYPE_ID,
    d.FABRIC_TYPE_DESC,
    d.PRDCT_TYPE_ID,
    d.PRDCT_TYPE_DESC,
    d.PRI_GRP_PRDCT_TYPE_ID,
    d.PRI_PRDCT_TYPE_DESC ,
    d.SUPP_ID,
    d.SUPP_DESC,
    d.SSN_ID,
    d.SSN_DESC,
    d.WKS_ON_SALE,
    d.CNTRY_OF_ORIGIN_ID,
    d.IFS_ID ,
    d.IFS_DESC,
    d.CONT_FLG ,
    d.CO_ATTR_VAL_ID_01 ,
    d.CO_ATTR_VAL_ID_02 ,
    d.CO_ATTR_VAL_ID_03 ,
    d.CO_ATTR_VAL_ID_04 ,
    d.CO_ATTR_VAL_ID_05 ,
    d.CO_ATTR_VAL_ID_06 ,
    d.CO_ATTR_VAL_ID_07 ,
    d.CO_ATTR_VAL_ID_08 ,
    d.CO_ATTR_VAL_ID_09 ,
    d.CO_ATTR_VAL_ID_10 ,
    d.CO_ATTR_VAL_ID_11 ,
    d.CO_ATTR_VAL_ID_12 ,
    d.CO_ATTR_VAL_ID_13 ,
    d.CO_ATTR_VAL_ID_14 ,
    d.CO_ATTR_VAL_ID_15 ,
    d.CO_ATTR_VAL_ID_16 ,
    d.CO_ATTR_VAL_ID_17 ,
    d.CO_ATTR_VAL_ID_18 ,
    d.CO_ATTR_VAL_ID_19 ,
    d.CO_ATTR_VAL_ID_20 ,
    d.CO_ATTR_VAL_ID_21 ,
    d.CO_ATTR_VAL_ID_22 ,
    d.CO_ATTR_VAL_ID_23 ,
    d.CO_ATTR_VAL_ID_24 ,
    d.CO_ATTR_VAL_ID_25 ,
    d.CO_ATTR_VAL_ID_26 ,
    d.CO_ATTR_VAL_ID_27 ,
    d.CO_ATTR_VAL_ID_28 ,
    d.CO_ATTR_VAL_ID_29 ,
    d.CO_ATTR_VAL_ID_30 ,
    d.DPT_ATTR_VAL_ID_01 ,
    d.DPT_ATTR_VAL_ID_02 ,
    d.DPT_ATTR_VAL_ID_03 ,
    d.DPT_ATTR_VAL_ID_04 ,
    d.DPT_ATTR_VAL_ID_05 ,
    d.DPT_ATTR_VAL_ID_06 ,
    d.DPT_ATTR_VAL_ID_07 ,
    d.DPT_ATTR_VAL_ID_08 ,
    d.DPT_ATTR_VAL_ID_09,
    d.DPT_ATTR_VAL_ID_10,
    d.DPT_ATTR_VAL_ID_11,
    d.DPT_ATTR_VAL_ID_12,
    d.DPT_ATTR_VAL_ID_13,
    d.DPT_ATTR_VAL_ID_14,
    d.DPT_ATTR_VAL_ID_15 ,
    d.DPT_ATTR_VAL_ID_16,
    d.DPT_ATTR_VAL_ID_17,
    d.DPT_ATTR_VAL_ID_18,
    d.DPT_ATTR_VAL_ID_19,
    d.DPT_ATTR_VAL_ID_20 ,
    d.ROW_STATUS
    FROM basereft.prod d) as b
    WHERE b.rownum < 100000

    END P1



    Someone could help me?

    Thanks,
    Rui

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    The lines

    DPT_ATTR_VAL_ID_18,
    DPT_ATTR_VAL_ID_19,
    DPT_ATTR_VAL_ID_20 ,
    ROW_STATUS,
    (SELECT row_number() over() as rownum,
    d.SKU_NAME ,


    should read

    DPT_ATTR_VAL_ID_18,
    DPT_ATTR_VAL_ID_19,
    DPT_ATTR_VAL_ID_20 ,
    ROW_STATUS
    from
    (SELECT row_number() over() as rownum,
    d.SKU_NAME ,

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jul 2003
    Location
    porto - Portugal
    Posts
    74

    sql procedure

    Thanks for your help...


    Know, i receive the folowing error:
    RMLEITE.CREATE_PROD: 110: [IBM][CLI Driver][DB2/NT] SQL0206N "SKU_ID" is not valid in the context where it is used. LINE NUMBER=110. SQLSTATE=42703

    Could you help me?

    CREATE PROCEDURE RMLEITE.CREATE_PROD ( )
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN

    CREATE TABLE BASEREFT.PROD2 (
    "SKU_ID" INTEGER WITH DEFAULT 999999999,
    "SKU_NAME" CHAR(25) ,
    "CO_ID" SMALLINT ,
    "DIV_ID" SMALLINT WITH DEFAULT 9999,
    "DPT_ID" SMALLINT WITH DEFAULT 9999,
    "CAT_ID" CHAR(5) WITH DEFAULT '99999',
    "CAT_DESC" CHAR(50) ,
    "SCTN_ID" CHAR(3) ,
    "SCTN_DESC" CHAR(50) ,
    "STYLE_ID" CHAR(10) WITH DEFAULT '9999999999',
    "COLOUR_ID" CHAR(2) WITH DEFAULT '99',
    "COLOUR_DESC" CHAR(10) ,
    "SIZE" CHAR(4) ,
    "SIZE_DESC" CHAR(13) ,
    "FABRIC_TYPE_ID" CHAR(4) ,
    "FABRIC_TYPE_DESC" CHAR(50) ,
    "PRDCT_TYPE_ID" CHAR(1) ,
    "PRDCT_TYPE_DESC" CHAR(50) ,
    "PRI_GRP_PRDCT_TYPE_ID" CHAR(4) ,
    "PRI_PRDCT_TYPE_DESC" CHAR(50) ,
    "SUPP_ID" INTEGER ,
    "SUPP_DESC" CHAR(50) ,
    "SSN_ID" CHAR(2) ,
    "SSN_DESC" CHAR(50) ,
    "WKS_ON_SALE" INTEGER ,
    "CNTRY_OF_ORIGIN_ID" CHAR(3) ,
    "IFS_ID" CHAR(2) WITH DEFAULT '99',
    "IFS_DESC" CHAR(50) ,
    "CONT_FLG" CHAR(1) ,
    "CO_ATTR_VAL_ID_01" CHAR(3) ,
    "CO_ATTR_VAL_ID_02" CHAR(3) ,
    "CO_ATTR_VAL_ID_03" CHAR(3) ,
    "CO_ATTR_VAL_ID_04" CHAR(3) ,
    "CO_ATTR_VAL_ID_05" CHAR(3) ,
    "CO_ATTR_VAL_ID_06" CHAR(3) ,
    "CO_ATTR_VAL_ID_07" CHAR(3) ,
    "CO_ATTR_VAL_ID_08" CHAR(3) ,
    "CO_ATTR_VAL_ID_09" CHAR(3) ,
    "CO_ATTR_VAL_ID_10" CHAR(3) ,
    "CO_ATTR_VAL_ID_11" CHAR(3) ,
    "CO_ATTR_VAL_ID_12" CHAR(3) ,
    "CO_ATTR_VAL_ID_13" CHAR(3) ,
    "CO_ATTR_VAL_ID_14" CHAR(3) ,
    "CO_ATTR_VAL_ID_15" CHAR(3) ,
    "CO_ATTR_VAL_ID_16" CHAR(3) ,
    "CO_ATTR_VAL_ID_17" CHAR(3) ,
    "CO_ATTR_VAL_ID_18" CHAR(3) ,
    "CO_ATTR_VAL_ID_19" CHAR(3) ,
    "CO_ATTR_VAL_ID_20" CHAR(3) ,
    "CO_ATTR_VAL_ID_21" CHAR(3) ,
    "CO_ATTR_VAL_ID_22" CHAR(3) ,
    "CO_ATTR_VAL_ID_23" CHAR(3) ,
    "CO_ATTR_VAL_ID_24" CHAR(3) ,
    "CO_ATTR_VAL_ID_25" CHAR(3) ,
    "CO_ATTR_VAL_ID_26" CHAR(3) ,
    "CO_ATTR_VAL_ID_27" CHAR(3) ,
    "CO_ATTR_VAL_ID_28" CHAR(3) ,
    "CO_ATTR_VAL_ID_29" CHAR(3) ,
    "CO_ATTR_VAL_ID_30" CHAR(3) ,
    "DPT_ATTR_VAL_ID_01" CHAR(3) ,
    "DPT_ATTR_VAL_ID_02" CHAR(3) ,
    "DPT_ATTR_VAL_ID_03" CHAR(3) ,
    "DPT_ATTR_VAL_ID_04" CHAR(3) ,
    "DPT_ATTR_VAL_ID_05" CHAR(3) ,
    "DPT_ATTR_VAL_ID_06" CHAR(3) ,
    "DPT_ATTR_VAL_ID_07" CHAR(3) ,
    "DPT_ATTR_VAL_ID_08" CHAR(3) ,
    "DPT_ATTR_VAL_ID_09" CHAR(3) ,
    "DPT_ATTR_VAL_ID_10" CHAR(3) ,
    "DPT_ATTR_VAL_ID_11" CHAR(3) ,
    "DPT_ATTR_VAL_ID_12" CHAR(3) ,
    "DPT_ATTR_VAL_ID_13" CHAR(3) ,
    "DPT_ATTR_VAL_ID_14" CHAR(3) ,
    "DPT_ATTR_VAL_ID_15" CHAR(3) ,
    "DPT_ATTR_VAL_ID_16" CHAR(3) ,
    "DPT_ATTR_VAL_ID_17" CHAR(3) ,
    "DPT_ATTR_VAL_ID_18" CHAR(3) ,
    "DPT_ATTR_VAL_ID_19" CHAR(3) ,
    "DPT_ATTR_VAL_ID_20" CHAR(3) ,
    "ROW_STATUS" CHAR(1) )
    IN USERSPACE6 NOT LOGGED INITIALLY ;
    -- DDL Statements for indexes on Table "BASEREFT"."PROD2"

    CREATE INDEX BASEREFT.PROD_05 ON "BASEREFT"."PROD2"
    ("DPT_ID" ASC,
    "IFS_ID" ASC,
    "STYLE_ID" ASC,
    "COLOUR_ID" ASC);

    -- DDL Statements for indexes on Table "BASEREFT"."PROD2"

    CREATE INDEX BASEREFT.PROD_06 ON "BASEREFT"."PROD2"
    ("STYLE_ID" ASC,
    "COLOUR_ID" ASC,
    "SKU_ID" ASC) ALLOW REVERSE SCANS;

    -- DDL Statements for indexes on Table "BASEREFT"."PROD2"

    CREATE UNIQUE INDEX BASEREFT.PROD_U2 ON "BASEREFT"."PROD2"
    ("SKU_ID" ASC);

    INSERT INTO basereft.prod2
    SELECT SKU_ID,
    SKU_NAME ,
    CO_ID,
    DIV_ID,
    DPT_ID,
    CAT_ID,
    CAT_DESC ,
    SCTN_ID,
    SCTN_DESC ,
    STYLE_ID,
    COLOUR_ID,
    COLOUR_DESC,
    SIZE ,
    SIZE_DESC,
    FABRIC_TYPE_ID,
    FABRIC_TYPE_DESC,
    PRDCT_TYPE_ID,
    PRDCT_TYPE_DESC,
    PRI_GRP_PRDCT_TYPE_ID,
    PRI_PRDCT_TYPE_DESC ,
    SUPP_ID,
    SUPP_DESC,
    SSN_ID,
    SSN_DESC,
    WKS_ON_SALE,
    CNTRY_OF_ORIGIN_ID,
    IFS_ID ,
    IFS_DESC,
    CONT_FLG ,
    CO_ATTR_VAL_ID_01 ,
    CO_ATTR_VAL_ID_02 ,
    CO_ATTR_VAL_ID_03 ,
    CO_ATTR_VAL_ID_04 ,
    CO_ATTR_VAL_ID_05 ,
    CO_ATTR_VAL_ID_06 ,
    CO_ATTR_VAL_ID_07 ,
    CO_ATTR_VAL_ID_08 ,
    CO_ATTR_VAL_ID_09 ,
    CO_ATTR_VAL_ID_10 ,
    CO_ATTR_VAL_ID_11 ,
    CO_ATTR_VAL_ID_12 ,
    CO_ATTR_VAL_ID_13 ,
    CO_ATTR_VAL_ID_14 ,
    CO_ATTR_VAL_ID_15 ,
    CO_ATTR_VAL_ID_16 ,
    CO_ATTR_VAL_ID_17 ,
    CO_ATTR_VAL_ID_18 ,
    CO_ATTR_VAL_ID_19 ,
    CO_ATTR_VAL_ID_20 ,
    CO_ATTR_VAL_ID_21 ,
    CO_ATTR_VAL_ID_22 ,
    CO_ATTR_VAL_ID_23 ,
    CO_ATTR_VAL_ID_24 ,
    CO_ATTR_VAL_ID_25 ,
    CO_ATTR_VAL_ID_26 ,
    CO_ATTR_VAL_ID_27 ,
    CO_ATTR_VAL_ID_28 ,
    CO_ATTR_VAL_ID_29 ,
    CO_ATTR_VAL_ID_30 ,
    DPT_ATTR_VAL_ID_01 ,
    DPT_ATTR_VAL_ID_02 ,
    DPT_ATTR_VAL_ID_03 ,
    DPT_ATTR_VAL_ID_04 ,
    DPT_ATTR_VAL_ID_05 ,
    DPT_ATTR_VAL_ID_06 ,
    DPT_ATTR_VAL_ID_07 ,
    DPT_ATTR_VAL_ID_08 ,
    DPT_ATTR_VAL_ID_09,
    DPT_ATTR_VAL_ID_10,
    DPT_ATTR_VAL_ID_11,
    DPT_ATTR_VAL_ID_12,
    DPT_ATTR_VAL_ID_13,
    DPT_ATTR_VAL_ID_14,
    DPT_ATTR_VAL_ID_15 ,
    DPT_ATTR_VAL_ID_16,
    DPT_ATTR_VAL_ID_17,
    DPT_ATTR_VAL_ID_18,
    DPT_ATTR_VAL_ID_19,
    DPT_ATTR_VAL_ID_20 ,
    ROW_STATUS
    FROM (SELECT row_number() over() as rownum,
    d.SKU_NAME ,
    d.CO_ID,
    d.DIV_ID,
    d.DPT_ID,
    d.CAT_ID,
    d.CAT_DESC ,
    d.SCTN_ID,
    d.SCTN_DESC ,
    d.STYLE_ID,
    d.COLOUR_ID,
    d.COLOUR_DESC,
    d.SIZE ,
    d.SIZE_DESC,
    d.FABRIC_TYPE_ID,
    d.FABRIC_TYPE_DESC,
    d.PRDCT_TYPE_ID,
    d.PRDCT_TYPE_DESC,
    d.PRI_GRP_PRDCT_TYPE_ID,
    d.PRI_PRDCT_TYPE_DESC ,
    d.SUPP_ID,
    d.SUPP_DESC,
    d.SSN_ID,
    d.SSN_DESC,
    d.WKS_ON_SALE,
    d.CNTRY_OF_ORIGIN_ID,
    d.IFS_ID ,
    d.IFS_DESC,
    d.CONT_FLG ,
    d.CO_ATTR_VAL_ID_01 ,
    d.CO_ATTR_VAL_ID_02 ,
    d.CO_ATTR_VAL_ID_03 ,
    d.CO_ATTR_VAL_ID_04 ,
    d.CO_ATTR_VAL_ID_05 ,
    d.CO_ATTR_VAL_ID_06 ,
    d.CO_ATTR_VAL_ID_07 ,
    d.CO_ATTR_VAL_ID_08 ,
    d.CO_ATTR_VAL_ID_09 ,
    d.CO_ATTR_VAL_ID_10 ,
    d.CO_ATTR_VAL_ID_11 ,
    d.CO_ATTR_VAL_ID_12 ,
    d.CO_ATTR_VAL_ID_13 ,
    d.CO_ATTR_VAL_ID_14 ,
    d.CO_ATTR_VAL_ID_15 ,
    d.CO_ATTR_VAL_ID_16 ,
    d.CO_ATTR_VAL_ID_17 ,
    d.CO_ATTR_VAL_ID_18 ,
    d.CO_ATTR_VAL_ID_19 ,
    d.CO_ATTR_VAL_ID_20 ,
    d.CO_ATTR_VAL_ID_21 ,
    d.CO_ATTR_VAL_ID_22 ,
    d.CO_ATTR_VAL_ID_23 ,
    d.CO_ATTR_VAL_ID_24 ,
    d.CO_ATTR_VAL_ID_25 ,
    d.CO_ATTR_VAL_ID_26 ,
    d.CO_ATTR_VAL_ID_27 ,
    d.CO_ATTR_VAL_ID_28 ,
    d.CO_ATTR_VAL_ID_29 ,
    d.CO_ATTR_VAL_ID_30 ,
    d.DPT_ATTR_VAL_ID_01 ,
    d.DPT_ATTR_VAL_ID_02 ,
    d.DPT_ATTR_VAL_ID_03 ,
    d.DPT_ATTR_VAL_ID_04 ,
    d.DPT_ATTR_VAL_ID_05 ,
    d.DPT_ATTR_VAL_ID_06 ,
    d.DPT_ATTR_VAL_ID_07 ,
    d.DPT_ATTR_VAL_ID_08 ,
    d.DPT_ATTR_VAL_ID_09,
    d.DPT_ATTR_VAL_ID_10,
    d.DPT_ATTR_VAL_ID_11,
    d.DPT_ATTR_VAL_ID_12,
    d.DPT_ATTR_VAL_ID_13,
    d.DPT_ATTR_VAL_ID_14,
    d.DPT_ATTR_VAL_ID_15 ,
    d.DPT_ATTR_VAL_ID_16,
    d.DPT_ATTR_VAL_ID_17,
    d.DPT_ATTR_VAL_ID_18,
    d.DPT_ATTR_VAL_ID_19,
    d.DPT_ATTR_VAL_ID_20 ,
    d.ROW_STATUS
    FROM basereft.prod d) as b
    WHERE b.rownum < 100000;
    commit;

    END P1

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You have

    SELECT SKU_ID,

    but in the FROM clause there is no SKU_ID

    FROM (SELECT row_number() over() as rownum,
    d.SKU_NAME ,
    d.CO_ID,

    Change this to
    FROM (SELECT row_number() over() as rownum,SKU_ID
    d.SKU_NAME ,
    d.CO_ID,

    Cheers

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •