Home » SQL & PL/SQL » SQL & PL/SQL » Solved: Merge Error
| Solved: Merge Error [message #204931] |
Wed, 22 November 2006 11:56  |
masijade
Messages: 7
Registered: November 2006
|
Junior Member |
|
|
Hello,
The following merge query is giving me a missing keyword error. Other than the fact that it uses the same fields (I changed the subquery to make it simple, but still valid) what might be wrong with this query
Merge Into TUKB101.T_VERSCH_PA_E O Using
(select Partner_ID, Mandant, Stichtag, Verschuldung_Verbund_STA, Verschuldung_Verbund_IRB from TUKB101.T_VERSCH_PA_E) S
On (O.Partner_ID = S.Partner_ID
And O.Mandant = S.Mandant
And O.Stichtag = S.Stichtag)
When Matched Then Update Set O.Verschuldung_Verbund_STA = S.Verschuldung_Verbund_STA,
O.Verschuldung_Verbund_IRB = S.Verschuldung_Verbund_IRB
[Updated on: Wed, 22 November 2006 12:31] Report message to a moderator |
|
|
|
|
|
| Re: Merge Error [message #204935 is a reply to message #204934] |
Wed, 22 November 2006 12:30  |
masijade
Messages: 7
Registered: November 2006
|
Junior Member |
|
|
Yeah, I just wanted to say that. I added a when not match insert .... statement.
It was just irking me, because the reference I have says that both the when matched and when not matched are optional.
It also says that you can enter "NULL" instead of an actual statement after the matched keyword.
But, both of the above statements are incorrect. If I do not have both a matched and not matched statement (and they cannot be "NULL") it gives a missing keyword error.
So, the reference did not match the reality and it just irked me for a few hours.
|
|
|
|
|
|
| Re: Solved: Merge Error [message #205037 is a reply to message #204931] |
Thu, 23 November 2006 02:22 |
masijade
Messages: 7
Registered: November 2006
|
Junior Member |
|
|
I have heard this before.
As far as the new statement I simply plastered a viable Insert onto the end that should never be executed.
My real problem, I guess, is I am not sure how to structure the Update command so that I don't have to use a merge.
What I need to do is to update two fields in the original table with the results of a subquery as follows:
Take information from two fields in the original table
Sum this information in two different variations using a third
field from the original table as an index into a third table
to get the grouping field for the sums from a fourth table
using another field from the third table as the index into the
fourth table.
Then, if the indexing produces multiple grouping values, use the
maximum sum value to use as the insert value.
Then, if the indexing does not produce any grouping values, use
the original value of the field that was to be summed as the
the insert value.
The entire subquery I defined and tested before hand, but had not a clue as to how to work it into an update statement, but it is exteremely easy to work it into a merge statement (as soon as I was able to figure out the true requirements for the matched/not matched parts).
So, as I mentioned in the first post, disregard the subquery that was included in the post, as it was simply an example to produce a working subquery statement without all the confusion that the actual subquery would have produced.
|
|
|
|
| Re: Solved: Merge Error [message #205044 is a reply to message #205037] |
Thu, 23 November 2006 02:41 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Well, you should just be able to do:UPDATE <table>
SET (col_1,col_2) = (SELECT value_2,value_2 FROM....)
WHERE <conditions to determine which rows get updated> |
|
|
|
| Re: Solved: Merge Error [message #205060 is a reply to message #205044] |
Thu, 23 November 2006 03:03 |
masijade
Messages: 7
Registered: November 2006
|
Junior Member |
|
|
That where clause is just the thing that I don't understand. All records will be updated, but how does it determine which of the rows from the subquery to update each original row of the table from, since the subquery returns only values that are determined from a grouping that has essentially nothing to do with the original table, at least not uniquely. With the merge I can have the subquery return a third field to use as the key, but in an update, I cannot. So, would the subquery esssentially get "executed" for every record in the table. If not, how does it determine which record from the subquery to use to update that record in the original table. I'm just not at all certain that a simple update would work properly or efficiently.
If you wish, or better said if you are willing to, I can post the entire subquery to you as a personal message and you can attempt to construct an update statement out of it, but I'm not expecting too much to come out of it. The merge works, I'm happy with it, and most of all, I am certain of its results, where I am not certain of the results or efficiency from an update statement.
|
|
|
|
| Re: Solved: Merge Error [message #205069 is a reply to message #205060] |
Thu, 23 November 2006 04:02 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Stick the update statement in this thread, and I'll try to take a look at it.
The Merge functionality is basically the same as an Update View.
If you're happy with the Merge, then I'd keep using it, but document it so that the next developer to look at the code doesn't spend too much time trying to work out what you're doing.
|
|
|
|
| Re: Solved: Merge Error [message #205152 is a reply to message #205069] |
Thu, 23 November 2006 09:13 |
masijade
Messages: 7
Registered: November 2006
|
Junior Member |
|
|
Here is the Merge Statement, if you're in the mood try to convert it to an update statement. And if you're really enthusiastic, you might try to improve the subquery (I'm sure you could have a field day on that):
Merge Into TUKB101.T_VERSCH_PA_E tgtInfo Using
(Select vp.Partner_ID as Partner_ID
,'00001' as Mandant
,to_date('2006-09-29', 'YYYY-MM-DD') as Stichtag
,nvl(max(sta.STA_Total), max(vp.Verschuldung_STA)) as Verschuldung_Verbund_STA
,nvl(max(irb.IRB_Total), max(vp.Verschuldung_IRB)) as Verschuldung_Verbund_IRB
From
(Select Partner_ID, Mandant, Stichtag, Verschuldung_STA, Verschuldung_IRB
From TUKB101.T_VERSCH_PA_E
Where Mandant = '00001'
And Stichtag = to_date('2006-09-29', 'YYYY-MM-DD')) vp
,(Select Partner_ID, PartnerVerbund_ID
From TUKB101.T_Z_PRT_PRTV_E
Where Mandant = '00001'
And Stichtag = to_date('2006-09-29', 'YYYY-MM-DD')) pzv
,(Select /*+ No_INDEX(a) NO_INDEX(b) NO_INDEX(c) */
b.PartnerVerbund_ID as PartnerVerbund_ID
,sum(a.Verschuldung_STA) as STA_Total
From
(Select Partner_ID, Verschuldung_STA
From TUKB101.T_VERSCH_PA_E
Where Mandant = '00001'
And Stichtag = to_date('2006-09-29', 'YYYY-MM-DD')) a
,(Select Partner_ID, PartnerVerbund_ID
From TUKB101.T_Z_PRT_PRTV_E
Where Mandant = '00001'
And Stichtag = to_date('2006-09-29', 'YYYY-MM-DD')) b
,(Select PartnerVerbund_ID
From TUKB101.T_PARTNERVER_E
Where Mandant = '00001'
And Stichtag = to_date('2006-09-29', 'YYYY-MM-DD')
And PartnerVerbundArt = 'KWG') c
Where a.Partner_ID = b.Partner_ID
And b.PartnerVerbund_ID = c.PartnerVerbund_ID
Group By b.PartnerVerbund_ID) sta
,(Select /*+ No_INDEX(d) NO_INDEX(e) NO_INDEX(f) */
e.PartnerVerbund_ID as PartnerVerbund_ID
,sum(d.Verschuldung_IRB) as IRB_Total
From
(Select Partner_ID, Verschuldung_IRB
From TUKB101.T_VERSCH_PA_E
Where Mandant = '00001'
And Stichtag = to_date('2006-09-29', 'YYYY-MM-DD')) d
,(Select Partner_ID, PartnerVerbund_ID
From TUKB101.T_Z_PRT_PRTV_E
Where Mandant = '00001'
And Stichtag = to_date('2006-09-29', 'YYYY-MM-DD')) e
,(Select PartnerVerbund_ID
From TUKB101.T_PARTNERVER_E
Where Mandant = '00001'
And Stichtag = to_date('2006-09-29', 'YYYY-MM-DD')
And PartnerVerbundArt = 'BON') f
Where d.Partner_ID = e.Partner_ID
And e.PartnerVerbund_ID = f.PartnerVerbund_ID
Group By e.PartnerVerbund_ID) irb
Where vp.Partner_ID = pzv.Partner_ID (+)
And pzv.PartnerVerbund_ID = sta.PartnerVerbund_ID (+)
And pzv.PartnerVerbund_ID = irb.PartnerVerbund_ID (+)
Group By vp.Partner_ID) srcInfo
On ( tgtInfo.Partner_ID = srcInfo.Partner_ID
And tgtInfo.Mandant = srcInfo.Mandant
And tgtInfo.Stichtag = srcInfo.Stichtag)
When Matched Then Update Set tgtInfo.Verschuldung_Verbund_STA = srcInfo.Verschuldung_Verbund_STA,
tgtInfo.Verschuldung_Verbund_IRB = srcInfo.Verschuldung_Verbund_IRB
When Not Matched Then Insert (tgtInfo.Partner_ID, tgtInfo.Mandant, tgtInfo.Stichtag, tgtInfo.Verschuldung_STA,
tgtInfo.Verschuldung_IRB, tgtInfo.Verschuldung_Verbund_STA, tgtInfo.Verschuldung_Verbund_IRB)
Values (srcInfo.Partner_ID, '00001', to_date('2006-09-29', 'YYYY-MM-DD'), srcInfo.Verschuldung_Verbund_STA,
srcInfo.Verschuldung_Verbund_IRB, srcInfo.Verschuldung_Verbund_STA, srcInfo.Verschuldung_Verbund_IRB)
The tables are partitioned on Mandant and Stichtag, and the other tables being referenced have indexes that don't really coordinate well with the index on the original table, which is the reason for the NO_INDEX hints.
[Updated on: Thu, 23 November 2006 09:14] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Tue Dec 03 20:36:54 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
