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.
Lets Look on the Table:
Pos 2 is missing in ID 3
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.
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.
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
My educated guess would be, that you want to make
DOC part of the key and assume a
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
- → I can't do a foreign key, constraint error
- → How to implement DbDongle::convertTimestamps as workaround of invalid timestamps with MySql strict
- → MySQL error "Foreign key constraint is incorrectly formed"
- → Eloquent Multitable query
- → "Laravel 5.1" add user and project with userId
- → Database backup with custom code in laravel 5 and get the data upto 10 rows from per table in database
- → Laravel 5.1 QueryException when trying to delete a project
- → Using Array in '->where()' for Laravel Query Building
- → Chaining "Count of Columns" of a Method to Single Query Builder
- → Laravel Eloquent Joining Strange query
- → convert time using mysql laravel 5
- → How to update a column after an expiration date in MySQL?
- → Foreign key constraint fails on existing key