replace null values with the last value other than null

[gabydrdoom]

Hello!

how do I replace null values in a column with the last non-null value?

Ex:
Column Column
AB AB
null AB
CT CT
null CT
null CT
BH BH
null BH
null BH
SV SV
TM TM

Thank you!

 

[Mike Lewis]

What do you mean by the "last" value? The value in the previous field? Or in the previous record? Or in the last record in the table?

The answer is probably that you should use NVL(), but you will need to clarify the question.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[gabydrdoom]

I have a table called
code abr
---------------
789 AB
789 null
789 null
801 CT
801 null
801 null
812 BH
812 null
812 null
816 SV
817 TM

And I want to select it so that I can fill in the blanks with the previous non-null value:

code abr
---------------
789 AB
789 AB
789 AB
801 CT
801 CT
801 CT
812 BH
812 BH
812 BH
816 SV
817 TM

 

[gabydrdoom]

Mike Lewis (Programmer) - in the previous record

 

[Olaf Doschke]

In T-SQL you could use window functions and when a column is NULL could look at LAG(column).

But when you scan a table it's simple to do, so this is a classic non-SQL task.

Cautious: This solution changes the data, it doesn't just set NULL values in a query result, they are permanently replaced. If you don't want that, you need to build up a result cursor instead of modifying the table.

SELECT yourtable
prevABR = ''
SCAN
   IF ISNULL(abr)
      REPLACE abr WITH prevABR
   ELSE
      prevABR = abr
   ENDIF
ENDSCAN

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[GriffMG]

SELECT myTable
DO WHILE .not. EOF()
	m.code = code
	m.abbr = abbr
	skip
	DO WHILE .not. EOF() .and. code = m.code
		replace abbr WITH m.abbr
		SKIP
	ENDDO
ENDDO

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Olaf Doschke]

Yes, both will work for your sample data.

My code takes it literally and puts in previous non-null ABR value on following rows, regardless of code. Also keeps all non-null values as is.
Griff harmonizes all rows with the same code.

If you're only interested in the CODE/ABR pairs without NULL in ABR, that'll be possible with SQL:

SELECT DISTINCT CODE, ABR FROM yourtable WHERE NOT ABR IS NULL

Bye, Olaf.





Olaf Doschke Software Engineering

https://www.doschke.name

 

[atlopes]

Gabydrdoom,

If you have a code for the null values, you can UPDATE * FROM:

CREATE CURSOR SampleData (Code Numeric(3), Abr Char(2) NULL)

INSERT INTO SampleData VALUES (789, 'AB')
INSERT INTO SampleData VALUES (789, NULL)
INSERT INTO SampleData VALUES (789, NULL)
INSERT INTO SampleData VALUES (801, 'CT')
INSERT INTO SampleData VALUES (801, NULL)
INSERT INTO SampleData VALUES (801, NULL)
INSERT INTO SampleData VALUES (812, 'BH')
INSERT INTO SampleData VALUES (812, NULL)
INSERT INTO SampleData VALUES (812, NULL)
INSERT INTO SampleData VALUES (816, 'ST')
INSERT INTO SampleData VALUES (817, 'TM')

[highlight #FCE94F]UPDATE SampleData ;
	SET Abr = NN.Abr ;
	FROM (SELECT Code, Abr FROM SampleData WHERE Abr IS NOT NULL) AS NN ;
	WHERE SampleData.Code = NN.Code[/highlight]

BROWSE

 

[gabydrdoom]

OK, thanks again for all your help.