1 2 Previous Next 15 Replies Latest reply: Aug 7, 2013 10:08 AM by Mike Kutz Go to original post RSS
      • 15. Re: Dynamically assign values to different ROWTYPE-Columns by ruletable
        Mike Kutz

        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.


        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