Home » SQL & PL/SQL » SQL & PL/SQL » Solved: Merge Error
Solved: Merge Error [message #204931] Wed, 22 November 2006 11:56 Go to next message
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 #204934 is a reply to message #204931] Wed, 22 November 2006 12:21 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Believe you need a WHEN NOT MATCHED clause.
Re: Merge Error [message #204935 is a reply to message #204934] Wed, 22 November 2006 12:30 Go to previous messageGo to next message
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: Merge Error [message #205030 is a reply to message #204935] Thu, 23 November 2006 01:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
A better reference would seem to be in order. Cool

In fairness, a Merge statement without one of the Matched clauses would just be an Insert or Update statement wearing a fancy suit.

What's your current statement and error message?
Re: Solved: Merge Error [message #205037 is a reply to message #204931] Thu, 23 November 2006 02:22 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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

Previous Topic: Integer Representation in oracle
Next Topic: connect by prior, how to get all the level even started from middle
Goto Forum:
  


Current Time: Tue Dec 06 06:50:05 CST 2016

Total time taken to generate the page: 0.43947 seconds