Fill missing cell entries from previous rows
I have a table with transactions.
The columns are:
trxid,date,payee,category,amount,split
trxid is sequential transaction ID although with gaps here and there.
Split is either blank or S. S is for when a row is the sum of following rows.
The problem is that the row which has S has date and the payee and the amount, while the following component transactions only have the amounts but no date or payee.
What I want to do is go through the table and fill in the empty date and payee where missing with the date and payee of the preceding row with the S entry in the split column.
Example
| trxid | Date | Payee | Category | Amount | Split |
| 1 | 01/08/2000 | Tesco | Shopping | -10 | S |
| 2 | Shopping | -8 | |||
| 3 | Household | -2 | |||
| 4 | 05/09/2000 | BP | Petrol | -100 | |
| 5 | 06/09/2000 | Brady | Clothing | -55 | |
| 8 | 10/10/2000 | Argos | Laptop | -400 | S |
| 9 | Laptop | -329.99 | |||
| 10 | Toys | -70.01 | |||
| 15 | 12/10/2000 | Maplins | Electronics | -15.99 | |
| 16 | 15/10/2000 | HMRC | Stolen income | -200 | |
| 17 | 15/10/2000 | Employer | Salary | 1000 |
So as you can see, trxid 2 and 3 are missing date and payee and they need to be the same date and payee as trxid 1.
Same things with trxid 9 and 10 inheriting date and payee of trxid 8.
Hope this is clear.
I have tried and failed so your help will be much appreciated.
I made the assumption trxid was constrained to be unique.
Can you post ddl for your real table, indexes, and your db version?
ASKER
( "TRXID" NUMBER(*,0) NOT NULL ENABLE,
"TDATE" DATE,
"NUM" VARCHAR2(15 BYTE),
"PAYEE" VARCHAR2(35 BYTE),
"CATEGORY" VARCHAR2(60 BYTE),
"MEMO" VARCHAR2(60 BYTE),
"AMOUNT" NUMBER(10,2),
"RECONCILE" CHAR(1 BYTE),
"BALANCE" NUMBER(10,2),
"SPLIT" CHAR(1 BYTE),
"ACCTNO" CHAR(5 BYTE) NOT NULL ENABLE,
CONSTRAINT "PK_TRANSACTIONS" PRIMARY KEY ("TRXID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_ACCOUNT_TRANSACTIONS" FOREIGN KEY ("ACCTNO")
REFERENCES "DBUSER"."ACCOUNTS" ("ACCTID") ON DELETE CASCADE DISABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
For example trxid=2, I assumed I could trust there would only be one trxid=1 and I could use the values from that row.
If there can be two or more rows for trxid=1, then how I do know which row I can use to populate the trxid=2 values?
And if there are two rows for a particular trxid and one has null date/payee but the other does not, what is the rule then?
What if they both have nulls?
ASKER
Apologies
ASKER
Regarding why your orginal suggestion was causing error, I've looked around.
Does this make sense to you as the cause of the failure?
"You're trying to update one row in T. But joining it to two rows in T1.
You can't do this when updating a subquery. Each row you're changing must appear exactly once in the results of the query. "
I'm going to guess it's because the query is valid but the optimizer of your optimizer version couldn't figure that out, so it defaulted to the safer position of assuming it was not rather than allow it and possibly create invalid results.
I tested on a 21c db so it's the latest and greatest.
ASKER
The first part works like a charm. But the UPDATE SQL fails with:
SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
01779. 00000 - "cannot modify a column which maps to a non key-preserved table"
*Cause: An attempt was made to insert or update columns of a join view which
map to a non-key-preserved table.
*Action: Modify the underlying base tables directly.