Hello,
I'm using the AEP Query Service, and I'm trying to create a column header with a dynamic name that includes today's date, like this: "Header+today's date". However, the query below is generating multiple errors:
SET @col_name= SELECT 'HEADER' || TO_CHAR(current_date, 'YYYYMMDD'); -- Eliminar la tabla si ya existe
DROP TABLE IF EXISTS SPSUnsubsample_timestamp;
-- Crear la tabla con la estructura deseada
CREATE TABLE SPSUnsubsample_timestamp AS
WITH data_rows AS (
SELECT DISTINCT
get_json_object(to_json(identityMap), '$.Email[0].id') AS user_email
FROM ajo_email_tracking_experience_event_dataset
WHERE eventType = 'message.tracking'
AND get_json_object(to_json(_experience), '$.customerJourneyManagement.messageInteraction.interactionType') = 'click'
AND LOWER(get_json_object(to_json(_experience), '$.customerJourneyManagement.messageInteraction.label')) LIKE '%unsubscribe%'
AND get_json_object(to_json(_experience), '$.customerJourneyManagement.messageInteraction.trackingType') IN ('opt_out', 'direct_optout')
),
row_count AS (
SELECT COUNT(*) AS total FROM data_rows
)
SELECT * FROM (
-- HEADER primero
SELECT 'HEADER' || TO_CHAR(current_date, 'YYYYMMDD') AS @col_name UNION ALL
-- Correos electrónicos
SELECT user_email FROM data_rows
UNION ALL
-- TRAILER al final
SELECT 'TRAILER_' || LPAD(CAST(total AS STRING), 8, '0') FROM row_count
) final_result;
Is it possible to achieve this with the query service? If not, are there any workarounds or suggestions for how I could modify this approach to make it work?
Thank you!
