This discussion is archived
8 Replies Latest reply: Jan 4, 2013 11:53 AM by 643412 RSS

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

643412 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    I figured it out.
    Thanks for the help.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points