Hi, I'm writing a complicated sql query and used a lot of 'with..as' to name a block of sub-query. It's working in SQL server, but not in Tableau Custom SQL.
I researched on the error and looks like I need to use initial SQL. I already looked at the online help(Run Initial SQL), but still don't understand how to use it, can someone help me with the syntax of initial SQL? I tried to copy the with..as clause into initial sql, which didn't work.
My query looks like this:
With Sales as (Select Column12345 from XXX),
COGS as (Select Column12345 from XXX),
Expense as (Select Column12345 from XXX)
Select column 1, column 2, column 3 from Sales
Union
Select column 1, column 2, column 3 from COGS
Union
Select column 1, column 2, column 3 from Expense
OK. Let's try another way then.
Put the following in the Initial SQL:
DROP TABLE IF EXISTS Sales;
DROP TABLE IF EXISTS COGS;
DROP TABLE IF EXISTS Expense;
CREATE TABLE Sales AS
Select column1, column 2, column 3, column 4, column 5 from XXX;
CREATE TABLE COGS AS
Select column1, column 2, column 3, column 4, column 5 from XXX;
CREATE TABLE Expense AS
Select column1, column 2, column 3, column 4, column 5 from XXX;
Put the following in the Custom SQL:
Select column 1, column 2, column 3 from Sales
Union
Select column 1, column 2, column 3 from COGS
Union
Select column 1, column 2, column 3 from Expense
