Merge Columns when Column in Master-/PK-Row is NULL

Question

Post reply

Merge Columns when Column in Master-/PK-Row is NULL

info 58414

Hall of Fame

Points: 3386
More actions
August 31, 2019 at 10:21 am

#3676010
Hi,
I have a master row where OrderType is NOT NULL (Bold Row)
Problem: When on column in MasterRow is empty, then take the value from a neighbor line (same PK), update the NULL-value in the master line:
Query: Is Coalesce the right way to go over all rows and columns? There are max. 3 lines per PK.
=> If ISNULL (Master.Column) THEN take the first NOT NULL value from Neighbor lines.

order;orderType;Period;Amount;Unit;Group;incentive
441001;ONL;;0;;99;
441001;;QUATER;1;ST;;
441001;;;0;;;TV
Regards and Thanks
Nicole 🙂
- This topic was modified 4 years, 7 months ago by info 58414.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18257 More actions · Answer 1

I think you could do it with LEAD or LAG on two columns and a COALESCE

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

My take on this would be to find and fix the code that's making this mess and then make the ORDER TYPE column NOT NULL. Actually, the best thing to do would be to have an "Order_Header" table where the ORDER TYPE only needs to be noted once and then store the details in a separate "Order_Detail" table to store the individual line items involved in the order.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Burrows SSC Guru Points: 65138 More actions · Answer 3

Jeff Moden wrote:

Actually, the best thing to do would be to have an "Order_Header" table where the ORDER TYPE only needs to be noted once and then store the details in a separate "Order_Detail" table to store the individual line items involved in the order.

Glad I'm still doing it right 😉

Far away is close at hand in the images of elsewhere.
Anon.