Home » SQL & PL/SQL » SQL & PL/SQL » grouping query with an update
grouping query with an update [message #439725] Tue, 19 January 2010 04:39 Go to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
Take for example this TRANS table, it has these fields, ACCOUNTID, CURRENCY, AMT.
Example data

7468543 GBP 54.60
3947638 USD 65.00
7468543 GBP 10.00

What i need to do is group the data from the above TRANS table and update an amount field in another table called TRANS_PROCESSED

TRANS_PROCESSED contains these fields: TRANS_P_ID, AMOUNT

TRANS_P_ID is made up of the users ACCOUNT_ID~CURRENCY so it would be like this 7468543~GBP or 3947638~USD

but how do i update the amounts in the TRANS_PROCESSED table?

so for TRANS_P_ID 7468543~GBP whatever the amount is, it should be the old amount + 64.60

I know how to do the grouping like this

        SELECT ACCOUNTID || '~' || CURRENCY, SUM(AMT)
        INTO t_account_id, t_amt
        FROM TRANS
        GROUP BY ACCOUNTID || '~' || CURRENCY;
        


But now i need to somehow update amount field with the necessary sum(amt) and trans_p_id
Re: grouping query with an update [message #439731 is a reply to message #439725] Tue, 19 January 2010 05:05 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
You need to use a correlated sub-query
UPDATE table1 a
SET col = (SELECT sum(col)
           FROM table2
           WHERE col1 = a.col1
           .....)
WHERE ....
Re: grouping query with an update [message #439742 is a reply to message #439731] Tue, 19 January 2010 05:22 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
        UPDATE TRANS_PROCESSED
        SET AMOUNT =
                    (SELECT ACCOUNTID || '~' || CURRENCY, SUM(AMT)
                    FROM TRANS
                    GROUP BY ACCOUNTID || '~' || CURRENCY)
        WHERE TRANS_P_ID = .....


Thanks, but how would i get the TRANS_P_ID as the 'ACCOUNTID || '~' || CURRENCY' is inside the brackets?

Also, can we do SET AMOUNT = AMOUNT + (SELECT ...) - so we can just keep adding to the amount?
Re: grouping query with an update [message #439745 is a reply to message #439742] Tue, 19 January 2010 05:27 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ This is not a CORRELATED subquery as this latter is correlated to nothing of the outer. See cookiemonster's example it gives you the syntax.

2/ Why do you use this concatenation?

Regards
Michel

[Updated on: Tue, 19 January 2010 05:28]

Report message to a moderator

Re: grouping query with an update [message #439752 is a reply to message #439745] Tue, 19 January 2010 05:37 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
Need to use concat because the TRANS_P_ID is made up of the ACCOUNTID || '~' || CURRENCY in the other table

so the id is like 12345~GBP
Re: grouping query with an update [message #439753 is a reply to message #439752] Tue, 19 January 2010 05:42 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Need to use concat because the TRANS_P_ID is made up of the ACCOUNTID || '~' || CURRENCY in the other table

This breaks the 1NF.

Regards
Michel
Re: grouping query with an update [message #439754 is a reply to message #439725] Tue, 19 January 2010 05:46 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
As I mentioned in a previous thread I wouldn't store the acount_id and currency concatenated in the other table, just use two columns. If the users want to see it in that format write code to display it to them like that but don't store it that way as it'll make all sorts of simple sql complicated and stop you using things like foreign keys.

As for this query - you don't need a group by. The correlated query will select the sum of the amount for a single group because the where clause of the sub-query will specify a single group. You use group by when selecting more than one group simultaneously so you don't need it here.

Re: grouping query with an update [message #439756 is a reply to message #439754] Tue, 19 January 2010 05:51 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
I need this to do an update for all possible TRANS_P_ID that are in the TRANS table so if the TRANS table had this data as i said in the 1st post

7468543 GBP 54.60
3947638 USD 65.00
7468543 GBP 10.00

this would actually do 2 updates

update TRANS_PROCESSED SET AMOUNT = 64.60 WHERE TRANS_P_ID = '7468543~GBP'

update TRANS_PROCESSED SET AMOUNT = 65.00 WHERE TRANS_P_ID = '3947638~USD'

would that correlarted subquery cater for this? This trans table is like a temporary table that hold all the data, i process it, update stuff in TRANS_PROCESSED and then wipe the TRANS table

[Updated on: Tue, 19 January 2010 05:52]

Report message to a moderator

Re: grouping query with an update [message #439757 is a reply to message #439725] Tue, 19 January 2010 05:53 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
And a correlated sub-query will do that.
Just try it.
Re: grouping query with an update [message #439758 is a reply to message #439757] Tue, 19 January 2010 06:00 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
But how to I specify the WHERE clause?

        UPDATE INTEGRATION.INT_BLAH_TEST a
        SET AMT = (SELECT SUM(AMT) 
                   FROM INTEGRATION.CASH_TRANS 
                   WHERE a.CASH_BAL_ID = ACCOUNT_ID || '~' || TXN_CCY)
        WHERE 

[Updated on: Tue, 19 January 2010 06:27] by Moderator

Report message to a moderator

Re: grouping query with an update [message #439766 is a reply to message #439758] Tue, 19 January 2010 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
WHERE TRANS_P_ID IN ('7468543~GBP','3947638~USD')

Isn't this what you want?

Regards
Michel
Re: grouping query with an update [message #439768 is a reply to message #439766] Tue, 19 January 2010 06:31 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
Michel Cadot wrote on Tue, 19 January 2010 06:28
WHERE TRANS_P_ID IN ('7468543~GBP','3947638~USD')

Isn't this what you want?

Regards
Michel


Yes but the values can't be hardcoded, they are the ACCOUNTID and CURRENCY combinations from the other table
Re: grouping query with an update [message #439770 is a reply to message #439768] Tue, 19 January 2010 06:35 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which other table?
The one that is updated or the one that is selected in the subquery?

Regards
Michel
Re: grouping query with an update [message #439772 is a reply to message #439770] Tue, 19 January 2010 06:39 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
Michel Cadot wrote on Tue, 19 January 2010 06:35
Which other table?
The one that is updated or the one that is selected in the subquery?

Regards
Michel


The one that is selected in the sub query
Re: grouping query with an update [message #439773 is a reply to message #439772] Tue, 19 January 2010 06:42 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
WHERE EXISTS (SELECT null FROM ... with the same correlation than the other subquery)

Regards
Michel

[Updated on: Tue, 19 January 2010 06:43]

Report message to a moderator

Re: grouping query with an update [message #439775 is a reply to message #439773] Tue, 19 January 2010 06:45 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
Michel Cadot wrote on Tue, 19 January 2010 06:42
WHERE EXISTS (SELECT null FROM ... with the same correlation than the other subquery)

Regards
Michel


Ah, cool!
So something like this?

        UPDATE INTEGRATION.INT_BLAH_TEST a
        SET AMT = (SELECT SUM(AMT) FROM INTEGRATION.CASH_TRANS WHERE a.CASH_BAL_ID = ACCOUNT_ID || '~' || TXN_CCY)
        WHERE EXISTS (SELECT CASH_BAL_ID FROM INTEGRATION.INT_BLAH_TEST WHERE a.CASH_BAL_ID = ACCOUNT_ID || '~' || TXN_CCY);


Seems like alot of stuff being repeated though

edit * how do i use the correlation from the first sub query?

[Updated on: Tue, 19 January 2010 06:47]

Report message to a moderator

Re: grouping query with an update [message #439776 is a reply to message #439725] Tue, 19 January 2010 06:50 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Pretty sure that should be:
UPDATE INTEGRATION.INT_BLAH_TEST a
        SET AMT = (SELECT SUM(AMT) FROM INTEGRATION.CASH_TRANS WHERE a.CASH_BAL_ID = ACCOUNT_ID || '~' || TXN_CCY)
        WHERE EXISTS (SELECT null FROM INTEGRATION.CASH_TRANS WHERE a.CASH_BAL_ID = ACCOUNT_ID || '~' || TXN_CCY);

Re: grouping query with an update [message #439778 is a reply to message #439776] Tue, 19 January 2010 06:52 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
cookiemonster wrote on Tue, 19 January 2010 06:50
Pretty sure that should be:
UPDATE INTEGRATION.INT_BLAH_TEST a
        SET AMT = (SELECT SUM(AMT) FROM INTEGRATION.CASH_TRANS WHERE a.CASH_BAL_ID = ACCOUNT_ID || '~' || TXN_CCY)
        WHERE EXISTS (SELECT null FROM INTEGRATION.CASH_TRANS WHERE a.CASH_BAL_ID = ACCOUNT_ID || '~' || TXN_CCY);



Hmm, I don't quite understand the 'select null..' part?
Re: grouping query with an update [message #439779 is a reply to message #439725] Tue, 19 January 2010 06:54 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
Ok it makes sense, we don't specifically need any fields, so we just select null. so would this execute 1 update query for every accountid/ccy combination from the trans table?
Re: grouping query with an update [message #439780 is a reply to message #439725] Tue, 19 January 2010 06:55 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
With an EXISTS (unlike an IN) it doesn't make any difference what columns you select in the sub-query, so you might as well not select any.
I was mainly correcting the table name in the 2nd sub-query mind.
Re: grouping query with an update [message #439781 is a reply to message #439779] Tue, 19 January 2010 06:58 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
pyscho wrote on Tue, 19 January 2010 12:54
Ok it makes sense, we don't specifically need any fields, so we just select null. so would this execute 1 update query for every accountid/ccy combination from the trans table?


More accurate to say it'll do a single update that modifies every row in INT_BLAH_TEST that has a mtching combination of accountid/ccy in CASH_TRANS table, and for every row updated it'll issue a sub-query against CASH_TRANS for that combination.
Re: grouping query with an update [message #439806 is a reply to message #439781] Tue, 19 January 2010 08:43 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
Thanks alot guys, really appreciate it. Also to add the new amount onto bthe existing amount, can we just use the + operator as usual?

        UPDATE INTEGRATION.INT_BLAH_TEST a
        SET AMT = AMT + (SELECT SUM(AMT) FROM INTEGRATION.CASH_TRANS WHERE a.CASH_BAL_ID = ACCOUNT_ID || '~' || TXN_CCY)
        WHERE EXISTS (SELECT NULL FROM INTEGRATION.CASH_TRANS WHERE a.CASH_BAL_ID = ACCOUNT_ID || '~' || TXN_CCY);
Re: grouping query with an update [message #439807 is a reply to message #439806] Tue, 19 January 2010 08:47 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
UPDATE INTEGRATION.INT_BLAH_TEST a
        SET AMT = (SELECT a.AMT + SUM(AMT) 
                   FROM INTEGRATION.CASH_TRANS 
                   WHERE a.CASH_BAL_ID = ACCOUNT_ID || '~' || TXN_CCY)
        WHERE EXISTS (SELECT NULL 
                      FROM INTEGRATION.CASH_TRANS 
                      WHERE a.CASH_BAL_ID = ACCOUNT_ID || '~' || TXN_CCY);

Formating query is not just putting code tags but also splitting the lines so that it is easier to read.

Regards
Michel

[Updated on: Tue, 19 January 2010 08:48]

Report message to a moderator

Re: grouping query with an update [message #439810 is a reply to message #439807] Tue, 19 January 2010 09:04 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
Thanks bud.

    PROCEDURE PROCESS_BATCH IS
    BEGIN
    
        UPDATE INTEGRATION.INT_BLAH_TEST a
                SET AMT = (SELECT a.AMT + SUM(AMT) 
                           FROM INTEGRATION.CASH_TRANS 
                           WHERE a.CASH_BAL_ID = ACCOUNT_ID || '~' || TXN_CCY)
                WHERE EXISTS (SELECT NULL
                              FROM INTEGRATION.CASH_TRANS 
                              WHERE a.CASH_BAL_ID = ACCOUNT_ID || '~' || TXN_CCY);

        EXECUTE IMMEDIATE 'TRUNCATE TABLE INTEGRATION.CASH_TRANS';

    END PROCESS_BATCH;


Is there now a way i can add to the above query, to do an insert if the cash_bal_id doesn't exist in INTEGRATION.INT_BLAH_TEST
Re: grouping query with an update [message #439813 is a reply to message #439725] Tue, 19 January 2010 09:18 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Look up merge
Re: grouping query with an update [message #439818 is a reply to message #439810] Tue, 19 January 2010 09:37 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With the link: MERGE.
It is even easier, no EXISTS just the subquery in the USING clause, the correlation in ON clause and then fill the values you want when the row exists (WHEN MATCHED) or not (WHEN NOT MATCHED).

Regards
Michel

[Updated on: Tue, 19 January 2010 09:38]

Report message to a moderator

Re: grouping query with an update [message #439819 is a reply to message #439818] Tue, 19 January 2010 09:43 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
Thanks guys im having some problems with this

        MERGE INTO INTEGRATION.INT_BLAH_TEST t
        USING DUAL
        ON t.CASH_BAL_ID = ACCOUNT_ID || '~' || TXN_CCY
        WHEN MATCHED THEN
            UPDATE INTEGRATION.INT_BLAH_TEST a
                    SET AMT = (SELECT a.AMT + SUM(AMT) 
                               FROM INTEGRATION.CASH_TRANS 
                               WHERE a.CASH_BAL_ID = ACCOUNT_ID || '~' || TXN_CCY)
                    WHERE EXISTS (SELECT NULL
                                  FROM INTEGRATION.CASH_TRANS 
                                  WHERE a.CASH_BAL_ID = ACCOUNT_ID || '~' || TXN_CCY);
        WHEN NOT MATCHED THEN
            -- insert here
            NULL;


I'm not sure how to fix the merge on ON t.CASH_BAL_ID = ACCOUNT_ID || '~' || TXN_CCY
Re: grouping query with an update [message #439823 is a reply to message #439819] Tue, 19 January 2010 09:52 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said, the subquery should be in the USING clause (without the a.AMT nor the correlation, of course) and there is no more EXISTS.

Regards
Michel

[Updated on: Tue, 19 January 2010 09:54]

Report message to a moderator

Re: grouping query with an update [message #439827 is a reply to message #439823] Tue, 19 January 2010 10:05 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
Michel Cadot wrote on Tue, 19 January 2010 09:52
As I said, the subquery should be in the USING clause (without the a.AMT nor the correlation, of course) and there is no more EXISTS.

Regards
Michel


        MERGE INTO INTEGRATION.INT_BLAH_TEST A
        USING (SELECT SUM(AMT) FROM INTEGRATION.CASH_TRANS GROUP BY ACCOUNT_ID || '~' || TXN_CCY) B
        ON (A.CASH_BAL_ID = B.ACCOUNT_ID || '~' || B.TXN_CCY)
        WHEN MATCHED THEN
            UPDATE 
            SET AMT = (SELECT a.AMT + SUM(AMT) 
                       FROM INTEGRATION.CASH_TRANS 
                       WHERE a.CASH_BAL_ID = ACCOUNT_ID || '~' || TXN_CCY)
        WHEN NOT MATCHED THEN
            NULL;


in the NOT_MATCHED, why doesnt it let me call my own insert_row() function - it keeps asking me to define the keyword 'insert'..
Re: grouping query with an update [message #439828 is a reply to message #439725] Tue, 19 January 2010 10:18 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Because that's how it's designed to work?
Re: grouping query with an update [message #439829 is a reply to message #439828] Tue, 19 January 2010 10:22 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
Hmm is is there no other way to make use of my insert_row() function? i dnt want to have to repeat code in the same package

[Updated on: Tue, 19 January 2010 10:25]

Report message to a moderator

Re: grouping query with an update [message #439838 is a reply to message #439829] Tue, 19 January 2010 11:12 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No you have to put the INSERT in the MERGE, then you don't need your insert_row function. Wink

In addition, in MERGE you have to name MAX() (give an alias) in the second query to use it in WHEN MATCHED part.

Regards
Michel
Re: grouping query with an update [message #439933 is a reply to message #439838] Wed, 20 January 2010 04:46 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
        MERGE INTO INTEGRATION.INT_BLAH_TEST A
        USING (SELECT SUM(AMT) FROM INTEGRATION.CASH_TRANS GROUP BY ACCOUNT_ID || '~' || TXN_CCY) B
        ON (A.CASH_BAL_ID = B.ACCOUNT_ID || '~' || B.TXN_CCY)
        WHEN MATCHED THEN
            UPDATE 
            SET AMT = (SELECT a.AMT + SUM(AMT) 
                       FROM INTEGRATION.CASH_TRANS 
                       WHERE a.CASH_BAL_ID = ACCOUNT_ID || '~' || TXN_CCY)
        WHEN NOT MATCHED THEN
                    INSERT VALUES (B.ACCOUNT_ID || '~' || B.TXN_CCY, 
                                   B.LTY_ID,
                                   B.CLIENT_ID,
                                   B.TXN_CCY, 
                                   SELECT SUM(B.AMT) FROM INTEGRATION.CASH_TRANS WHERE ACCOUNT_ID=B.ACCOUNT_ID AND TXN_CCY=B.TXN_CCY,
                                   '0');


Can I not do a select in a merge query? i need to get the SUM(AMT) if i need to do an insert..
Re: grouping query with an update [message #439939 is a reply to message #439933] Wed, 20 January 2010 05:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You look like you're still missing the point of MERGE a bit.

The idea is that you take a table/query (B in your code) and compare its result set with another table (A in your code).
If the row from B already exists in A then A is updated in a manner of your chosing.
If the row from B doesn't exist in A then you can insert a row into A using the values from B.


To make your insert work, you need to include the columns ACCOUNT_ID, TXN_CCY, LTY_ID, B.CLIENT_ID in the list of columns returned by your query B.

Once you've done that, you can just use the amount from B in your insert statement.
Re: grouping query with an update [message #439941 is a reply to message #439939] Wed, 20 January 2010 05:15 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
        MERGE INTO INTEGRATION.INT_BLAH_TEST A
        USING (SELECT ACCOUNT_ID, TXN_CCY, LTY_ID, CLIENT_ID, SUM(AMT) FROM INTEGRATION.CASH_TRANS GROUP BY ACCOUNT_ID || '~' || TXN_CCY) B
        ON (A.CASH_BAL_ID = B.ACCOUNT_ID || '~' || B.TXN_CCY)
        WHEN MATCHED THEN
            UPDATE 
            SET AMT = (SELECT a.AMT + SUM(AMT) FROM INTEGRATION.CASH_TRANS)
        WHEN NOT MATCHED THEN
                    INSERT VALUES (B.ACCOUNT_ID || '~' || B.TXN_CCY, 
                                   B.LTY_ID,
                                   B.CLIENT_ID,
                                   B.TXN_CCY, 
                                   SUM(AMT),
                                   SUM(AMT));


But I need to grab the SUM(AMT) of all cash_trans matching the particular ACCOUNT_ID || '~' || TXN_CCY id and insert this

it now errors on the SUM(AMT) in insert saying

group function is not allowed here

[Updated on: Wed, 20 January 2010 05:18]

Report message to a moderator

Re: grouping query with an update [message #439948 is a reply to message #439941] Wed, 20 January 2010 05:38 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
In addition, in MERGE you have to name MAX() (give an alias) in the second query to use it in WHEN MATCHED part.

And now also in WHEN NOT MATCHED part.

The UPDATE is not a complete UPDATE you already made the select part in MERGE second query, the purpose is to use the columns that have already been computed in the 2 queries.

Regards
Michel
Re: grouping query with an update [message #440138 is a reply to message #439948] Thu, 21 January 2010 05:19 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
guys i have tried to work this out but still unable to. im not sure how to get the sum(amt) that i have computed, in the 'insert' part (NOT MATCHED)
Re: grouping query with an update [message #440140 is a reply to message #440138] Thu, 21 January 2010 05:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Use an alias for the calculated field in the original query and refer to that.

I find I have to warn you for what you are about to do: it is apparent that this solution is somewhat beyond your level of knowledge of SQL and PL/SQL.
Keep in mind that, once you actually do implement this in your code and you don't REALLY understand how it works, you will be in a lot of trouble when a bug arises.
Not understanding your own code is a developer's nightmare...

[Updated on: Thu, 21 January 2010 05:30]

Report message to a moderator

Re: grouping query with an update [message #440151 is a reply to message #440140] Thu, 21 January 2010 05:56 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
I've made some progress

        MERGE INTO INTEGRATION.INT_BLAH_TEST CASHBAL
        USING ( SELECT ACCOUNT_ID || '~' || TXN_CCY AS CASH_BAL_ID, 
                      LTY_ID, 
                      CLIENT_ID, 
                      TXN_CCY AS LEDGER_CCY, 
                      SUM(AMT) AS TOTAL_BALANCE_FMT 
                FROM CASH_TRANS 
                GROUP BY ACCOUNT_ID || '~' || TXN_CCY, 
                        ACCOUNT_ID, 
                        TXN_CCY, 
                        LTY_ID, 
                        CLIENT_ID) CASHTRANS
        ON (CASHBAL.CASH_BAL_ID = CASHTRANS.CASH_BAL_ID)
        WHEN MATCHED THEN
              UPDATE 
              SET BALANCE_FMT = (CASHBAL.BALANCE_FMT + CASHTRANS.TOTAL_BALANCE_FMT)
        WHEN NOT MATCHED THEN
              INSERT (CASHBAL.CASH_BAL_ID, CASHBAL.LTY_ID, CASHBAL.CLIENT_ID, CASHBAL.LEDGER_CCY, CASHBAL.BALANCE_FMT)
              VALUES (CASHTRANS.CASH_BAL_ID, CASHTRANS.LTY_ID, CASHTRANS.CLIENT_ID, CASHTRANS.LEDGER_CCY, CASHTRANS.TOTAL_BALANCE_FMT);


It errors on the last line (VALUES..)

CASHTRANS.LTY_ID

ORA-01722: invalid number

any ideas guys?
Re: grouping query with an update [message #440152 is a reply to message #440151] Thu, 21 January 2010 06:01 Go to previous messageGo to previous message
pyscho
Messages: 134
Registered: December 2009
Senior Member
Think i solved it, was just a data type mismatch Very Happy
Previous Topic: Exception handling
Next Topic: problem with my insert query
Goto Forum:
  


Current Time: Fri Sep 30 00:29:58 CDT 2016

Total time taken to generate the page: 0.12598 seconds