8 Replies Latest reply: Jan 4, 2013 1:53 PM by 643412 RSS

    Need suggestion.  Have a task not sure of how to tackle.

    643412
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      CORE     11.2.0.3.0     Production
      TNS for Solaris: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.0 - Production



      I have a table that is being split up. But on top of that, the structure of the columns being removed from the original table is being changed so that the column names are being concatenated onto the line data.
      Here is the original table build. All columns that start with A_ ...E_ are being pulled out, and are the only ones that I'm concerned with.
      CREATE TABLE FS_NRIS_INFORMS.NRI_FRCC_REF
      (
        PNVG_ID                 NUMBER(10)            NOT NULL,
        PNVG                    VARCHAR2(10 BYTE)     NOT NULL,
        LOCATION                VARCHAR2(16 BYTE),
        DESCRIPTION             VARCHAR2(254 BYTE),
        VEG_GRP                 VARCHAR2(2 BYTE),
        A_OVRSTRY_AVG_DBH_LOW   NUMBER(10),
        A_OVRSTRY_AVG_DBH_HIGH  NUMBER(10),
        A_CC_LOW                NUMBER(10),
        A_CC_HIGH               NUMBER(10),
        B_OVRSTRY_AVG_DBH_LOW   NUMBER(10),
        B_OVRSTRY_AVG_DBH_HIGH  NUMBER(10),
        B_CC_LOW                NUMBER(10),
        B_CC_HIGH               NUMBER(10),
        C_OVRSTRY_AVG_DBH_LOW   NUMBER(10),
        C_OVRSTRY_AVG_DBH_HIGH  NUMBER(10),
        C_CC_LOW                NUMBER(10),
        C_CC_HIGH               NUMBER(10),
        D_OVRSTRY_AVG_DBH_LOW   NUMBER(10),
        D_OVRSTRY_AVG_DBH_HIGH  NUMBER(10),
        D_CC_LOW                NUMBER(10),
        D_CC_HIGH               NUMBER(10),
        E_OVRSTRY_AVG_DBH_LOW   NUMBER(10),
        E_OVRSTRY_AVG_DBH_HIGH  NUMBER(10),
        E_CC_LOW                NUMBER(10),
        E_CC_HIGH               NUMBER(10),
        A_PCT                   NUMBER(10),
        B_PCT                   NUMBER(10),
        C_PCT                   NUMBER(10),
        D_PCT                   NUMBER(10),
        E_PCT                   NUMBER(10),
        CREATED_BY              VARCHAR2(30 BYTE)     DEFAULT USER,
        CREATED_DATE            DATE                  DEFAULT SYSDATE,
        CREATED_IN_INSTANCE     NUMBER(6)             NOT NULL,
        MODIFIED_BY             VARCHAR2(30 BYTE),
        MODIFIED_DATE           DATE,
        MODIFIED_IN_INSTANCE    NUMBER(6)
      )
      TABLESPACE USERS_NRIS_INFORMS
      RESULT_CACHE (MODE DEFAULT)
      PCTUSED    0
      PCTFREE    10
      INITRANS   1
      MAXTRANS   255
      STORAGE    (
                  INITIAL          80K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                  FLASH_CACHE      DEFAULT
                  CELL_FLASH_CACHE DEFAULT
                 )
      LOGGING 
      NOCOMPRESS 
      NOCACHE
      NOPARALLEL
      MONITORING;
      
      COMMENT ON TABLE FS_NRIS_INFORMS.NRI_FRCC_REF IS 'Stores reference information for the FRCC tool.';
      
      
      
      CREATE UNIQUE INDEX FS_NRIS_INFORMS.NRI_FRCC_REF_PK ON FS_NRIS_INFORMS.NRI_FRCC_REF
      (PNVG_ID)
      LOGGING
      TABLESPACE INDEXES_NRIS_INFORMS
      PCTFREE    10
      INITRANS   2
      MAXTRANS   255
      STORAGE    (
                  INITIAL          80K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                  FLASH_CACHE      DEFAULT
                  CELL_FLASH_CACHE DEFAULT
                 )
      NOPARALLEL;
      
      
      CREATE OR REPLACE TRIGGER FS_NRIS_INFORMS.nri_frcc_ref_t
      BEFORE INSERT OR UPDATE
      ON fs_nris_informs.nri_frcc_ref
      FOR EACH ROW
      DECLARE
      BEGIN
         IF INSERTING
         THEN
            db_instance.insert_audit_columns(:new.created_by,
                                             :new.created_in_instance,
                                             :new.created_date);
         ELSE
            db_instance.update_audit_columns(:new.modified_by,
                                             :new.modified_in_instance,
                                             :new.modified_date);
         END IF;
      END;
      /
      
      
      ALTER TABLE FS_NRIS_INFORMS.NRI_FRCC_REF ADD (
        CONSTRAINT NRI_FRCC_REF_PK
        PRIMARY KEY
        (PNVG_ID)
        USING INDEX FS_NRIS_INFORMS.NRI_FRCC_REF_PK
        ENABLE VALIDATE);
      INSERT SAMPLE:
      Insert into FS_NRIS_INFORMS.NRI_FRCC_REF
         (PNVG_ID, PNVG, LOCATION, DESCRIPTION, VEG_GRP, 
          A_OVRSTRY_AVG_DBH_LOW, A_OVRSTRY_AVG_DBH_HIGH, A_CC_LOW, A_CC_HIGH, B_OVRSTRY_AVG_DBH_LOW, 
          B_OVRSTRY_AVG_DBH_HIGH, B_CC_LOW, B_CC_HIGH, C_OVRSTRY_AVG_DBH_LOW, C_OVRSTRY_AVG_DBH_HIGH, 
          C_CC_LOW, C_CC_HIGH, D_OVRSTRY_AVG_DBH_LOW, D_OVRSTRY_AVG_DBH_HIGH, D_CC_LOW, 
          D_CC_HIGH, E_OVRSTRY_AVG_DBH_LOW, E_OVRSTRY_AVG_DBH_HIGH, E_CC_LOW, E_CC_HIGH, 
          A_PCT, B_PCT, C_PCT, D_PCT, E_PCT, 
          CREATED_BY, CREATED_DATE, CREATED_IN_INSTANCE, MODIFIED_BY, MODIFIED_DATE, 
          MODIFIED_IN_INSTANCE)
       Values
         (37, 'LLSH', 'Eastern', 'Longleaf Pine - Sandhills', 'xx', 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          'INFORMS', TO_DATE('04/05/2005 14:10:15', 'MM/DD/YYYY HH24:MI:SS'), 10642, 'INFORMS', TO_DATE('07/14/2005 14:37:25', 'MM/DD/YYYY HH24:MI:SS'), 
          10642);
      Insert into FS_NRIS_INFORMS.NRI_FRCC_REF
         (PNVG_ID, PNVG, LOCATION, DESCRIPTION, VEG_GRP, 
          A_OVRSTRY_AVG_DBH_LOW, A_OVRSTRY_AVG_DBH_HIGH, A_CC_LOW, A_CC_HIGH, B_OVRSTRY_AVG_DBH_LOW, 
          B_OVRSTRY_AVG_DBH_HIGH, B_CC_LOW, B_CC_HIGH, C_OVRSTRY_AVG_DBH_LOW, C_OVRSTRY_AVG_DBH_HIGH, 
          C_CC_LOW, C_CC_HIGH, D_OVRSTRY_AVG_DBH_LOW, D_OVRSTRY_AVG_DBH_HIGH, D_CC_LOW, 
          D_CC_HIGH, E_OVRSTRY_AVG_DBH_LOW, E_OVRSTRY_AVG_DBH_HIGH, E_CC_LOW, E_CC_HIGH, 
          A_PCT, B_PCT, C_PCT, D_PCT, E_PCT, 
          CREATED_BY, CREATED_DATE, CREATED_IN_INSTANCE, MODIFIED_BY, MODIFIED_DATE, 
          MODIFIED_IN_INSTANCE)
       Values
         (24, 'MABA', 'Eastern', 'Maple-Basswood', 'xx', 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          'INFORMS', TO_DATE('04/05/2005 14:10:15', 'MM/DD/YYYY HH24:MI:SS'), 10642, 'INFORMS', TO_DATE('07/14/2005 14:37:25', 'MM/DD/YYYY HH24:MI:SS'), 
          10642);
      Insert into FS_NRIS_INFORMS.NRI_FRCC_REF
         (PNVG_ID, PNVG, LOCATION, DESCRIPTION, VEG_GRP, 
          A_OVRSTRY_AVG_DBH_LOW, A_OVRSTRY_AVG_DBH_HIGH, A_CC_LOW, A_CC_HIGH, B_OVRSTRY_AVG_DBH_LOW, 
          B_OVRSTRY_AVG_DBH_HIGH, B_CC_LOW, B_CC_HIGH, C_OVRSTRY_AVG_DBH_LOW, C_OVRSTRY_AVG_DBH_HIGH, 
          C_CC_LOW, C_CC_HIGH, D_OVRSTRY_AVG_DBH_LOW, D_OVRSTRY_AVG_DBH_HIGH, D_CC_LOW, 
          D_CC_HIGH, E_OVRSTRY_AVG_DBH_LOW, E_OVRSTRY_AVG_DBH_HIGH, E_CC_LOW, E_CC_HIGH, 
          A_PCT, B_PCT, C_PCT, D_PCT, E_PCT, 
          CREATED_BY, CREATED_DATE, CREATED_IN_INSTANCE, MODIFIED_BY, MODIFIED_DATE, 
          MODIFIED_IN_INSTANCE)
       Values
         (23, 'MBOA', 'Eastern', 'Maple-Basswood-Oak-Aspen Mosaic', 'xx', 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          'INFORMS', TO_DATE('04/05/2005 14:10:15', 'MM/DD/YYYY HH24:MI:SS'), 10642, 'INFORMS', TO_DATE('07/14/2005 14:37:25', 'MM/DD/YYYY HH24:MI:SS'), 
          10642);
      Insert into FS_NRIS_INFORMS.NRI_FRCC_REF
         (PNVG_ID, PNVG, LOCATION, DESCRIPTION, VEG_GRP, 
          A_OVRSTRY_AVG_DBH_LOW, A_OVRSTRY_AVG_DBH_HIGH, A_CC_LOW, A_CC_HIGH, B_OVRSTRY_AVG_DBH_LOW, 
          B_OVRSTRY_AVG_DBH_HIGH, B_CC_LOW, B_CC_HIGH, C_OVRSTRY_AVG_DBH_LOW, C_OVRSTRY_AVG_DBH_HIGH, 
          C_CC_LOW, C_CC_HIGH, D_OVRSTRY_AVG_DBH_LOW, D_OVRSTRY_AVG_DBH_HIGH, D_CC_LOW, 
          D_CC_HIGH, E_OVRSTRY_AVG_DBH_LOW, E_OVRSTRY_AVG_DBH_HIGH, E_CC_LOW, E_CC_HIGH, 
          A_PCT, B_PCT, C_PCT, D_PCT, E_PCT, 
          CREATED_BY, CREATED_DATE, CREATED_IN_INSTANCE, MODIFIED_BY, MODIFIED_DATE, 
          MODIFIED_IN_INSTANCE)
       Values
         (26, 'MMPH', 'Eastern', 'Mixed Mesophytic Northeast', 'xx', 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          'INFORMS', TO_DATE('04/05/2005 14:10:15', 'MM/DD/YYYY HH24:MI:SS'), 10642, 'INFORMS', TO_DATE('07/14/2005 14:37:25', 'MM/DD/YYYY HH24:MI:SS'), 
          10642);
      Insert into FS_NRIS_INFORMS.NRI_FRCC_REF
         (PNVG_ID, PNVG, LOCATION, DESCRIPTION, VEG_GRP, 
          A_OVRSTRY_AVG_DBH_LOW, A_OVRSTRY_AVG_DBH_HIGH, A_CC_LOW, A_CC_HIGH, B_OVRSTRY_AVG_DBH_LOW, 
          B_OVRSTRY_AVG_DBH_HIGH, B_CC_LOW, B_CC_HIGH, C_OVRSTRY_AVG_DBH_LOW, C_OVRSTRY_AVG_DBH_HIGH, 
          C_CC_LOW, C_CC_HIGH, D_OVRSTRY_AVG_DBH_LOW, D_OVRSTRY_AVG_DBH_HIGH, D_CC_LOW, 
          D_CC_HIGH, E_OVRSTRY_AVG_DBH_LOW, E_OVRSTRY_AVG_DBH_HIGH, E_CC_LOW, E_CC_HIGH, 
          A_PCT, B_PCT, C_PCT, D_PCT, E_PCT, 
          CREATED_BY, CREATED_DATE, CREATED_IN_INSTANCE, MODIFIED_BY, MODIFIED_DATE, 
          MODIFIED_IN_INSTANCE)
       Values
         (25, 'NESF', 'Eastern', 'Northeastern Spruce-Fir', 'xx', 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          'INFORMS', TO_DATE('04/05/2005 14:10:15', 'MM/DD/YYYY HH24:MI:SS'), 10642, 'INFORMS', TO_DATE('07/14/2005 14:37:25', 'MM/DD/YYYY HH24:MI:SS'), 
          10642);
      Insert into FS_NRIS_INFORMS.NRI_FRCC_REF
         (PNVG_ID, PNVG, LOCATION, DESCRIPTION, VEG_GRP, 
          A_OVRSTRY_AVG_DBH_LOW, A_OVRSTRY_AVG_DBH_HIGH, A_CC_LOW, A_CC_HIGH, B_OVRSTRY_AVG_DBH_LOW, 
          B_OVRSTRY_AVG_DBH_HIGH, B_CC_LOW, B_CC_HIGH, C_OVRSTRY_AVG_DBH_LOW, C_OVRSTRY_AVG_DBH_HIGH, 
          C_CC_LOW, C_CC_HIGH, D_OVRSTRY_AVG_DBH_LOW, D_OVRSTRY_AVG_DBH_HIGH, D_CC_LOW, 
          D_CC_HIGH, E_OVRSTRY_AVG_DBH_LOW, E_OVRSTRY_AVG_DBH_HIGH, E_CC_LOW, E_CC_HIGH, 
          A_PCT, B_PCT, C_PCT, D_PCT, E_PCT, 
          CREATED_BY, CREATED_DATE, CREATED_IN_INSTANCE, MODIFIED_BY, MODIFIED_DATE, 
          MODIFIED_IN_INSTANCE)
       Values
         (22, 'NHDW1', 'Eastern', 'Northern Hardwoods #1', 'xx', 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          'INFORMS', TO_DATE('04/05/2005 14:10:16', 'MM/DD/YYYY HH24:MI:SS'), 10642, 'INFORMS', TO_DATE('07/14/2005 14:37:25', 'MM/DD/YYYY HH24:MI:SS'), 
          10642);
      Insert into FS_NRIS_INFORMS.NRI_FRCC_REF
         (PNVG_ID, PNVG, LOCATION, DESCRIPTION, VEG_GRP, 
          A_OVRSTRY_AVG_DBH_LOW, A_OVRSTRY_AVG_DBH_HIGH, A_CC_LOW, A_CC_HIGH, B_OVRSTRY_AVG_DBH_LOW, 
          B_OVRSTRY_AVG_DBH_HIGH, B_CC_LOW, B_CC_HIGH, C_OVRSTRY_AVG_DBH_LOW, C_OVRSTRY_AVG_DBH_HIGH, 
          C_CC_LOW, C_CC_HIGH, D_OVRSTRY_AVG_DBH_LOW, D_OVRSTRY_AVG_DBH_HIGH, D_CC_LOW, 
          D_CC_HIGH, E_OVRSTRY_AVG_DBH_LOW, E_OVRSTRY_AVG_DBH_HIGH, E_CC_LOW, E_CC_HIGH, 
          A_PCT, B_PCT, C_PCT, D_PCT, E_PCT, 
          CREATED_BY, CREATED_DATE, CREATED_IN_INSTANCE, MODIFIED_BY, MODIFIED_DATE, 
          MODIFIED_IN_INSTANCE)
       Values
         (19, 'NHDW2', 'Eastern', 'Conifer Northern Hardwoods', 'xx', 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          'INFORMS', TO_DATE('04/05/2005 14:10:16', 'MM/DD/YYYY HH24:MI:SS'), 10642, 'INFORMS', TO_DATE('07/14/2005 14:37:25', 'MM/DD/YYYY HH24:MI:SS'), 
          10642);
      Insert into FS_NRIS_INFORMS.NRI_FRCC_REF
         (PNVG_ID, PNVG, LOCATION, DESCRIPTION, VEG_GRP, 
          A_OVRSTRY_AVG_DBH_LOW, A_OVRSTRY_AVG_DBH_HIGH, A_CC_LOW, A_CC_HIGH, B_OVRSTRY_AVG_DBH_LOW, 
          B_OVRSTRY_AVG_DBH_HIGH, B_CC_LOW, B_CC_HIGH, C_OVRSTRY_AVG_DBH_LOW, C_OVRSTRY_AVG_DBH_HIGH, 
          C_CC_LOW, C_CC_HIGH, D_OVRSTRY_AVG_DBH_LOW, D_OVRSTRY_AVG_DBH_HIGH, D_CC_LOW, 
          D_CC_HIGH, E_OVRSTRY_AVG_DBH_LOW, E_OVRSTRY_AVG_DBH_HIGH, E_CC_LOW, E_CC_HIGH, 
          A_PCT, B_PCT, C_PCT, D_PCT, E_PCT, 
          CREATED_BY, CREATED_DATE, CREATED_IN_INSTANCE, MODIFIED_BY, MODIFIED_DATE, 
          MODIFIED_IN_INSTANCE)
       Values
         (18, 'NHDW3', 'Eastern', 'Northern Hardwoods #2', 'xx', 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          'INFORMS', TO_DATE('04/05/2005 14:10:16', 'MM/DD/YYYY HH24:MI:SS'), 10642, 'INFORMS', TO_DATE('07/14/2005 14:37:25', 'MM/DD/YYYY HH24:MI:SS'), 
          10642);
      Insert into FS_NRIS_INFORMS.NRI_FRCC_REF
         (PNVG_ID, PNVG, LOCATION, DESCRIPTION, VEG_GRP, 
          A_OVRSTRY_AVG_DBH_LOW, A_OVRSTRY_AVG_DBH_HIGH, A_CC_LOW, A_CC_HIGH, B_OVRSTRY_AVG_DBH_LOW, 
          B_OVRSTRY_AVG_DBH_HIGH, B_CC_LOW, B_CC_HIGH, C_OVRSTRY_AVG_DBH_LOW, C_OVRSTRY_AVG_DBH_HIGH, 
          C_CC_LOW, C_CC_HIGH, D_OVRSTRY_AVG_DBH_LOW, D_OVRSTRY_AVG_DBH_HIGH, D_CC_LOW, 
          D_CC_HIGH, E_OVRSTRY_AVG_DBH_LOW, E_OVRSTRY_AVG_DBH_HIGH, E_CC_LOW, E_CC_HIGH, 
          A_PCT, B_PCT, C_PCT, D_PCT, E_PCT, 
          CREATED_BY, CREATED_DATE, CREATED_IN_INSTANCE, MODIFIED_BY, MODIFIED_DATE, 
          MODIFIED_IN_INSTANCE)
       Values
         (21, 'NHFI', 'Eastern', 'Northern Hardwoods-Fir', 'xx', 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          'INFORMS', TO_DATE('04/05/2005 14:10:16', 'MM/DD/YYYY HH24:MI:SS'), 10642, 'INFORMS', TO_DATE('07/14/2005 14:37:25', 'MM/DD/YYYY HH24:MI:SS'), 
          10642);
      Insert into FS_NRIS_INFORMS.NRI_FRCC_REF
         (PNVG_ID, PNVG, LOCATION, DESCRIPTION, VEG_GRP, 
          A_OVRSTRY_AVG_DBH_LOW, A_OVRSTRY_AVG_DBH_HIGH, A_CC_LOW, A_CC_HIGH, B_OVRSTRY_AVG_DBH_LOW, 
          B_OVRSTRY_AVG_DBH_HIGH, B_CC_LOW, B_CC_HIGH, C_OVRSTRY_AVG_DBH_LOW, C_OVRSTRY_AVG_DBH_HIGH, 
          C_CC_LOW, C_CC_HIGH, D_OVRSTRY_AVG_DBH_LOW, D_OVRSTRY_AVG_DBH_HIGH, D_CC_LOW, 
          D_CC_HIGH, E_OVRSTRY_AVG_DBH_LOW, E_OVRSTRY_AVG_DBH_HIGH, E_CC_LOW, E_CC_HIGH, 
          A_PCT, B_PCT, C_PCT, D_PCT, E_PCT, 
          CREATED_BY, CREATED_DATE, CREATED_IN_INSTANCE, MODIFIED_BY, MODIFIED_DATE, 
          MODIFIED_IN_INSTANCE)
       Values
         (20, 'NHSP', 'Eastern', 'Northern Hardwoods-Spruce', 'xx', 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          0, 0, 0, 0, 0, 
          'INFORMS', TO_DATE('04/05/2005 14:10:16', 'MM/DD/YYYY HH24:MI:SS'), 10642, 'INFORMS', TO_DATE('07/14/2005 14:37:25', 'MM/DD/YYYY HH24:MI:SS'), 
          10642);
      The new table build is this:
      CREATE TABLE FS_NRIS_ANALYZER.NRSA_FRCC_SERAL_REF
      (
        BPS_ID_FK  NUMBER(10)                         NOT NULL,
        SERAL      VARCHAR2(20 BYTE)                  NOT NULL,
        KEY_NAME   VARCHAR2(50 BYTE)                  NOT NULL,
        KEY_VALUE  NUMBER(10)                         NOT NULL
      )
      TABLESPACE USERS_NRIS_FSVEG
      RESULT_CACHE (MODE DEFAULT)
      PCTUSED    0
      PCTFREE    10
      INITRANS   1
      MAXTRANS   255
      STORAGE    (
                  INITIAL          80K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                  FLASH_CACHE      DEFAULT
                  CELL_FLASH_CACHE DEFAULT
                 )
      LOGGING 
      NOCOMPRESS 
      NOCACHE
      NOPARALLEL
      MONITORING;
      
      
      ALTER TABLE FS_NRIS_ANALYZER.NRSA_FRCC_SERAL_REF ADD (
        CONSTRAINT NRSA_FRCC_SERAL_REF_FK 
        FOREIGN KEY (BPS_ID_FK) 
        REFERENCES FS_NRIS_ANALYZER.NRSA_FRCC_REF (BPS_ID)
        ENABLE VALIDATE);
      Here is where the fun begins...

      This new table contains the data from the columns removed from NRI_FRCC_REF.
      SERAL is the prefix of the column. So for the existing data it will be A, B, C, D, or E.
      KEY_NAME is the rest of the column name. So for the existing data it will be OVRSTRY_AVG_DBH_LOW, OVRSTRY_AVG_DBH_HIGH,
      CC_LOW, CC_HIGH, or PCT
      KEY_VALUE is the value that was assigned to that record. So, if PNVG = 1 and A_CC_HIGH = 2. Then the new record would
      look like: BPS_ID_FK = '1', SERAL = 'A', KEY_NAME= 'CC_HIGH', KEY_VALUE = 2


      Thinking I had this licked, here is what I constructed...
      select pnvg_id as BPS_ID_FK,
              substr('A_OVRSTRY_AVG_DBH_LOW', 0,1) as SERAL, 
              substr('A_OVRSTRY_AVG_DBH_LOW', 3) as KEY_NAME,
              A_OVRSTRY_AVG_DBH_LOW as KEY_VALUE        
      from FS_NRIS_INFORMS.NRI_FRCC_REF
      
      union all
      -------------------------------------------------------
      select pnvg_id as BPS_ID_FK,
              substr('B_OVRSTRY_AVG_DBH_LOW', 0,1) as SERAL, 
              substr('B_OVRSTRY_AVG_DBH_LOW', 3) as KEY_NAME,
              A_OVRSTRY_AVG_DBH_LOW as KEY_VALUE        
      from FS_NRIS_INFORMS.NRI_FRCC_REF
      ;
      union all
      -------------------------------------------------------
      select pnvg_id as BPS_ID_FK,
              substr('A_OVRSTRY_AVG_DBH_LOW', 0,1) as SERAL, 
              substr('A_OVRSTRY_AVG_DBH_LOW', 3) as KEY_NAME,
              A_OVRSTRY_AVG_DBH_LOW as KEY_VALUE        
      from FS_NRIS_INFORMS.NRI_FRCC_REF
      
      union all
      -------------------------------------------------------
      select pnvg_id as BPS_ID_FK,
              substr('A_OVRSTRY_AVG_DBH_LOW', 0,1) as SERAL, 
              substr('A_OVRSTRY_AVG_DBH_LOW', 3) as KEY_NAME,
              A_OVRSTRY_AVG_DBH_LOW as KEY_VALUE        
      from FS_NRIS_INFORMS.NRI_FRCC_REF
      
      union all
      -------------------------------------------------------
      select pnvg_id as BPS_ID_FK,
              substr('A_OVRSTRY_AVG_DBH_LOW', 0,1) as SERAL, 
              substr('A_OVRSTRY_AVG_DBH_LOW', 3) as KEY_NAME,
              A_OVRSTRY_AVG_DBH_LOW as KEY_VALUE        
      from FS_NRIS_INFORMS.NRI_FRCC_REF
      
      union all
      -------------------------------------------------------
      select pnvg_id as BPS_ID_FK,
              substr('A_OVRSTRY_AVG_DBH_LOW', 0,1) as SERAL, 
              substr('A_OVRSTRY_AVG_DBH_LOW', 3) as KEY_NAME,
              A_OVRSTRY_AVG_DBH_LOW as KEY_VALUE        
      from FS_NRIS_INFORMS.NRI_FRCC_REF
      
      union all
      -------------------------------------------------------
      select pnvg_id as BPS_ID_FK,
              substr('A_OVRSTRY_AVG_DBH_LOW', 0,1) as SERAL, 
              substr('A_OVRSTRY_AVG_DBH_LOW', 3) as KEY_NAME,
              A_OVRSTRY_AVG_DBH_LOW as KEY_VALUE        
      from FS_NRIS_INFORMS.NRI_FRCC_REF
      
      union all
      -------------------------------------------------------
      select pnvg_id as BPS_ID_FK,
              substr('A_OVRSTRY_AVG_DBH_LOW', 0,1) as SERAL, 
              substr('A_OVRSTRY_AVG_DBH_LOW', 3) as KEY_NAME,
              A_OVRSTRY_AVG_DBH_LOW as KEY_VALUE        
      from FS_NRIS_INFORMS.NRI_FRCC_REF
      
      union all
      -------------------------------------------------------
      select pnvg_id as BPS_ID_FK,
              substr('A_OVRSTRY_AVG_DBH_LOW', 0,1) as SERAL, 
              substr('A_OVRSTRY_AVG_DBH_LOW', 3) as KEY_NAME,
              A_OVRSTRY_AVG_DBH_LOW as KEY_VALUE        
      from FS_NRIS_INFORMS.NRI_FRCC_REF
      
      union all;
      And it's far from correct...nor does it function. LOL. I had it working, but I changed something and now it doesn't.

      What is the best way to accomplish pulling the data out of the original table for an insert? I was trying to just get the data pulled correctly, then I was going to export it as insert statements via TOAD, and change the schema/table name.

      Thanks.....again. ;)

      Edited by: Willy_B on Jan 3, 2013 11:23 AM
        • 1. Re: Need suggestion.  Have a task not sure of how to tackle.
          rp0428
          See BluShadow's '4. How do I convert rows to columns?' in the FAQ from the top link on the page
          SQL and PL/SQL FAQ
          • 2. Re: Need suggestion.  Have a task not sure of how to tackle.
            Frank Kulash
            Hi,

            Whenever you have a problem, please post CREATE TABLE and INSERT statements for a little sample data, and the results you want from that data.
            See the forum FAQ {message:id=9360002}

            In this case you did post CREATE TABLE statements, but it could be simplified a lot. For example, if you saw a solution that handled on the A_ and B_ columns, you could easily extend that to handle C_, D_ and E_ columns as well. Likewise, if you had a solution that only handled 2 or 3 key_names, you could easily extend that to handle the number of key_names you actually have. Please post a simplified version of the problem, including data. Include some NULLs in the sample data, to show what you want to do with them.

            Since you're using Oracle 11, you can use the SELECT ... UNPIVOT feature.
            • 3. Re: Need suggestion.  Have a task not sure of how to tackle.
              643412
              My apologies....I've edited the original posting to include the insert on the original table.

              Actually, there is no way to simplify it. That's my task.

              I gave the original table....can't change that.
              I gave the new table...can't change that.
              I now have included the data that has to be migrated....can't change that.
              I gave my script that I've been working on. That can be changed or blown away. But it actually does exactly what you say. I took the first column, and then literally copied it for all the rest of the columns.
              select pnvg_id as BPS_ID_FK,
                      substr('A_OVRSTRY_AVG_DBH_LOW', 0,1) as SERAL, 
                      substr('A_OVRSTRY_AVG_DBH_LOW', 3) as KEY_NAME,
                      A_OVRSTRY_AVG_DBH_LOW as KEY_VALUE        
              from FS_NRIS_INFORMS.NRI_FRCC_REF
              Next I went to B_....and so forth. Utilizing union all.
              But it's not right.

              I'm reading through this static pivoting and cursor projection and going cross eyed. It's hard to make sense of it the way it's laid out.

              Edited by: Willy_B on Jan 3, 2013 11:35 AM
              • 4. Re: Need suggestion.  Have a task not sure of how to tackle.
                Frank Kulash
                Hi,

                Did you include the results you want from the given data? (It's hard to tell, because the message is so long.) It would help if you posted the results for only 2 or 3 key_names; we'll find a solution that works for all your key_names.
                Are the NULLs in the sample data and results? (If the original data never has NULLs, then don't put any in the sample data, either.)
                • 5. Re: Need suggestion.  Have a task not sure of how to tackle.
                  643412
                  Here is what I need to have it end up like. This is written instructions from my PM... That's all I have.

                  This new table contains the data from the columns removed from NRI_FRCC_REF.
                  SERAL is the prefix of the column. So for the existing data it will be A, B, C, D, or E.
                  KEY_NAME is the rest of the column name. So for the existing data it will be OVRSTRY_AVG_DBH_LOW, OVRSTRY_AVG_DBH_HIGH,
                  CC_LOW, CC_HIGH, or PCT
                  KEY_VALUE is the value that was assigned to that record. So, if PNVG = 1 and A_CC_HIGH = 2. Then the new record would
                  look like: BPS_ID_FK = '1', SERAL = 'A', KEY_NAME= 'CC_HIGH', KEY_VALUE = 2


                  or as an input/output
                    select * from FS_NRIS_ANALYZER.NRSA_FRCC_SERAL_REF;
                  
                  BPS_ID_FK     SERAL     KEY_NAME     KEY_VALUE
                  1            A         CC_HIGH        2
                  • 6. Re: Need suggestion.  Have a task not sure of how to tackle.
                    643412
                    Ok, I've been reading up on the pivot data. And I have been trying to utilize it. But then it dawned on me that the pivot data scenario makes one change based on one pivot.
                    This isn't what I need. What I need, as shown above, is for EACH row to change based on the content of the column that currently holds data greater than zero.
                    • 7. Unpivot
                      Frank Kulash
                      Hi,

                      When you have data on multiple rows, and you want to display that as multiple columns on 1 row, then use pivot.
                      If I understand the problem (and I admit that's a big IF), you want to do just the opposite. You have data in mutliple columns on 1 row, and you want to display that same data in 1 column on multiple rows. That's called Unpivot .
                      (Actually, both pivot and unpivot can do 2, 3, or more columns at the same time, not just 1, but I don't think that matters in this problem.)

                      No kidding; it would really help if you posted a simplified example that showed what you want to do, including the exact results you want from the given sample data.
                      When your instructions include the exact results, that's wonderful, but it's also rare. Somebody has to spend a little time reading your instructions, deciding how it applies to the sample data, and making a mock-up of the results. If it's not worth your time to do that, I understand.
                      • 8. Re: Need suggestion.  Have a task not sure of how to tackle.
                        643412
                        I figured it out.
                        Thanks for the help.