"Unexpected ordering of clauses" error when exporting an Excel file in MySQL

Question

I need to export the result of a query to a CSV file in MySQL.

I wrote:

SELECT orders.user_id, orders.order_id, members.name, members.family,
       orders.agent_id, CONCAT(m.name, ' ', m.family) AS agent_name
  FROM orders
  JOIN members
    ON members.username=orders.user_id
  JOIN members m
    ON orders.agent_id=m.username
 WHERE order_status=4 and pay_method='verbalPayment'
   AND agent_id is not NULL
   AND order_time BETWEEN 1559935800 AND 1560596861
  INTO OUTFILE 'weekly_orders.csv' FIELDS TERMINATED BY ',';

But when I ran this query, I got this error:

Static analysis:

1 errors were found during analysis.

Unexpected ordering of clauses. (near "FROM" at position 131)

What is the problem and how can I solve it?

Where does Excel come in? Is the CSV file to be imported in Excel? — Peter Mortensen, Jul 15 '21 at 00:28

Gordon Linoff · Answer 1 · 2021-07-15T00:35:27.667

Obviously, the problem is the INTO, which should immediately follow the SELECT. I would suggest writing the query as:

SELECT o.user_id, o.order_id, mu.name, mu.family, 
       o.agent_id, CONCAT_WS(' ', ma.name, ma.family) AS agent_name 
INTO OUTFILE 'weekly_orders.csv' FIELDS TERMINATED BY ','
FROM orders o JOIN
     members mu
     ON mu.username = o.user_id JOIN
     members ma
     ON o.agent_id = ma.username 
 WHERE o.order_status = 4 AND
       o.pay_method = 'verbalPayment' AND
       o.order_time BETWEEN 1559935800 AND 1560596861 ;

Notes:

The condition agent_id is not NULL is redundant. The JOIN takes care of this.
Alias all your tables. mu for "member as user" and ma for "member as agent" is easier to understand.
CONCAT_WS() works even when one of the names is NULL.
I would also be inclined to convert the date constants from YYYY-MM-DD format to the Unix format, so the query is more easily understood (but you may understand what those numbers mean).

Re *"the problem is that the INTO follows the SELECT"*: Do you mean *"the problem is that the INTO* ***must follow*** *the SELECT"* (sort of the opposite)? — Peter Mortensen, Jul 15 '21 at 00:22

score 2 · Answer 2 · edited Jul 15 '21 at 00:18

2

Wrong into clause position:

  SELECT orders.user_id
    , orders.order_id
    , members.name
    , members.family
    , orders.agent_id
    , CONCAT(m.name, ' ', m.family) AS agent_name
    INTO OUTFILE 'weekly_orders.csv' FIELDS TERMINATED BY ','
    FROM orders
    JOIN members ON members.username=orders.user_id
    join members m on orders.agent_id=m.username
    where order_status=4
    and pay_method='verbalPayment'
    and agent_id is not NULL and order_time between 1559935800 and 1560596861

edited Jul 15 '21 at 00:18

Peter Mortensen

30,738
21
105
131

answered Jun 15 '19 at 11:23

ScaisEdge

131,976
10
91
107

What is the reason? What rules apply? Please respond by editing your answer, not here in comments (***without*** "Edit:", "Update:", or similar - the answer should appear as if it was written today). – Peter Mortensen Jul 15 '21 at 00:20

Barbaros Özhan · Answer 3 · 2021-07-15T07:49:18.603

2

If you have the former versions of the database such as 5.6,5.7.. etc, then INTO clause need to precede FROM clause, unless a trailing locking clause such as FOR UPDATE or LOCK IN SHARE MODE is used. Putting INTO clause to the end of the statement is supported, and positioning it before a locking clause is deprected starting from the version 8.0.20.

Therefore, seems you're currently using a former version of the database, and need to prefer the following reordered syntax :

....INTO OUTFILE 'weekly_orders.csv' FIELDS TERMINATED BY ','
    FROM orders ....

edited Jul 15 '21 at 07:49

answered Jun 15 '19 at 11:24

Barbaros Özhan

59,113
10
31
55

1

*Why* is that required? Please respond by editing your answer, not here in comments (***without*** "Edit:", "Update:", or similar - the answer should appear as if it was written today). – Peter Mortensen Jul 15 '21 at 00:21

"Unexpected ordering of clauses" error when exporting an Excel file in MySQL

3 Answers3