SQL Pivot functionality

[JMoore]

Hey Everyone, I have been running into an issue where a SQL Query I am using works as workflow with the pivot command in it but when trying to access the SQL Query Builder tab it can't recognize the Pivot command, but most importantly it throws an error "Error: Action (55): Parse Error at char(1): An operator must be between operands" when trying to use an action tool for setting up the analytic app. Any suggestions on how to handle this error?

Portion of SQL code:

WITH t1 AS (
SELECT UDFL.*
FROM database.table_udfl UDFL
) --SELECT UDF TABLE and alias as t1
,t2 AS (SELECT * FROM t1 --pivot the data from t1 in t2 table
PIVOT (MAX(UDF_VALUE)
FOR UDF_NAME IN (table of names))

[LordNeilLord]

@JMoore

Quick thought on this, why not pivot the data in Alteryx rather than in SQL?

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

[JMoore]

Hi  @LordNeilLord, 

The second half of the data is dependent on this pivot so it can do a left outer join, if I didn't do the pivot I would have to bring in all the data and then attach it to my other subset, this would cause me to pull in millions of rows that are not needed.

[JMoore]

The root of the issue I found today and it is due to using * after select the * is an operator that it does not allow in action or function icons as it is restricted, any suggestions on how to still use it in SQL in Alteryx? I am trying to re-configure my query so the * can be avoided but running into several issues still.

---

[JMoore · Accepted solution]

Spoke with Alteryx support and Jerad was super helpful, using two action tools to update the dates in the SQL query with a text replace: "Replace([Destination], "09-MAR-18", DateTimeFormat(DateTimeAdd([#1],-1,'days'),"%d-%b-%y"))"