CODEWITHSUNDEEP
mysql
Ankit Doshi
Ankit Doshi

Reputation: 1174

#1054 unknown column in order clause

I am trying to export my database table directly into csv file but getting error like below

1054 - Unknown column 'callstart' in 'order clause'

SELECT 'Date','callerid','Called Number','Code','Destination'
UNION ALL 
SELECT 
  CONVERT_TZ(`callstart`,'+00:00','+02:00') as call_date, 
  callerid, callednum, pattern, notes 
FROM (cdrs) 
WHERE `callstart` >= '2015-06-12 00:00:01' 
  AND `callstart` <= '2015-06-13 23:59:59' 
  AND `parent_id` = '0' 
ORDER BY callstart DESC 
INTO OUTFILE '/tmp/2015-06-27-11-28-47.csv' 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY ' ';

CDRS Table :

Field Name | Datatype   |Null 
-----------------------------
callstart  | datetime   |No
callerid   | varchar(30)|No
callednum  | varchar(30)|No
pattern    | varchar(20)|No
notes      | varchar(80)|NO
parent_id  | int(11)    |NO

And cdrs table have already callstart field but why i am getting this that i don't know

NOTE :
If I will remove before UNION ALL Query then it works fine but I have export this query with heading so I just add UNION ALL so if is there any other way in which I can add heading manually from database then also it will fine but sequence of fields must not be changed

Any suggestions ?

Upvotes: 1

Views: 3275

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Your query is a bit confusing without an explanation. You want to get the headers of rows into the outfile, which is why you are using single quotes in the first query. This will probably do what you want:

SELECT 'Date', 'callerid', 'Called Number', 'Code', 'Destination'
UNION ALL 
(SELECT CONVERT_TZ(`callstart`,'+00:00','+02:00') as call_date, callerid, 
        callednum, pattern, notes
 FROM cdrs
 WHERE `callstart` >= '2015-06-12 00:00:01' AND 
       `callstart` <= '2015-06-13 23:59:59' AND `parent_id` = '0'
 ORDER BY callstart DESC
)
INTO OUTFILE '/tmp/2015-06-27-11-28-47.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY ' ';

Some explanation. Your ORDER BY was for the result of the UNION ALL, not for the second subquery. It was looking for the column names from the first subquery -- and there are none.

You only want the second subquery to be sorted, so use parentheses and ORDER BY just for that subquery.

Note: in practice this will work, but it depends on the results being returned from the UNION ALL in the order the statement is written. As far as I know, most databases do this. However, it is very hard to find documentation that guarantees this order of processing (Postgres is best on the documentation front).

Upvotes: 1

Related Questions