Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Why does nth_value data disappear?

I have a view that I created using nth_value and when I query that data, I get values, however when try to use that data, everything returns a null value. Also, if I convert that view into a table (with no changes to the actual data), all the nth_values become null. How do I keep this from happening? I can provide samples, but I really just need to know how to keep the data from disappearing.

Tagged:

Best Answer

  • Accepted Answer

    Hello Curtis and Lenoy,
    Thank you both for your help in this. I was able to figure out how to make it work properly. I added an index column that iterated based off the vendor and invoiced date, and then used that for the nth_value.
    Thank you again for your help.
    Matt
    In case it is helpful to anyone in the future:
    select
    three.*,
    case
    when invoiceddate = dateflags then nth_value(Dateflags, 4 ignore nulls) over (order by idx asc rows between current row and unbounded following)
    else nth_value(dateflags, 3 ignore nulls) over (order by idx asc rows between current row and unbounded following) end DueDate
    from (
    select
    two.*,
    case
    when dateflag = 0 then null
    else invoiceddate end DateFlags
    from (
    select distinct
    InvoicedDate,
    one.VendorId,
    -- to_char("Date", 'Day') DayName,
    min(case
    when dayofweek(InvoicedDate) in (1, 7) then 0
    when one.Vendorid = lower(c.vendor) and InvoicedDate = c.holiday_date::date then 0
    else 1 end) DateFlag,
    row_number() over (order by vendorid, invoiceddate) Idx
    from (
    select
    dt.InvoicedDate,
    b."_id" VendorId
    from (
    select
    to_char(startdate,'yyyy-mm-dd')::date InvoicedDate
    from (SELECT ts as startdate FROM (SELECT '2018-01-01T00:00:00Z'::timestamp as tm UNION SELECT '2020-12-31T00:00:00Z'::timestamp as tm) as t TIMESERIES ts as '1 day' OVER (ORDER BY tm))t
    ) dt, vendor_table b
    where
    b."_id" in ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm')
    ) one join holidays_table c
    on
    one.vendorid = lower(c.vendor)
    group by
    Invoiceddate,
    one.Vendorid
    order by 4
    ) two
    ) three;

Answers

  • The issue sounds like the view definition. Perhaps the view definition is missing an ORDER BY and the behavior is changing between what you're running and the view is running? That's only a guess. Can you post the sample queries?

  • My query is comprised of nested queries and I have attached the sample data of everything up to the point of using nth_value. I have also attached sample outputs of the view and the table which are the exact same query.
    create table networkdays as
    select
    a.*,
    case
    when invoiceddate = dateflags then nth_value(Dateflags, 4 ignore nulls) over (partition by vendorid order by invoiceddate asc rows between current row and unbounded following)
    else nth_value(dateflags, 3 ignore nulls) over (partition by vendorid order by invoiceddate asc rows between current row and unbounded following) end DueDate
    from sample_data a

    create or replace view networkdays as
    select
    a.*,
    case
    when invoiceddate = dateflags then nth_value(Dateflags, 4 ignore nulls) over (partition by vendorid order by invoiceddate asc rows between current row and unbounded following)
    else nth_value(dateflags, 3 ignore nulls) over (partition by vendorid order by invoiceddate asc rows between current row and unbounded following) end DueDate
    from sample_data a

    When I try to use this data in a different query, everything returns null values for dueDate.
    Thank you again for your help with this.

  • Ok, thanks for that. It seems to work OK for me. Please let me know if I'm doing something askew here.
    dbadmin=> create table sample_data (invoicedData date, VendorId char(1), DayName varchar(12), DateFlag boolean, DateFlags date);
    CREATE TABLE

    dbadmin=> copy sample_data from STDIN delimiter E'\t' ;
    Enter data to be copied followed by a newline.
    End with a backslash and a period on a line by itself.
    ...data pasted here...
    

    .

    dbadmin=> select * from sample_data ;
     invoicedData | VendorId |  DayName  | DateFlag | DateFlags
    --------------+----------+-----------+----------+------------
     2020-04-04   | a        | Saturday  | f        |
     2020-04-06   | a        | Monday    | t        | 2020-04-06
     2020-04-09   | a        | Thursday  | f        |
     2020-04-19   | a        | Sunday    | f        |
     2020-04-05   | a        | Sunday    | f        |
     2020-04-11   | a        | Saturday  | f        |
     2020-04-13   | a        | Monday    | t        | 2020-04-13
     2020-04-16   | a        | Thursday  | f        |
     2020-04-18   | a        | Saturday  | f        |
     2020-04-20   | a        | Monday    | t        | 2020-04-20
     2020-04-03   | a        | Friday    | t        | 2020-04-03
     2020-04-10   | a        | Friday    | t        | 2020-04-10
     2020-04-14   | a        | Tuesday   | f        |
     2020-04-15   | a        | Wednesday | t        | 2020-04-15
     2020-04-01   | a        | Wednesday | t        | 2020-04-01
     2020-04-12   | a        | Sunday    | f        |
     2020-04-17   | a        | Friday    | t        | 2020-04-17
     2020-04-07   | a        | Tuesday   | f        |
     2020-04-02   | a        | Thursday  | f        |
     2020-04-08   | a        | Wednesday | t        | 2020-04-08
    (20 rows)
    
    dbadmin=> commit ;
    COMMIT
    
    dbadmin=> select
    dbadmin-> a.*,
    dbadmin-> case
    dbadmin-> when invoiceddate = dateflags then nth_value(Dateflags, 4 ignore nulls) over (partition by vendorid order by invoiceddate asc rows between current row and unbounded following)
    dbadmin-> else nth_value(dateflags, 3 ignore nulls) over (partition by vendorid order by invoiceddate asc rows between current row and unbounded following) end DueDate
    dbadmin-> from sample_data a;
     invoicedDate | VendorId |  DayName  | DateFlag | DateFlags  |  DueDate
    --------------+----------+-----------+----------+------------+------------
     2020-04-01   | a        | Wednesday | t        | 2020-04-01 | 2020-04-08
     2020-04-02   | a        | Thursday  | f        |            | 2020-04-08
     2020-04-03   | a        | Friday    | t        | 2020-04-03 | 2020-04-10
     2020-04-04   | a        | Saturday  | f        |            | 2020-04-10
     2020-04-05   | a        | Sunday    | f        |            | 2020-04-10
     2020-04-06   | a        | Monday    | t        | 2020-04-06 | 2020-04-13
     2020-04-07   | a        | Tuesday   | f        |            | 2020-04-13
     2020-04-08   | a        | Wednesday | t        | 2020-04-08 | 2020-04-15
     2020-04-09   | a        | Thursday  | f        |            | 2020-04-15
     2020-04-10   | a        | Friday    | t        | 2020-04-10 | 2020-04-17
     2020-04-11   | a        | Saturday  | f        |            | 2020-04-17
     2020-04-12   | a        | Sunday    | f        |            | 2020-04-17
     2020-04-13   | a        | Monday    | t        | 2020-04-13 | 2020-04-20
     2020-04-14   | a        | Tuesday   | f        |            | 2020-04-20
     2020-04-15   | a        | Wednesday | t        | 2020-04-15 |
     2020-04-16   | a        | Thursday  | f        |            |
     2020-04-17   | a        | Friday    | t        | 2020-04-17 |
     2020-04-18   | a        | Saturday  | f        |            |
     2020-04-19   | a        | Sunday    | f        |            |
     2020-04-20   | a        | Monday    | t        | 2020-04-20 |
    (20 rows)
    
    dbadmin=> create table networkdays as
    dbadmin-> select
    dbadmin-> a.*,
    dbadmin-> case
    dbadmin-> when invoiceddate = dateflags then nth_value(Dateflags, 4 ignore nulls) over (partition by vendorid order by invoiceddate asc rows between current row and unbounded following)
    dbadmin-> else nth_value(dateflags, 3 ignore nulls) over (partition by vendorid order by invoiceddate asc rows between current row and unbounded following) end DueDate
    dbadmin-> from sample_data a ;
    CREATE TABLE
    dbadmin=> select * from networkdays ;
     invoicedDate | VendorId |  DayName  | DateFlag | DateFlags  |  DueDate
    --------------+----------+-----------+----------+------------+------------
     2020-04-01   | a        | Wednesday | t        | 2020-04-01 | 2020-04-08
     2020-04-02   | a        | Thursday  | f        |            | 2020-04-08
     2020-04-03   | a        | Friday    | t        | 2020-04-03 | 2020-04-10
     2020-04-04   | a        | Saturday  | f        |            | 2020-04-10
     2020-04-05   | a        | Sunday    | f        |            | 2020-04-10
     2020-04-06   | a        | Monday    | t        | 2020-04-06 | 2020-04-13
     2020-04-07   | a        | Tuesday   | f        |            | 2020-04-13
     2020-04-08   | a        | Wednesday | t        | 2020-04-08 | 2020-04-15
     2020-04-09   | a        | Thursday  | f        |            | 2020-04-15
     2020-04-10   | a        | Friday    | t        | 2020-04-10 | 2020-04-17
     2020-04-11   | a        | Saturday  | f        |            | 2020-04-17
     2020-04-12   | a        | Sunday    | f        |            | 2020-04-17
     2020-04-13   | a        | Monday    | t        | 2020-04-13 | 2020-04-20
     2020-04-14   | a        | Tuesday   | f        |            | 2020-04-20
     2020-04-15   | a        | Wednesday | t        | 2020-04-15 |
     2020-04-16   | a        | Thursday  | f        |            |
     2020-04-17   | a        | Friday    | t        | 2020-04-17 |
     2020-04-18   | a        | Saturday  | f        |            |
     2020-04-19   | a        | Sunday    | f        |            |
     2020-04-20   | a        | Monday    | t        | 2020-04-20 |
    (20 rows)
    
    dbadmin=> create or replace view networkdays as
    dbadmin-> select
    dbadmin-> a.*,
    dbadmin-> case
    dbadmin-> when invoiceddate = dateflags then nth_value(Dateflags, 4 ignore nulls) over (partition by vendorid order by invoiceddate asc rows between current row and unbounded following)
    dbadmin-> else nth_value(dateflags, 3 ignore nulls) over (partition by vendorid order by invoiceddate asc rows between current row and unbounded following) end DueDate
    dbadmin-> from sample_data a ;
    ROLLBACK 3327:  Existing object "networkdays" is not a view
    dbadmin=> \e
    CREATE VIEW
    dbadmin=> select * from v_networkdays ;
     invoicedDate | VendorId |  DayName  | DateFlag | DateFlags  |  DueDate
    --------------+----------+-----------+----------+------------+------------
     2020-04-01   | a        | Wednesday | t        | 2020-04-01 | 2020-04-08
     2020-04-02   | a        | Thursday  | f        |            | 2020-04-08
     2020-04-03   | a        | Friday    | t        | 2020-04-03 | 2020-04-10
     2020-04-04   | a        | Saturday  | f        |            | 2020-04-10
     2020-04-05   | a        | Sunday    | f        |            | 2020-04-10
     2020-04-06   | a        | Monday    | t        | 2020-04-06 | 2020-04-13
     2020-04-07   | a        | Tuesday   | f        |            | 2020-04-13
     2020-04-08   | a        | Wednesday | t        | 2020-04-08 | 2020-04-15
     2020-04-09   | a        | Thursday  | f        |            | 2020-04-15
     2020-04-10   | a        | Friday    | t        | 2020-04-10 | 2020-04-17
     2020-04-11   | a        | Saturday  | f        |            | 2020-04-17
     2020-04-12   | a        | Sunday    | f        |            | 2020-04-17
     2020-04-13   | a        | Monday    | t        | 2020-04-13 | 2020-04-20
     2020-04-14   | a        | Tuesday   | f        |            | 2020-04-20
     2020-04-15   | a        | Wednesday | t        | 2020-04-15 |
     2020-04-16   | a        | Thursday  | f        |            |
     2020-04-17   | a        | Friday    | t        | 2020-04-17 |
     2020-04-18   | a        | Saturday  | f        |            |
     2020-04-19   | a        | Sunday    | f        |            |
     2020-04-20   | a        | Monday    | t        | 2020-04-20 |
    (20 rows)
    
  • I don't understand why mine is not working then. I am trying it via vsql and dBeaver and neither will keep the duedate when I am creating a table. The only difference I see between you and me is that DateFlag is boolean for you and an integer for me. And I was only trying to create the table in the hopes that I was committing the data to a value (like copy values in excel) rather than being potentially variable like a view allows so that I could left join that data into another query I wrote. All the data shows up null in that second query, prompting me to try and figure this out. But I am still at a complete loss as to why my data is disappearing each time.

  • making dateFlag a boolean shouldn't matter, but you could easily try that. What version are you on? It's possible there is some obscure bug somewhere causing it. I'm on 10.0, so that's not helpful for you, unfortunately. Otherwise, you might need to open a case to let support look into it. Sorry I can't be more helpful!

  • I just tried updating to the 9.3.1 drivers and still the same thing. How do I log a support ticket? Thank you again for your help.

  • Whoever administers Vertica for your organization should be able to help with that. I don't have a lot of visibility into that process. Maybe someone with more knowledge on that process will chime in here.

  • LenoyJLenoyJ Employee

    Support portal is here: https://softwaresupport.softwaregrp.com/. Reach out to your account rep for more information.
     
    That being said, you said;

    When I try to use this data in a different query, everything returns null values for dueDate

    Can you paste this query or explain what you're trying to do?

  • I changed my type to int, and I still get the same behavior. What version are you on, Sunnywilson09? Is your sample code the same as mine?

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.