Re: Dimension table load - PLSQL question

Thanks Thomas. This is good idea to work around single table problem and MERGE command, but it will not work in this case. I am not updating anything, only returning key if record exists. Inserting otherwise.
 Regards, Ranko.

 On 9/29/05, Mercadante, Thomas F (LABOR) < Thomas.Mercadante_at_labor.state.ny.us> wrote:
>
> Ronko,
>
> Could you union all of the selects into one query and then use merge?
>
> Merge (table)
>
> Using
>
> select descr1
>
> from lkp_table1 where cd = p_cd1
>
> union
>
> select descr2
>
> from lkp_table2 where cd = p_cd2 etc
>
>
>
> etc………
>
>
>
> Would this work?
>
> Tom
>
> ------------------------------
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Ranko Mosic
> *Sent:* Thursday, September 29, 2005 8:45 AM
> *To:* Paul Drake
> *Cc:* ORACLE-L
> *Subject:* Re: Dimension table load - PLSQL question
>
> Thanks for very helpful, no patronizing answer. If you've read more
> carefully what the problem is
>
> you'd see that MERGE can't work because it works on one table upserting
> another.
>
> I have one table being inserted from 6 tables.
>
> Thanks genius.
>
> On 9/28/05, *Paul Drake* <bdbafh_at_gmail.com> wrote:
>
> On 9/28/05, *Ranko Mosic* <ranko.mosic_at_gmail.com > wrote:
>
> Hi,
>
> requirement:
>
> - input parameters are codes p_cd1, p_cd2, ...
>
> - for these codes I get descriptions ( select descr1 into v_descr1 from
>
>
>
> lkp_table1 where cd = p_cd1; select descr2 into v_descr2 from lkp_table2 where
>
>
>
> cd = p_cd2 etc )
>
> - check if table t has records where t.descr1 = v_descr1
>
> and t.descr2 = v_descr2 and on and on ....;
>
> - if row exists return primary key;
>
> - if not then insert.
>
>
> What is the best way of doing it ( simplest ) ?
>
>
>
>
> Regards, Ranko.
>
>
>
> Ranko,
>
> "Simplest way" is to solicit opinions without using a search engine or
> checking the documentation.
> Its also usually "simplest" to leverage the existing provided
> functionality, rather than writing your own routines, error handling, etc.
>
> A search of "oracle 10.1 upsert" in google.com <http://google.com/> + "I'm
> feeling lucky" produced this for me.
> Perhaps you might get lucky too.
>
> Paul
>
> http://www.psoug.org/reference/merge.html
>
> MERGE <hint> INTO <table_name>
> USING <table_view_or_query>
> ON (<condition>)
> WHEN MATCHED THEN <update_clause>
> WHEN NOT MATCHED THEN <insert_clause>;
>
>

--
http://www.freelists.org/webpage/oracle-l