I am aggregating various tables into a singular "mega" table to drive an analytics platform.
While I have several
WITH clauses defined above my provided code snippet, these are simply to mitigate fanout (since I am utilizing multiple
LEFT JOINS) and I have left those out for simplicity. Here is the main query code:
SELECT Visits.ClientName , Visits.ClientNumber , Visits.SubClient , Visits.Product , Visits.Unit , Visits.ParticipantID , ARRAY_AGG((Visits.ActivityDateAndHour, Visits.NumberOfVisits, Items.ProductID, Items.ItemDescription, Items.ProductViews, Items.ProductQuickViews, Items.AddWishList, Items.CartAdditions) ORDER BY Visits.ActivityDateAndHour DESC) as VisitResults FROM Visits LEFT JOIN Items ON Visits.ParticipantID = Visits.ParticipantID and Visits.ActivityDateAndHour = Items.ActivityDateAndHour GROUP BY Visits.ClientName, Visits.ClientNumber, Visits.SubClient, Visits.Product, Visits.Unit, Visits.ParticipantID HAVING Visits.ParticipantID = '104148::2td::069049'
Everything in the query result is correct, however the field headers corresponding to the
ARRAY_AGG function are nonsensical, as shown in the picture. I am wondering if there is any way to alias the fields within the
What I have tried:
- Aliasing within the ARRAY_AGG function (this did not work syntactically and the validator refused to execute the code.)
ARRAY_AGG((Visits.ActivityDateAndHour as Date, Visits.NumberOfVisits as NumVisits, Items.ProductID as ProductID, ..., etc.) ORDER BY Visits.ActivityDateAndHour DESC) as VisitResults
- Following along with this solution here for a similar question in PostgreSQL. This did not work because GBC (to my knowledge) does not allow for you to create your own user-defined types or cast to type