This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Aug 7, 2013 8:08 AM by Mike Kutz Go to original post RSS
  • 15. Re: Dynamically assign values to different ROWTYPE-Columns by ruletable
    Mike Kutz Expert
    Currently Being Moderated

    seggione wrote:

     

    3. So we need to check if:

    1. Is there data in column xy the "source"-table? -> Use this source / value
    2. Is there a default-value in column xy for this specific customer provided? -> Use this source / value
    3. No initial source or no customer-based default declared? -> Use general default

     

     

    This would be the COALESCE() command.

     

    Also, your 'customer_defaults' table needs to be in a WIDE format.

    eg

    create table customer_defaults (

      customer_id int primary key

    ,table1_column1 varchar2(1)

    ,table1_column3 number

    ,table1_column5 varchar2(3)

    -- etc

    );

     

    This way you can:

    insert into PO_LINEITEMS ( column1 -- etc )

    select coalesce( ST.column1, CD.table1_column1, MyConstants.table1_column1_default ) as COLUMN1

      -- etc

    from staging_table ST

      join PO P on (/* what ever makes a PO unique within the staging table

                              New POs were bulk-created in a prior step

                            */)

      left outer join customer_defaults CD on (ST.customer_id=CD.customer_id);

1 2 Previous Next

Legend

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