9 Replies Latest reply: Jul 3, 2013 6:05 AM by DK2010 RSS

    sql loader problem

    901346

      Please help me why record has been discarded-

      PROJECT_DATA_1

      (

        PROJECT_ID                NUMBER,

        PROJECT_TYPE_ID          NUMBER,

        CVP_STAGE_ID              NUMBER,

        EFFECTIVE_DATE            DATE,

        E_AUCTION                VARCHAR2(1 BYTE),

        ESOURCED                  VARCHAR2(1 BYTE),

        ONESOURCE_REF            VARCHAR2(20 BYTE),

        MST_METRIC_ID            NUMBER,

        NODE_ID                  NUMBER,

        PERIOD_ID                NUMBER,

        CAPTURED_BENEFIT          NUMBER,

        ADD_IN_YEAR_BENEFIT      NUMBER,

        PROJECTS_IN_PROGRESS      NUMBER,

        ALL_PLANNED_PROJECTS      NUMBER,

        MOST_LIKELY              NUMBER,

        CAPTURED_BENEFIT_CA      NUMBER,

        ADD_IN_YEAR_BENEFIT_CA    NUMBER,

        PROJECTS_IN_PROGRESS_CA  NUMBER,

        ALL_PLANNED_PROJECTS_CA  NUMBER,

        MOST_LIKELY_CA            NUMBER,

        CAPTURED_BENEFIT_CR      NUMBER,

        ADD_IN_YEAR_BENEFIT_CR    NUMBER,

        PROJECTS_IN_PROGRESS_CR  NUMBER,

        ALL_PLANNED_PROJECTS_CR  NUMBER,

        MOST_LIKELY_CR            NUMBER,

        CREATION_DATE            DATE,

        UPDATE_DATE              DATE,

        UPDATE_BY                NUMBER,

        COST_AVOID_PCT            NUMBER,

        COST_REDUCT_PCT          NUMBER,

        ESOURCING_EVENT          NUMBER,

        ESOURCING_FOLLOWON_EVENT  NUMBER,

        PROJECT_SUB_TYPE          NUMBER,

        PROCURMENT_LEAD_ID        NUMBER,

        MS_ID                    NUMBER,

        SAVINGS_TYPE_ID          NUMBER,

        FINANCIAL_CATEGORY_ID    NUMBER,

        PROJECT_NAME              VARCHAR2(2000 BYTE),

        DESCRIPTION              VARCHAR2(2000 BYTE),

        REPORT_VALUE              NUMBER,

        REPORT_YTD                NUMBER,

        PSCM_MARKET_SECTOR        NUMBER,

        REPORT_BU                NUMBER,

        REPORT_SPU                NUMBER,

        REPORT_CATEGORY          NUMBER,

        TOTAL_BENEFIT            NUMBER,

        IP_LOOKUP                NUMBER,

        REPORT_CATEGORY_2        NUMBER,

        MANUAL_TAR_OVERRIDE      VARCHAR2(4000 BYTE),

        SITE_ID                  NUMBER

      )

      LOGGING

      NOCOMPRESS

      NOCACHE

      NOPARALLEL

      MONITORING;

       

      -------------CTL FILE----------------

      LOAD DATA

      INFILE *

      BADFILE './project-data.BAD'

      DISCARDFILE './project-data.DSC'

      APPEND INTO TABLE PROJECT_DATA_1

      Fields terminated by ";" Optionally enclosed by '^'

      (

        PROJECT_ID NULLIF (PROJECT_ID="NULL"),

        PROJECT_TYPE_ID NULLIF (PROJECT_TYPE_ID="NULL"),

        CVP_STAGE_ID NULLIF (CVP_STAGE_ID="NULL"),

        EFFECTIVE_DATE DATE "MM/DD/YYYY HH24:MI:SS" NULLIF (EFFECTIVE_DATE="NULL"),

        E_AUCTION,

        ESOURCED,

        ONESOURCE_REF,

        MST_METRIC_ID NULLIF (MST_METRIC_ID="NULL"),

        NODE_ID NULLIF (NODE_ID="NULL"),

        PERIOD_ID NULLIF (PERIOD_ID="NULL"),

        CAPTURED_BENEFIT NULLIF (CAPTURED_BENEFIT="NULL"),

        ADD_IN_YEAR_BENEFIT NULLIF (ADD_IN_YEAR_BENEFIT="NULL"),

        PROJECTS_IN_PROGRESS NULLIF (PROJECTS_IN_PROGRESS="NULL"),

        ALL_PLANNED_PROJECTS NULLIF (ALL_PLANNED_PROJECTS="NULL"),

        MOST_LIKELY NULLIF (MOST_LIKELY="NULL"),

        CAPTURED_BENEFIT_CA NULLIF (CAPTURED_BENEFIT_CA="NULL"),

        ADD_IN_YEAR_BENEFIT_CA NULLIF (ADD_IN_YEAR_BENEFIT_CA="NULL"),

        PROJECTS_IN_PROGRESS_CA NULLIF (PROJECTS_IN_PROGRESS_CA="NULL"),

        ALL_PLANNED_PROJECTS_CA NULLIF (ALL_PLANNED_PROJECTS_CA="NULL"),

        MOST_LIKELY_CA NULLIF (MOST_LIKELY_CA="NULL"),

        CAPTURED_BENEFIT_CR NULLIF (CAPTURED_BENEFIT_CR="NULL"),

        ADD_IN_YEAR_BENEFIT_CR NULLIF (ADD_IN_YEAR_BENEFIT_CR="NULL"),

        PROJECTS_IN_PROGRESS_CR NULLIF (PROJECTS_IN_PROGRESS_CR="NULL"),

        ALL_PLANNED_PROJECTS_CR NULLIF (ALL_PLANNED_PROJECTS_CR="NULL"),

        MOST_LIKELY_CR NULLIF (MOST_LIKELY_CR="NULL"),

        CREATION_DATE DATE "MM/DD/YYYY HH24:MI:SS" NULLIF (CREATION_DATE="NULL"),

        UPDATE_DATE DATE "MM/DD/YYYY HH24:MI:SS" NULLIF (UPDATE_DATE="NULL"),

        UPDATE_BY NULLIF (UPDATE_BY="NULL"),

        COST_AVOID_PCT NULLIF (COST_AVOID_PCT="NULL"),

        COST_REDUCT_PCT NULLIF (COST_REDUCT_PCT="NULL"),

        ESOURCING_EVENT NULLIF (ESOURCING_EVENT="NULL"),

        ESOURCING_FOLLOWON_EVENT NULLIF (ESOURCING_FOLLOWON_EVENT="NULL"),

        PROJECT_SUB_TYPE NULLIF (PROJECT_SUB_TYPE="NULL"),

        PROCURMENT_LEAD_ID NULLIF (PROCURMENT_LEAD_ID="NULL"),

        MS_ID NULLIF (MS_ID="NULL"),

        SAVINGS_TYPE_ID NULLIF (SAVINGS_TYPE_ID="NULL"),

        FINANCIAL_CATEGORY_ID NULLIF (FINANCIAL_CATEGORY_ID="NULL"),

        PROJECT_NAME,

        DESCRIPTION,

        REPORT_VALUE NULLIF (REPORT_VALUE="NULL"),

        REPORT_YTD NULLIF (REPORT_YTD="NULL"),

        PSCM_MARKET_SECTOR NULLIF (PSCM_MARKET_SECTOR="NULL"),

        REPORT_BU NULLIF (REPORT_BU="NULL"),

        REPORT_SPU NULLIF (REPORT_SPU="NULL"),

        REPORT_CATEGORY NULLIF (REPORT_CATEGORY="NULL"),

        TOTAL_BENEFIT NULLIF (TOTAL_BENEFIT="NULL"),

        IP_LOOKUP NULLIF (IP_LOOKUP="NULL"),

        REPORT_CATEGORY_2 NULLIF (REPORT_CATEGORY_2="NULL"),

        MANUAL_TAR_OVERRIDE,

        SITE_ID NULLIF (SITE_ID="NULL")

      )

      BEGINDATA

      1170680;50119;3;^06/30/2013 00:00:00^;^Y^;^Y^;^WS238970526^;153;14;18;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;^06/29/2013 16:52:00^;NULL;NULL;100;0;715820;715824;-1;4783;1075268;480111;50116;^Poland 2013 - High Rise MOP^;^ Pylon reklamowy na stacjach autostradowych^;0;0;6;900602;64;16;0;NULL;16;^^;900602

      1112891;50121;6;^06/19/2012 00:00:00^;^N^;^N^;^^;153;3;18;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;^06/29/2013 16:52:00^;NULL;NULL;0;100;-1;-1;-1;3628;1075262;480111;50116;^McJunkin 2012 Level 1 Value Added Saving^;^ 1Q cost savings: 1) one time for Application Eng: Technical Assistance - $92,059.77. Stocking Neway Cast Valves at the local McJunkin shop. 1Q sales of $225,387.72 with ~29% savings. 2) Product substitution - $9624.18 one-time cost savings. Stocked Smith forged valves for Cherry Point. 1Q sales $28,872.55. 3) Clean up of surplus - MRC Buy backs for 5-14-12 through 11-1-12. Results in Credits of $66,347.39.^;0;0;1;102;65;22;0;NULL;22;^^;102