common table expression - MySQL error " 'WITH' is not valid at this position for this server version expecting ALTER, ANALYZE, ...."

Question

I know this is a known issue with older versions of MySQL Workbench but I'm certain I have the latest version so that is definitely not the cause. The code is the following, I will also explain why I am using this approach.

WITH temp as
(
LOAD DATE INFILE 'File Location.csv'
IGNORE
INTO TABLE temp
FIELDS TERMINATED BY '^~'
LINES TERMINATED BY '
'
IGNORE 1 ROWS
(RUN_DATE, PROC-DT, STL_DT, TRD_DT, CHG_DT, SENT_ADP_DT, ACKN_ADP_DT, ORIG_PROC_DT)
)
select * from temp; 

So, in a previous query, I loaded the entirety of the INFILE into a table that I need, but these fields in specific (all date fields) were not being populated correctly. I'm trying to create a temp table so that I may test some preprocessing logic on these fields (using the SET argument for LOAD DATA) and then hopefully inject these columns correctly into my permanent table with the appropriate logic. However, I have no idea why I'm getting this confounded error. Thanks in advance for the help.

Answer 1

I have several comments, in no particular order:

• You can't create a table with LOAD DATA. You must create the table first.
• You can't run LOAD DATA inside a CTE. The CTE can only have a SELECT inside.
• You don't need a CTE for this.
• Your error on 'WITH' suggests aren't using a version of MySQL that supports CTE syntax.
• The syntax support depends on your version of MySQL Server, not MySQL Workbench. Workbench is just a client, it can connect to any version of MySQL Server. Try running the query SELECT @@version; and that will tell you the version of MySQL Server you are connected to. If it's less than version 8.0, it doesn't support CTE syntax.

You should create the table, then load data, then select from it.

CREATE TEMPORARY TABLE temp ( ... );

LOAD DATE INFILE 'File Location.csv'
IGNORE
INTO TABLE temp
FIELDS TERMINATED BY '^~'
LINES TERMINATED BY '
'
IGNORE 1 ROWS
(RUN_DATE, PROC-DT, STL_DT, TRD_DT, CHG_DT, SENT_ADP_DT, ACKN_ADP_DT, ORIG_PROC_DT);

SELECT * FROM temp; 

Unlike a CTE, the temporary table will persist until your client ends its session. Then like any temporary table, it will be dropped automatically.