Why does nth_value data disappear?
Elemist11
Community Edition User
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.
Best Answer
-
Elemist11
Community Edition User
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;0
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
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.
Support portal is here: https://softwaresupport.softwaregrp.com/. Reach out to your account rep for more information.
That being said, you said;
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?