CODEWITHSUNDEEP
sqlhana
Tomi S.
Tomi S.

Reputation: 3

Add missing rows within a table

I need a hint please, in my table it can happen that positions of an order is not written to the next ID.

Let's look at the table:

Pos 2 is missing in ID 3

ID DOC POSI TOTAL
1 123 1 100
1 123 2 600
1 123 3 200
2 123 1 100
2 123 2 600
2 123 3 200
3 123 1 100
3 123 3 200

Is it possible to create a view using SQL that compares the individual IDs partitions with each other and appends the missing value from ID 2 to ID 3 as a row?

Maybe you have some keywords for me, if something like this is possible.

Upvotes: 0

Views: 1063

Answers (1)

Mathias Kemeter
Mathias Kemeter

Reputation: 1183

The hint would be: Use a join.

One way of approaching this is, that you select the key pairs that you expect and then left join the original table. Be conscious about the missing-value handling, since you have not specified in your question what should happen to those newly created entries.

Test Data

CREATE TABLE test (id INTEGER, doc INTEGER, posi INTEGER, total INTEGER);
INSERT INTO test VALUES (1, 123, 1, 100);
INSERT INTO test VALUES (1, 123, 2, 600);
INSERT INTO test VALUES (1, 123, 3, 200);
INSERT INTO test VALUES (2, 123, 1, 100);
INSERT INTO test VALUES (2, 123, 2, 600);
INSERT INTO test VALUES (2, 123, 3, 200);
INSERT INTO test VALUES (3, 123, 1, 100);
INSERT INTO test VALUES (3, 123, 3, 200);

The possible key combinations can be generated with a cross join:

SELECT DISTINCT a.id, b.posi 
FROM test a, test b

And now join the original table:

WITH expected_lines AS (
    SELECT DISTINCT a.id, b.posi 
    FROM test a, test b
)
SELECT el.id, el.posi, t.doc, t.total
FROM expected_lines el
LEFT JOIN test t ON el.id = t.id AND el.posi = t.posi

You did not describe further, what should happen with the now empty columns. As you may note DOC and TOTAL are null.

My educated guess would be, that you want to make DOC part of the key and assume a TOTAL of 0. If that's the case, you can go with the following:

WITH expected_lines AS (
    SELECT DISTINCT a.id, b.posi, c.doc 
    FROM test a, test b, test c
)
SELECT el.id, el.posi, el.doc, ifnull(t.total, 0) total
FROM expected_lines el
LEFT JOIN test t ON el.id = t.id AND el.posi = t.posi AND el.doc = t.doc

Result enter image description here

Upvotes: 3

Related Questions