0

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'

Query Results: Query Result

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 ARRAY_AGG?

What I have tried:

  1. 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
  1. 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 TABLE

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.