How to insert records into the RESULT of a query?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: How to insert records into the RESULT of a query?

  1. #1
    Join Date
    Nov 2009
    Posts
    5

    How to insert records into the RESULT of a query?

    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

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    easiest way?
    Create a pivot table containing just the numerals... 1,2,3,4,...,N then join both tables with the (+) operator.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Nov 2009
    Posts
    5
    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 ...

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Nov 2009
    Posts
    5
    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.

  6. #6
    Join Date
    Nov 2009
    Posts
    5
    ... errr .. two events ... not to

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

  7. #7
    Join Date
    Apr 2006
    Posts
    377
    One option would be:

    Code:
    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

  8. #8
    Join Date
    Nov 2009
    Posts
    5
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width