Avatar of Rich Olu
Rich OluFlag for United Kingdom of Great Britain and Northern Ireland asked on

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

 

trxidDatePayeeCategoryAmountSplit
101/08/2000TescoShopping-10S
2

Shopping-8
3

Household-2
405/09/2000BPPetrol-100
506/09/2000BradyClothing-55
810/10/2000ArgosLaptop-400S
9

Laptop-329.99
10

Toys-70.01
1512/10/2000MaplinsElectronics-15.99
1615/10/2000HMRCStolen income-200
1715/10/2000EmployerSalary1000


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.

Microsoft SQL Server* Oracle PL/SQLOracle DatabaseSQL

Avatar of undefined
Last Comment
Sean Stuber

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Rich Olu

Hi Sean,

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.


Sean Stuber

I tested both of them and they both worked in my test cases.

I made the assumption trxid was constrained to be unique.
Can you post ddl for your real table, indexes, and your db version?

ASKER
Rich Olu

CREATE TABLE "DBUSER"."TRANSACTIONS"
   (    "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" ;

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Sean Stuber

if trxid is not unique how do we know which rows go together?

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
Rich Olu

Sorry I edited the response. It is indeed unique.
Apologies
SOLUTION
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Rich Olu

Thanks Sean, it worked. Although it was taking ages so I split it to update the 2 fields separately. It still took a while.

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. "
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Sean Stuber

If trxid is unique then the join can't be to more than 1 row.

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.