How to fetch yesterday records for an array based date column using aep query service

[RitwikMo]

I have an array based date column called Billinvoicedate which is of date type.I want to calculate and fetch all the yesterday records for that particular date column.But i am constantly getting error while trying different sort of sql queries.

e.g. select * from pe_cust_ren_sub where DATE(_pe.rensub.BillinvoiceDate ) =DATE(CURRENT_DATE -1 )

I am getting error as  follows:

1:43:06 PM > ErrorCode: 08P01 queryId: cf72f42b-687e-4b26-a821-af1d7b20f241 Unknown error encountered. Reason: [cannot resolve 'CAST(pe_cust_ren_sub ._pe.`rensub`.`BillInvoiceDate` AS DATE)' due to data type mismatch: cannot cast array<date> to date; line 4 pos 7; 

[DavidRoss91 · Accepted solution]

Hi @RitwikMo 

You'd have to explode the array. Try below:

SELECT *

FROM pe_cust_ren_sub

WHERE EXISTS (

SELECT 1

FROM explode (BillInvoiceDate) AS date

WHERE date = CURRENT_DATE - INTERVAL '1 day'