# Add Missing Rows Within A Table

## 17 February 2022 - 1 answer

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

Lets Look on the Table:

Pos 2 is missing in ID 3

IDDOCPOSITOTAL
11231100
11232600
11233200
21231100
21232600
21233200
31231100
31233200

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.

Edit: Result

I got now the missing row in the ID, now i want to use the 0 Value, in a LAG() OVER() Function to get the Deviations but i think i cant calculate because of NULL Value.

``````linetotal-lag(linetotal, 1,0) over (partition by t.DOCNUM, t.POSI order by t.Docnum, t.posi, t.logid) as OE
``````

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
``````