<zzzzzz45_at_hotmail.com> schreef in bericht
news:1184234555.246650.321990_at_w3g2000hsg.googlegroups.com...
> On Jul 10, 11:44 pm, "Shakespeare" <what..._at_xs4all.nl> wrote:
>> "sybrandb" <sybra..._at_gmail.com> schreef in
>> berichtnews:1184078950.759479.319700_at_p39g2000hse.googlegroups.com...
>>
>>
>>
>> > On Jul 10, 4:18 pm, zzzzz..._at_hotmail.com wrote:
>> >> On Jul 10, 2:55 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>>
>> >> > On Jul 10, 9:41 am, zzzzz..._at_hotmail.com wrote:
>>
>> >> > > There are quite a few tables involved and views also so all the
>> >> > > DDLs
>> >> > > would be a lot of information.
>>
>> >> > > The update is below. Please note the two inner selects are
>> >> > > identical
>> >> > > (bar the fields returned and the last line of the first select
>> >> > > which
>> >> > > ties the inner records to the records to be updated in
>> >> > > "qlimUpdate").
>> >> > > This update should only update records in svc_quote_line_import
>> >> > > where
>> >> > > matches are found in associated tables and leave all other records
>> >> > > alone. This is what it does on 9i.
>>
>> >> > > The error is ORA-01407: cannot update
>> >> > > ("NETPRICET"."SVC_QUOTE_LINE_IMPORT"."QUOTE_LINE_IMPORT_ID") to
>> >> > > NULL.
>>
>> >> > > Please note QUOTE_LINE_IMPORT_ID is being set to 1 in the update.
>> >> > > The
>> >> > > only way it would be set to null is if the second inner select
>> >> > > returns
>> >> > > more records than the first inner select.
>>
>> >> > > When I run both selects on their own both return 49 records (they
>> >> > > are
>> >> > > identical after all). When the update runs the second select seems
>> >> > > to
>> >> > > "find" about 10 more records that the first select doesn't return.
>> >> > > I
>> >> > > can't figure out why. I've confirmed it is returning extra records
>> >> > > by
>> >> > > removing non nullable fields from the update to see what it
>> >> > > actually
>> >> > > updates. It sets fields in these extra records to null
>>
>> >> > > update svc_quote_line_import qlimUpdate
>> >> > > set (DESCRIPTION, IS_IMPORTABLE, SERVICE_LINE_NUMBER,
>> >> > > COUNT_BASED_TYPE_ID, COUNT_BASED_TYPE_VALUE, ITEM_CATEGORY_ID,
>> >> > > part_import_source_id) =
>> >> > > (
>> >> > > Select distinct pm.description, 1,
>> >> > > prlp.SERVICELINENUMBER,
>> >> > > pm.ITEM_COUNT_NAME_ID, pm.ITEM_COUNT_VALUE, ic.ITEM_CATEGORY_ID, 2
>> >> > > From svc_quote_import qi,
>> >> > > svc_distrib_price_region dpr,
>> >> > > svc_quote_line_import qlim, svc_item_categories ic,
>> >> > > SVC_SLN_PRODUCT_LINE_GRP_MAP slnpl,
>> >> > > partmaster pm,
>> >> > > partregionlistprice prlp
>> >> > > where qi.quote_import_id =
>> >> > > p_QUOTE_IMPORT_ID
>> >> > > and qi.quote_import_id =
>> >> > > qlim.quote_import_id
>> >> > > and qlim.ordercode = prlp.ORDERCODE
>> >> > > and qi.DISTRIBUTORID = dpr.DISTRIBUTORID
>> >> > > and dpr.REGION_ID = trim(prlp.region_id)
>> >> > > and dpr.CURRENCYID = prlp.CURRENCY
>> >> > > and qlim.ordercode = pm.ordercode
>> >> > > and prlp.SERVICELINENUMBER =
>> >> > > slnpl.SERVICE_LINE_NUMBER (+)
>> >> > > and trunc(prlp.expdate) >= trunc(sysdate)
>> >> > > and trunc(prlp.msrp_effective_date) <=
>> >> > > sysdate
>> >> > > and trunc(prlp.msrp_expiration_date) >=
>> >> > > sysdate
>> >> > > and pm.portfolioid =
>> >> > > -- Data and Voice
>> >> > > and pm.designationflag =
>> >> > > ic.ITEM_CATEGORY_DESC (+)
>> >> > > and qlim.is_importable =
>> >> > > -- May already be done
>> >> > > and qlimUpdate.QUOTE_LINE_IMPORT_ID =
>> >> > > qlim.QUOTE_LINE_IMPORT_ID
>> >> > > )
>> >> > > where qlimUpdate.QUOTE_LINE_IMPORT_ID in
>> >> > > (
>> >> > > Select distinct qlim.QUOTE_LINE_IMPORT_ID
>> >> > > From svc_quote_import qi,
>> >> > > svc_distrib_price_region dpr,
>> >> > > svc_quote_line_import qlim, svc_item_categories ic,
>> >> > > SVC_SLN_PRODUCT_LINE_GRP_MAP slnpl,
>> >> > > partmaster pm,
>> >> > > partregionlistprice prlp
>> >> > > where qi.quote_import_id =
>> >> > > p_QUOTE_IMPORT_ID
>> >> > > and qi.quote_import_id =
>> >> > > qlim.quote_import_id
>> >> > > and qlim.ordercode = prlp.ORDERCODE
>> >> > > and qi.DISTRIBUTORID = dpr.DISTRIBUTORID
>> >> > > and dpr.REGION_ID = trim(prlp.region_id)
>> >> > > and dpr.CURRENCYID = prlp.CURRENCY
>> >> > > and qlim.ordercode = pm.ordercode
>> >> > > and prlp.SERVICELINENUMBER =
>> >> > > slnpl.SERVICE_LINE_NUMBER (+)
>> >> > > and trunc(prlp.expdate) >= trunc(sysdate)
>> >> > > and trunc(prlp.msrp_effective_date) <=
>> >> > > sysdate
>> >> > > and trunc(prlp.msrp_expiration_date) >=
>> >> > > sysdate
>> >> > > and pm.portfolioid =
>> >> > > -- Data and Voice
>> >> > > and pm.designationflag =
>> >> > > ic.ITEM_CATEGORY_DESC (+)
>> >> > > and qlim.is_importable =
>> >> > > -- May already be done
>> >> > > );
>>
>> >> > > What makes it stranger is the behaviour seems to be intermittent,
>> >> > > i.e.
>> >> > > sometimes for the same data it happily updates 49 records. Very
>> >> > > confusing
>>
>> >> > > TIA
>>
>> >> > What is the exact version of Oracle? 10.x.x.x
>>
>> >> > What does the explain plan show as to how the queries are being
>> >> > solved?
>>
>> >> > There have been bugs on various versions of 10g where wrong results
>> >> > could be returned by a query where a FBI was involved and where hash
>> >> > aggregation was used. If, this is a bug my guess is it is related
>> >> > to
>> >> > a problem with IN cluases. I do not know of one but it is likely to
>> >> > be version specific and perhaps platform specific as well.
>>
>> >> > Can you recognize any repeatable pattern about the data when the
>> >> > query
>> >> > fails as compared to when it works?
>>
>> >> > HTH -- Mark D Powell --
>>
>> >> Version is oracle Database 10g Enterprise Edition Release 10.2.0.3.0 -
>> >> 64bi
>>
>> >> I don't see a pattern. It is consistently works for a while then
>> >> consistently fails. I've identified the records the second select
>> >> should not return but when run as part of the Update does return. I've
>> >> verified they should not be returned from the select. I'm loath to say
>> >> it's an oracle bug as we've just gone to 10g so I'm new to 10g but the
>> >> SQL has been working on 9i for a couple of years.
>>
>> >> I feel if I re-engineer the update (perhaps first writing the source
>> >> records for the update into a table then doing the update) I can avoid
>> >> the issue. Problem then is that update syntax is used elsewhere so
>> >> perhaps I can't have confidence in it...
>>
>> >> Perhaps someone could suggest an alternative update syntax to update
>> >> one table from a set of tables where not all records that can be
>> >> updated will have a match in the set of tables and only records that
>> >> do have a match should get updated?- Hide quoted text -
>>
>> >> - Show quoted text -
>>
>> > The statement is incorrect anyway as anything between the () in an IN
>> > statement constitutes a SET, and SETS are unique *by definition*. So
>> > the distinct is redundant.
>> > However you probably have duplicates in that column in 'table1', which
>> > is why you are updating more records than you expect.
>>
>> > --
>> > Sybrand Bakker
>> > Senior Oracle DBA
>>
>> Since you noticethis statement working fine one time and failing another
>> time, my advice would be to check your cursor sharing settings, which may
>> be
>> set to forced. We have seen strange behaviour using this setting....
>> On the other hand: repeating tests would NEVER be exactly the same, since
>> you are using sysdate in your statement....
>>
>> Shakespeare
>> What's in a date?
>
> In investigating this I've found some behaviour that is just baffling
> - if anyone has some insight it would be appreciated.
>
> I've taken out the Select from the update that seemed to be returning
> more rows when run as part of an update than when run as a stand alone
> select. When I run this select like so:
>
> Select distinct qlim.QUOTE_LINE_IMPORT_ID, pm.description,
> prlp.SERVICELINENUMBER, pm.ITEM_COUNT_NAME_ID, pm.ITEM_COUNT_VALUE,
> ic.ITEM_CATEGORY_ID
> From svc_quote_import qi, svc_distrib_price_region dpr,
> svc_quote_line_import qlim, svc_item_categories ic,
> SVC_SLN_PRODUCT_LINE_GRP_MAP slnpl, partmaster pm,
> partregionlistprice prlp
> where qi.quote_import_id = 9776
> and qi.quote_import_id = qlim.quote_import_id
> and qlim.ordercode = prlp.ORDERCODE
> and qi.DISTRIBUTORID = dpr.DISTRIBUTORID
> and dpr.REGION_ID = trim(prlp.region_id)
> and dpr.CURRENCYID = prlp.CURRENCY
> and qlim.ordercode = pm.ordercode
> and prlp.SERVICELINENUMBER = slnpl.SERVICE_LINE_NUMBER (+)
> and trunc(prlp.expdate) >= trunc(sysdate)
> and trunc(prlp.msrp_effective_date) <= trunc(sysdate)
> and trunc(prlp.msrp_expiration_date) >= trunc(sysdate)
> and pm.portfolioid = 0
> and pm.designationflag = ic.ITEM_CATEGORY_DESC (+)
> and qlim.is_importable = 0
>
> I get 4 records.
>
> When I run the exact same Select but use it to insert into a table
> like so:
>
> insert into SVC_QTE_LINE_IMP_FILL
> Select distinct qlim.QUOTE_LINE_IMPORT_ID, pm.description,
> prlp.SERVICELINENUMBER, pm.ITEM_COUNT_NAME_ID, pm.ITEM_COUNT_VALUE,
> ic.ITEM_CATEGORY_ID
> From svc_quote_import qi, svc_distrib_price_region dpr,
> svc_quote_line_import qlim, svc_item_categories ic,
> SVC_SLN_PRODUCT_LINE_GRP_MAP slnpl, partmaster pm,
> partregionlistprice prlp
> where qi.quote_import_id = 9776
> and qi.quote_import_id = qlim.quote_import_id
> and qlim.ordercode = prlp.ORDERCODE
> and qi.DISTRIBUTORID = dpr.DISTRIBUTORID
> and dpr.REGION_ID = trim(prlp.region_id)
> and dpr.CURRENCYID = prlp.CURRENCY
> and qlim.ordercode = pm.ordercode
> and prlp.SERVICELINENUMBER = slnpl.SERVICE_LINE_NUMBER (+)
> and trunc(prlp.expdate) >= trunc(sysdate)
> and trunc(prlp.msrp_effective_date) <= trunc(sysdate)
> and trunc(prlp.msrp_expiration_date) >= trunc(sysdate)
> and pm.portfolioid = 0
> and pm.designationflag = ic.ITEM_CATEGORY_DESC (+)
> and qlim.is_importable = 0
>
> I get 18 rows inserted. Please note this is the exact same select.
> Frankly I'm at a loss. There must be an explanation but I can't see
> it.
>
> TIA,
> Mark
>
It may be quite some work to do so, but could you check
a) whether the inserted records are unique (should be due to the distinct,
but I'm not sure about distinct and null values....)
b) whether the inserted records meet your where-clause
c) if all inserted records are in the original tables?
To extend your test, select/insert the corresponding rowid's of your source
tables as well!
By the way: are the sources all tables? No materialized views or whatever?
Are you sure your tables aren't modified during testing due to other users
performing updates/inserts?
Be sure to truncate your target table before testing!
Shakespeare
What's in a row?
Received on Wed Jul 18 2007 - 16:56:19 CDT
