How to insert records into the RESULT of a query?

[TStander]

Good day

I'll try to illustrate my problem with an example:

Say I have the following table:

Nr Value
-----------
1 10
2 5
4 6
5 8



I would like to query this table and to manipulate the result to look like

Nr Value
-----------
1 10
2 5
3 NULL
4 6
5 8

In short - I would like to identify breaks in contuous data (a number sequence in the example) and insert a record in the result set to indicate that there is a break. This is needed for a graphing component I use, to be able to draw discontinueties in a line graph.

I do not want to add this new record to my database, just pass it on to the application. How would I go about doing that?

My idea is to write a PL/SQL stores procedure to do the query, do the manipulation and then return the manipulated result set.

I am whoever not sure of which data structures to use -

I'll read the original query into a ? what ?
I can then loop through the records and figure out which records need to be added, add it and at the end of the loop return the ... errr ?what? ?how?

Hope my description makes kind of sense.

Any help will be sincerely appreciated.

Tiaan

[PAVB]

easiest way?
Create a pivot table containing just the numerals... 1,2,3,4,...,N then join both tables with the (+) operator.

[TStander]

Thanks for the reply!

If I understand you correctly, which I think I don't, I would need to know N?

My example might also not have been clear enough - I do not want to insert rows for every missing n - just a single row at any discontinuety.

eg.

Nr Value
-----------
1 10
2 5
10 3
11 8


should become

Nr Value
-----------
1 10
2 5
3 NULL (in fact ... Nr can be anything between 3 and 9 - it will have the same result on the graph in the end)
10 3
11 8


Thanks again for any thoughts


What would probably be possible, but I don't know how elegant or optimal a solution, would be to do something like:

Select
x.NR,
NULL as Value
from

(select

(NR + 1) as NR,
LEAD(NR,1,null) OVER (PARTITION by (some other field ..hehe) ORDER BY NR) - NR as difference

from

Some_table

) x

where difference > 1

This should produce a record for each discontinuety.
I can then UNION this with the SELECT Nr, Value FROM SOME_TABLE

It should work, but would it be the fastest solution? Most probably not ...

[PAVB]

Proposed solution was to create a temp secondary table, let's call it allnumbers - this table would have a single column, let's call it nr of the number datatype.

Your process knows the limits of yourtable.nr values... populate allnumbers column inserting one row for each value in between min(yourtable.nr) and max(yourtable.nr) then just join yourtable with allnumbers on nr column with (+) operator.

I know this is not cutting edge technology but ... it works

[TStander]

Thanks Pablo

My current solution does exactly that and as you say ... it works

The realworld problem I have makes it unusable for some of my graphs.

Imagine this - I have timestamps of my data in 10 millisecond increments.
I would like to draw to "events" on the same graph - they could be hours or even days apart.

This would mean creating millions of records in the temp table (8 640 000 10 millisecond intervals in a day ...).

It works ok where my data is timestamped in days or 10minute intervals.

[TStander]

... errr .. two events ... not to

Why am I not allowed to edit my posts? Because I'm a newbie ?

[ebrian]

One option would be:

SQL> SELECT * FROM sparse;

        NR      VALUE
---------- ----------
         1         10
         2          5
        10          3
        11          8

SQL> SELECT nr,
  2         value
  3  FROM  ( SELECT lvl nr,
  4                 value  ,
  5                 lag(value, 1, 0) over (ORDER BY lvl) lg
  6          FROM sparse
  7          RIGHT OUTER JOIN
  8        ( SELECT lvl
  9          FROM ( SELECT level lvl,
 10                         mn
 11                 FROM (SELECT MIN(nr) mn,
 12                              MAX(nr) mx
 13                       FROM sparse)
 14                 CONNECT BY level <= mx)
 15                 WHERE   lvl >= mn)
 16         ON nr = lvl)
 17  WHERE  value IS NOT NULL
 18  OR lg IS NOT NULL;

        NR      VALUE
---------- ----------
         1         10
         2          5
         3
        10          3
        11          8

[TStander]

Thank you ebrian

I'm going to have to read up a bit on the "levell" and "connect by" parts of your query - I'm definately going to learn something new

It produces the exact result I need, I now just need to figure out how it works.

Thanks for your time!

Regards
Tiaan