14 Replies Latest reply on Jun 24, 2013 5:40 PM by LostInPermuation

    confused on consolidating/migrating data

    LostInPermuation

      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 request to take a table and it's data, and migrate it to another schema.  The caveat is that the table columns change names, as well as the data formats change.

      Here is the request:  (I don't know what happened to the {code} option, or what it's been replaced with...so just let me know and I'll gladly fix)

      NRI_FRCC_REF

      Rename to: NRSA_FRCC_REF

      Column Name Changes:

      PNVG_ID ==> BPS_ID

      PNVG ==> BPS

      Columns to be removed:

      All columns with A_ through E_. This data is being moved to a new table. (see below)

       

      New table:

      NRSA_FRCC_SERAL_REF

      Columns:

      BPS_ID_FK (number) ==> FK to BPS_ID (NRI_FRCC_REF)

      SERAL (varchar2(20))

      KEY_NAME (varchar2(50))

      KEY_VALUE (number(10))

       

      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

       

      I don't know why, but I can NOT seem to get my brain to wrap around this.  I'm completely drawing a blank.

       

      I know some of you will want to try and recreate...so here is the old table structure, and I grabbed the first row from the old table as an insert.

       

      CREATE TABLE 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;

       

      Insert into 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,

          )

      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);

       

      And here is the new table structure:

       

      CREATE TABLE 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,

        MODIFIED_BY    VARCHAR2(30 BYTE),

        MODIFIED_DATE  DATE,

        CREATED_BY     VARCHAR2(30 BYTE),

        CREATED_DATE   DATE

      )

      TABLESPACE USERS

      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;


      I'm assuming that it would be easier to do this via a select using decode or something like that...but again, I'm drawing a blank.

       

      Thanks for any assistance.

        • 1. Re: confused on consolidating/migrating data
          APC

          I think what you're looking for this the multi-table insert.

           

              insert all        

                 into nrsa_frcc_ref (bps_id, bps)

                       values (pnvg_id, pnvg)

                 into nrsa_frcc_seral_ref ( bps_id_fk , seral, key_name, key_value )

                      values (pnvg_id, 'A', 'OVRSTRY_AVG_DBH_LOW', a_ovrstry_avg_dbh_low)

                 into nrsa_frcc_seral_ref ( bps_id_fk , seral, key_name, key_value )

                      values (pnvg_id, 'A', 'OVRSTRY_AVG_DBH_HIGH', a_ovrstry_avg_dbh_high)

                 into nrsa_frcc_seral_ref ( bps_id_fk , seral, key_name, key_value )

                      values (pnvg_id, 'A', 'CC_LOW', a_cc_low)

                 into nrsa_frcc_seral_ref ( bps_id_fk , seral, key_name, key_value )

                      values (pnvg_id, 'A', CC_HIGH', a_cc_high)

                 into nrsa_frcc_seral_ref ( bps_id_fk , seral, key_name, key_value )

                      values (pnvg_id, 'B', 'OVRSTRY_AVG_DBH_LOW', b_ovrstry_avg_dbh_low)

             select * from nri_frcc_ref

           

          Obviously I've skipped some of the cut'n'paste but you get the idea.

           

          There is a lot of flexibility in the multi-table insert syntax.  For instance we can use conditions to control the insert.  It's all in the documentation.  Find out more: http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9014.htm#i2125362

          • 2. Re: confused on consolidating/migrating data
            Frank Kulash

            Hi,

             

            Putting each value of seral on a separate row is a good idea.

            Putting each separate key_name on a separate row sounds like a very bad idea.  Search for "Entity-Attribute-Value" (or "EAV") fo see why it results in complicated, inefficeint and error-prone SQL.

             

            Either way, you can use the SELECT ... UNPIVOT feature to efficiently convert columns into rows (that is, to take a single input row, and put that same data on to  multiple output rows, with fewer columns).

             

            If you'd like help, post INSERT statements for a little sample input (2 or 3 different values of seral, and 2 or 3 different key_names will be enough to show how) and the exact results you want from that data.

            • 3. Re: confused on consolidating/migrating data
              LostInPermuation

              Not sure I follow why this is inefficient or anything else.  This isn't meant to be dynamic or reusable.  This is a one time deal where we are changing the structure of the table.  All new data coming in, will obviously conform.  But the old data, to migrate it over, has to convert from the old column structure to the new column structure.

               

              Insert statements, I included one or two in my original....  but here are a few.

              Insert into 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 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 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 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 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 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 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 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 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 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);

              Insert into 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

                 (27, 'NOFP', 'Eastern', 'Northern Floodplain Forest', '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 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

                 (34, 'NOKS', 'Eastern', 'Northern Oak Savannah', '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 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

                 (33, 'NTPR', 'Eastern', 'Northern Tallgrass Prairie', '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 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

                 (38, 'BKBE', 'Eastern', 'Black Belt', '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 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

                 (117, 'MCAN', 'Western', 'Southwestern Mixed Conifer', 'FV',

                  0, 1, 0, 999, 1,

                  16, 31, 999, 1, 16,

                  0, 30, 16, 999, 0,

                  30, 16, 999, 31, 999,

                  10, 5, 20, 60, 5,

                  'INFORMS', TO_DATE('04/05/2005 14:10:15', 'MM/DD/YYYY HH24:MI:SS'), 10642, 'INFORMS', TO_DATE('01/04/2007 17:18:31', 'MM/DD/YYYY HH24:MI:SS'),

                  10642);

              And again, the results need to look like:

               

              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



              I'm trying to figure out how to write this in a pl/sql block.  Right now I'm struggling with a cursor trying to make it make sense.  I'm learning all this still....

               

              set serveroutput on

              DECLAR

              v_PNVG_ID                                     number;
              v_PNVG                                          varchar2;
              v_LOCATION                                    varchar2;
              v_DESCRIPTION                               varchar2;
              v_VEG_GRP                                      varchar2;

              v_A_OVRSTRY_AVG_DBH_LOW  number;

              v_A_OVRSTRY_AVG_DBH_HIGH  number;

              v_A_CC_LOW  number;

              v_A_CC_HIGH  number;

              v_B_OVRSTRY_AVG_DBH_LOW  number;

              v_B_OVRSTRY_AVG_DBH_HIGH  number;

              v_B_CC_LOW  number;

              v_B_CC_HIGH  number;

              v_C_OVRSTRY_AVG_DBH_LOW;  number

              v_C_OVRSTRY_AVG_DBH_HIGH  number;

              v_C_CC_LOW  number;

              v_C_CC_HIGH  number;

              v_D_OVRSTRY_AVG_DBH_LOW;  number

              v_D_OVRSTRY_AVG_DBH_HIGH  number;

              v_D_CC_LOW  number;

              v_D_CC_HIGH  number;

              v_E_OVRSTRY_AVG_DBH_LOW  number;

              v_E_OVRSTRY_AVG_DBH_HIGH  number;

              v_E_CC_LOW  number;

              v_E_CC_HIGH  number;

              v_A_PCT  number;

              v_B_PCT  number;

              v_C_PCT  number;

              v_D_PCT  number;

              v_E_PCT  number;

              v_CREATED_BY  varchar;

              v_CREATED_DATE  DATE;

              v_CREATED_IN_INSTANCE  number;

              v_MODIFIED_BY  varchar;

              v_MODIFIED_DATE  DATE;

              v_MODIFIED_IN_INSTANCE  number;

               

              CURSOR orig_data

              is

                   select * from FS_NRIS_INFORMS.NRI_FRCC_REF;

               

              BEGIN

                   OPEN orig_data;

                   fetch

                   LOOP

              (Not sure how to work it here.  But I've been thinking about it and I'm sure that I'll need to use a combo of substr and decode to change and divide the names of the columns and containing data.)


              Thanks.

              • 4. Re: confused on consolidating/migrating data
                Frank Kulash

                Hi,

                 

                Thanks for posting the sample data.  Don't forget to post the results you want from that data, that is, the results you want to see from "SELECT * FROM nrsa_frcc_seral_ref" after it is populated.

                 

                If you don't see why Entity-Attribute-Value is such a bad idea, then you must not have read anything about it. For example

                http://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-big-design-mistakes.html

                shows how a very simple query on a relational table would require a 3-way self-join using EAV.

                 

                Once again, this sounds like a job for SELECT ... UNPIVOT, whether you insist on using EAV or a reasonable table design.  You don't need PL/SQL or SUBSTR, and probably not DECODE (or CASE, which can do anything DECODE can do, and do it better in many situations).

                • 5. Re: confused on consolidating/migrating data
                  APC

                  LostInPermuation wrote:

                   

                  Not sure I follow why this is inefficient or anything else.  This isn't meant to be dynamic or reusable.  This is a one time deal where we are changing the structure of the table.

                   

                  Frank isn't referring to your Pl/SQL code. He is saying your proposed new structure, specifically the nrsa_frcc_seral_ref table, is likely to engender brittle, inefficient code.   Basically. you should  normalise your data model.  Or else stop using a relational database and use something more suitable instead.  http://nosql-database.org/

                   

                  Anyway, I guess the fact that you're still going on about DECODE and SUBSTR means you haven't read my suggestion at all.  Sigh.

                   

                  Cheers, APC

                  • 6. Re: confused on consolidating/migrating data
                    LostInPermuation

                    Just to clarify, I am listening.  But this isn't something I have any say in.

                    I read all of the link on "Tony Andrews on OTLT and EAV".  However interesting, I didn't understand how the EAV would be better.  The code appears to be much more complicated to use.  I do understand how it make the table it's self more flexible for use.

                     

                    I'm not stuck on using decode and substr.  I was merely stating that it was how I first figured it needed to be handled.  I'm open to pretty much anything, AS LONG as it follows the guidelines that my PM has given me.  It has to remain in Oracle, it has to remain structured "as stated".   If decode and substr are not the right solution, great.  I won't mention them again. 

                     

                    I will definitely note the problematic issues that it brings to the table, and recommend it be changed...but for this release, it's already set in stone.

                     

                    So what are my other options?

                    • 7. Re: confused on consolidating/migrating data
                      Frank Kulash

                      Hi,

                       

                      LostInPermuation wrote:

                       

                      Just to clarify, I am listening.  But this isn't something I have any say in.

                      I read all of the link on "Tony Andrews on OTLT and EAV".  However interesting, I didn't understand how the EAV would be better.  The code appears to be much more complicated to use.  I do understand how it make the table it's self more flexible for use.

                      That's exactly the point.  EAV is NOT better than 3rd Normal Form; it's worse.  The code for the EAV table appears to be much more complicated because it IS much more complicated, and much less efficient, too.

                       

                      I'm not stuck on using decode and substr.  I was merely stating that it was how I first figured it needed to be handled.  I'm open to pretty much anything, AS LONG as it follows the guidelines that my PM has given me.  It has to remain in Oracle, it has to remain structured "as stated".  If decode and substr are not the right solution, great.  I won't mention them again. 

                       

                      I will definitely note the problematic issues that it brings to the table, and recommend it be changed...but for this release, it's already set in stone.

                       

                      So what are my other options?

                      Use SELECT ... UNPIVOT, like I suggested, or a "multi-table" insert like APC suggested.  (I use quotes around "multi-table" because all the muliple destination tables are actually the same table in this case.)  I suspect SELECT ... UNPIVOT will be simpler to code and debug.  It will probably be more efficient, too, but for a one-time operation the difference might not be important. Again, if you'd like help, post the exact results you want from the given sample data.

                      • 8. Re: confused on consolidating/migrating data
                        LostInPermuation

                        Hey Frank.

                        Select...unpivot it is.  Great.  You are asking for the results, I've posted them twice now.  Am I not posting what you are looking for?

                         

                        example:

                        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

                         

                        RESULTS: BPS_ID_FK = '1', SERAL = 'A', KEY_NAME= 'CC_HIGH', KEY_VALUE = 2

                         

                        Or in columnar format?

                        BPS_ID_FKSERALKEY_NAMEKEY_VALUE
                        1ACC_HIGH2
                        • 9. Re: confused on consolidating/migrating data
                          LostInPermuation

                          Actually, I don't think I'm explaining the transformation well enough.  The original table had more columns, and the column names were different.  the referenced A, B, C, D, or E was actually combined with the KEY_NAME value.  So it was A_CC_HIGH, B_CC_HIGH, D_CC_HIGH and E_CC_HIGH.  There are about 6 different KEY_NAME values all repeating the A-E prefix.  That is being broke out as described.  Hence the reason I was looking at using (the forbidden words  )...  So some columns are dropped, and others are split and renamed

                          • 10. Re: confused on consolidating/migrating data
                            Frank Kulash

                            Hi

                             

                            LostInPermuation wrote:

                             

                            Hey Frank.

                            Select...unpivot it is.  Great.  You are asking for the results, I've posted them twice now.  Am I not posting what you are looking for?

                            If you've already posted the results twice, then I've already posted the solution twice. You've posted a somewhat vague description of the results twice, and I've posted a somewhat vague description of the solution twice.  I can't post an exact solution until I know the exact results.

                            ... Or in columnar format?

                            BPS_ID_FK SERAL KEY_NAME KEY_VALUE
                            1 A CC_HIGH 2

                             

                            The "columnar format" above is what I need, but for all rows, not just 1. Simplify the problem.  Post results for 2 or 3 distinct values of seral, and 2 or 3 distinct values of key_name.  We'll find a solution that can easily be adapted to the actual number or serals and key_names.

                            • 11. Re: confused on consolidating/migrating data
                              LostInPermuation

                              Here is one row with many values:

                               

                              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

                              59

                              PPIN6

                              Western

                              Ponderosa Pine Southern Rockies

                              FV

                              0

                              1

                              0

                              999

                              1

                              12

                              31

                              999

                              1

                              12

                              0

                              30

                              12

                              999

                              0

                              30

                              12

                              999

                              31

                              999

                              15

                              5

                              25

                              50

                              5

                              INFORMS

                              4/5/2005 2:10:16 PM

                              10642

                              INFORMS

                              1/5/2007 9:37:02 AM

                              10642

                              And here is how it should look:

                               

                              BPS_ID_FK

                              SERAL

                              KEY_NAME

                              KEY_VALUE

                              59

                              A

                              OVRSTRY_AVG_DBH_LOW

                              0

                              59

                              A

                              OVRSTRY_AVG_DBH_HIGH

                              1

                              59

                              A

                              CC_LOW

                              0

                              59

                              A

                              CC_HIGH

                              999

                              59

                              B

                              OVRSTRY_AVG_DBH_LOW

                              1

                              59

                              B

                              OVRSTRY_AVG_DBH_HIGH

                              12

                              59

                              B

                              CC_LOW

                              31

                              59

                              B

                              CC_HIGH

                              999

                              59

                              C

                              OVRSTRY_AVG_DBH_LOW

                              1

                              59

                              C

                              OVRSTRY_AVG_DBH_HIGH

                              12

                              59

                              C

                              CC_LOW

                              0

                              59

                              C

                              CC_HIGH

                              30

                              59

                              D

                              OVRSTRY_AVG_DBH_LOW

                              12

                              59

                              D

                              OVRSTRY_AVG_DBH_HIGH

                              999

                              59

                              D

                              CC_LOW

                              0

                              59

                              D

                              CC_HIGH

                              30

                              59

                              E

                              OVRSTRY_AVG_DBH_LOW

                              12

                              59

                              E

                              OVRSTRY_AVG_DBH_HIGH

                              999

                              59

                              E

                              CC_LOW

                              31

                              59

                              E

                              CC_HIGH

                              999

                               

                              That has to be done to every row in the table.

                               

                              Hope that is a better example.

                              • 12. Re: confused on consolidating/migrating data
                                Frank Kulash

                                Hi,

                                 

                                Is that the output you want from the sample data you posted 2 days ago?

                                If so, explain how you get that bps_id_fk and those key_values from the given data.

                                If not, post INSERT statements for the sample data that produce that output.

                                • 13. Re: confused on consolidating/migrating data
                                  LostInPermuation

                                  My apologies for the delays.  I am having issues with my CSI subscription.

                                   

                                  The original request was that I was converting the data to a new table structure.  That is the WHOLE reason I've been posting this...I'm not trying to change columns to rows or vice versa.  I'm trying to figure out how to convert the data.

                                  The only insert statements I have is from the original table output.  Look back to the original posting.   It shows how the tables are differing.  The primary key changes names, some of the columns change names.  Some columns are being dropped...  That output I just gave was created manually reflecting the necessary changes.   This might make more sense as to why I was suggesting substr and decode now.  Not saying that they could still be the answer, but it might make more sense as to why I mentioned them.

                                  • 14. Re: confused on consolidating/migrating data
                                    LostInPermuation

                                    Here is what I ended up doing...

                                     

                                    To convert the data:

                                    --OVRSTRY_AVG_DBH_LOW

                                    --A

                                    select PNVG_ID as BPS_ID_FK,

                                    substr('A_OVRSTRY_AVG_DBH_LOW',1,1) as SERAL,

                                    substr('A_OVRSTRY_AVG_DBH_LOW', 3) as KEY_NAME,

                                    NVL(A_OVRSTRY_AVG_DBH_LOW, '0') as KEY_VALUE

                                    from FS_NRIS_INFORMS.NRI_FRCC_REF

                                    union all

                                    --B

                                    select PNVG_ID as BPS_ID_FK,

                                    substr('B_OVRSTRY_AVG_DBH_LOW',1,1) as SERAL,

                                    substr('B_OVRSTRY_AVG_DBH_LOW', 3) as KEY_NAME,

                                    NVL(B_OVRSTRY_AVG_DBH_LOW, '0') as KEY_VALUE

                                    from FS_NRIS_INFORMS.NRI_FRCC_REF

                                    union all

                                    --C

                                    select PNVG_ID as BPS_ID_FK,

                                    substr('C_OVRSTRY_AVG_DBH_LOW',1,1) as SERAL,

                                    substr('C_OVRSTRY_AVG_DBH_LOW', 3) as KEY_NAME,

                                    NVL(C_OVRSTRY_AVG_DBH_LOW, '0') as KEY_VALUE

                                    from FS_NRIS_INFORMS.NRI_FRCC_REF

                                    union all

                                    --D

                                    select PNVG_ID as BPS_ID_FK,

                                    substr('D_OVRSTRY_AVG_DBH_LOW',1,1) as SERAL,

                                    substr('D_OVRSTRY_AVG_DBH_LOW', 3) as KEY_NAME,

                                    NVL(D_OVRSTRY_AVG_DBH_LOW, '0') as KEY_VALUE

                                    from FS_NRIS_INFORMS.NRI_FRCC_REF

                                    union all

                                    --E

                                    select PNVG_ID as BPS_ID_FK,

                                    substr('E_OVRSTRY_AVG_DBH_LOW',1,1) as SERAL,

                                    substr('E_OVRSTRY_AVG_DBH_LOW', 3) as KEY_NAME,

                                    NVL(E_OVRSTRY_AVG_DBH_LOW, '0') as KEY_VALUE

                                    from FS_NRIS_INFORMS.NRI_FRCC_REF

                                    union all

                                    --############################################################

                                    --OVRSTRY_AVG_DBH_HIGH

                                    --A

                                    select PNVG_ID as BPS_ID_FK,

                                    substr('A_OVRSTRY_AVG_DBH_HIGH',1,1) as SERAL,

                                    substr('A_OVRSTRY_AVG_DBH_HIGH', 3) as KEY_NAME,

                                    NVL(A_OVRSTRY_AVG_DBH_HIGH, '0') as KEY_VALUE

                                    from FS_NRIS_INFORMS.NRI_FRCC_REF

                                     

                                    union all

                                    --B

                                    select PNVG_ID as BPS_ID_FK,

                                    substr('B_OVRSTRY_AVG_DBH_HIGH',1,1) as SERAL,

                                    substr('B_OVRSTRY_AVG_DBH_HIGH', 3) as KEY_NAME,

                                    NVL(B_OVRSTRY_AVG_DBH_HIGH, '0') as KEY_VALUE

                                    from FS_NRIS_INFORMS.NRI_FRCC_REF

                                     

                                    union all

                                    --C

                                    select PNVG_ID as BPS_ID_FK,

                                    substr('C_OVRSTRY_AVG_DBH_HIGH',1,1) as SERAL,

                                    substr('C_OVRSTRY_AVG_DBH_HIGH', 3) as KEY_NAME,

                                    NVL(C_OVRSTRY_AVG_DBH_HIGH, '0') as KEY_VALUE

                                    from FS_NRIS_INFORMS.NRI_FRCC_REF

                                     

                                    --D

                                    union all

                                    select PNVG_ID as BPS_ID_FK,

                                    substr('D_OVRSTRY_AVG_DBH_HIGH',1,1) as SERAL,

                                    substr('D_OVRSTRY_AVG_DBH_HIGH', 3) as KEY_NAME,

                                    NVL(D_OVRSTRY_AVG_DBH_HIGH, '0') as KEY_VALUE

                                    from FS_NRIS_INFORMS.NRI_FRCC_REF

                                     

                                    --E

                                    union all

                                    select PNVG_ID as BPS_ID_FK,

                                    substr('E_OVRSTRY_AVG_DBH_HIGH',1,1) as SERAL,

                                    substr('E_OVRSTRY_AVG_DBH_HIGH', 3) as KEY_NAME,

                                    NVL(E_OVRSTRY_AVG_DBH_HIGH, '0') as KEY_VALUE

                                    from FS_NRIS_INFORMS.NRI_FRCC_REF

                                     

                                    union all

                                    --############################################################

                                    --CC_LOW

                                    --A

                                    select PNVG_ID as BPS_ID_FK,

                                    substr('A_CC_LOW',1,1) as SERAL,

                                    substr('A_CC_LOW', 3) as KEY_NAME,

                                    NVL(A_CC_LOW, '0') as KEY_VALUE

                                    from FS_NRIS_INFORMS.NRI_FRCC_REF

                                     

                                    union all

                                    --B

                                    select PNVG_ID as BPS_ID_FK,

                                    substr('B_CC_LOW',1,1) as SERAL,

                                    substr('B_CC_LOW', 3) as KEY_NAME,

                                    NVL(B_CC_LOW, '0') as KEY_VALUE

                                    from FS_NRIS_INFORMS.NRI_FRCC_REF

                                     

                                    --C

                                    union all

                                    select PNVG_ID as BPS_ID_FK,

                                    substr('C_CC_LOW',1,1) as SERAL,

                                    substr('C_CC_LOW', 3) as KEY_NAME,

                                    NVL(C_CC_LOW, '0') as KEY_VALUE

                                    from FS_NRIS_INFORMS.NRI_FRCC_REF

                                     

                                    --D

                                    union all

                                    select PNVG_ID as BPS_ID_FK,

                                    substr('D_CC_LOW',1,1) as SERAL,

                                    substr('D_CC_LOW', 3) as KEY_NAME,

                                    NVL(D_CC_LOW, '0') as KEY_VALUE

                                    from FS_NRIS_INFORMS.NRI_FRCC_REF

                                     

                                    --E

                                    union all

                                    select PNVG_ID as BPS_ID_FK,

                                    substr('E_CC_LOW',1,1) as SERAL,

                                    substr('E_CC_LOW', 3) as KEY_NAME,

                                    NVL(E_CC_LOW, '0') as KEY_VALUE

                                    from FS_NRIS_INFORMS.NRI_FRCC_REF

                                     

                                    union all

                                    --############################################################

                                    --CC_HIGH

                                    --A

                                    select PNVG_ID as BPS_ID_FK,

                                    substr('A_CC_HIGH',1,1) as SERAL,

                                    substr('A_CC_HIGH', 3) as KEY_NAME,

                                    NVL(A_CC_HIGH, '0') as KEY_VALUE

                                    from FS_NRIS_INFORMS.NRI_FRCC_REF

                                     

                                    union all

                                    --B

                                    select PNVG_ID as BPS_ID_FK,

                                    substr('B_CC_HIGH',1,1) as SERAL,

                                    substr('B_CC_HIGH', 3) as KEY_NAME,

                                    NVL(B_CC_HIGH, '0') as KEY_VALUE

                                    from FS_NRIS_INFORMS.NRI_FRCC_REF

                                     

                                    --C

                                    union all

                                    select PNVG_ID as BPS_ID_FK,

                                    substr('C_CC_HIGH',1,1) as SERAL,

                                    substr('C_CC_HIGH', 3) as KEY_NAME,

                                    NVL(C_CC_HIGH, '0') as KEY_VALUE

                                    from FS_NRIS_INFORMS.NRI_FRCC_REF

                                     

                                    --D

                                    union all

                                    select PNVG_ID as BPS_ID_FK,

                                    substr('D_CC_HIGH',1,1) as SERAL,

                                    substr('D_CC_HIGH', 3) as KEY_NAME,

                                    NVL(D_CC_HIGH, '0') as KEY_VALUE

                                    from FS_NRIS_INFORMS.NRI_FRCC_REF

                                     

                                    --E

                                    union all

                                    select PNVG_ID as BPS_ID_FK,

                                    substr('E_CC_HIGH',1,1) as SERAL,

                                    substr('E_CC_HIGH', 3) as KEY_NAME,

                                    NVL(E_CC_HIGH, '0') as KEY_VALUE

                                    from FS_NRIS_INFORMS.NRI_FRCC_REF

                                     

                                    ;

                                     

                                    I would have liked to figure out a way to run one loop of that and take care of everything, but this got the job done.