0

I am using Amazon Marketing Cloud (AMC) for work and I am having trouble applying a WHERE [column] <> ''.

The CSV file that is output is large and it contains many records with nothing in the main ID column. I am able to filter out the nulls, but not the ''.

This is the error message I get when I CAST: "No match found for function signature type(<RecordType(BIGINT order, VARCHAR campaign)>)"

The field is compiled in a CTE using NAMED_ROW('order', ROW_NUMBER() OVER(PARTITION BY imp_user_id ORDER BY impression_timestamp),'campaign', campaign) AS campaign_order.

Then, the next CTE turns it into an array using ARRAY_SORT(COLLECT(distinct a.campaign_order)) AS path.

An example of the output is [[1, <Name of Campaign 1>],[2, <Name of Campaign 2>],...[N, <Name of Campaign N>]]

I know that AMC is based on Presto Database Engine, but when looking for documentation I am not sure whether to look at Presto, Hive, or Apache. Whenever I search for something one of those 3 sources usually comes up. I have luck sometimes and other times I do not. It would help if I knew exactly what form of SQL AMC was using so I can narrow down the documentation, syntax, etc.

This platform is still in beta I believe and is relatively exclusive in terms of access. So, I am not sure if many people will be able to help.

In short, I want to filter out records = '', but due to the data type of the field it won't let me.

How do I cast "type(<RecordType(BIGINT order, VARCHAR campaign)" so that I can filter out ''? Also, what documentation should I be using for AMC?

I am considering using LEN() so that I can filter out any records with length = 0.

Anyways, any and all help is appreciated!

If you need more information, then please let me know.

Thank you!

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
Frosty_Fraz
  • 47
  • 1
  • 11

1 Answers1

0

AMC documentation is available at https://advertising.amazon.com/marketing-cloud/documentation (Amazon Advertising account with AMC access is required).

In general AMC SQL is closer to PostgreSQL rather than Presto syntax.

It's a little hard to see why you are trying to cast a record to a string. I think it might be easier to filter the records before the window function is applied. I might be able to help more if you share the relevant part of your query. Alternatively, feel free to contact AMC support by email or via your sales rep.

Fred_dy_
  • 1