RE: Where is the problem in this INSERT statement?

Try making things easier using brackets.

In the query with the SELECT DISTINCT in the second query, where do you put the closing bracket ? If you put it before the WHERE clause, the outcome would obviously be different...

HTH, Remco

> ----------
> From: Bala Muru[SMTP:muru321_at_yahoo.com]
> Sent: maandag 4 december 2000 3:30
> To: Multiple recipients of list ORACLE-L
> Subject: Where is the problem in this INSERT statement?
>
> Hello Lists,
>
> Should there a difference between the following two
> INSERT statements?
> 1)INSERT INTO <table1>
> SELECT DISTINCT * FROM <table2>
> 2)INSERT INTO <table1>
> SELECT DISTINCT * FROM
> (SELECT DISTINCT * FROM <table2>)
>
> The first one inserts more records than the second.
> The second one seems to be correct. To me both should
> return same number of records, am I wrong somewhere?
>
> Any help would be highly appreciated.
>
> Thanks in advance.
> Muru
>
> A realtime example is given below:
>
> INSERT INTO resale_psrc
> {In the second query add SELECT DISTINCT * FROM here)
> SELECT DISTINCT
> b.market_area_id market_area_id,
> b.package_id ci_code,
> b.package_type_code ci_type,
> c.charge_code rate_code,
> 'W' charge_unit_type,
> 'Resale Generic' ps_name,
> '$' increment_type,
> 'Resale Generic' rate_name,
> c.amount amount,
> TO_DATE('01/01/1996','MM/DD/YYYY') date1
> TO_DATE('01/01/2099','MM/DD/YYYY') date2
> 'L' ps_local_non_local,
> '$' ps_applicator_type,
> d.charge_type_code
> FROM
> package_info@"lrds.world" b,
> package_charge_info@"lrds.world" c,
> cis_charge_code_ref_t@"lrds.world" d
> WHERE
> b.market_area_id=c.market_area_id AND
> b.package_id=c.package_id AND
> c.charge_code = d.charge_code
> /
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Shopping - Thousands of Stores. Millions of Products.
> http://shopping.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Bala Muru
> INET: muru321_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Mon Dec 04 2000 - 03:48:44 CST