1

I have successfully exported my table in MySQL to csv with columns. I used this guy's answer at Include headers when using SELECT INTO OUTFILE?

SELECT 'ColName1', 'ColName2', 'ColName3'

UNION ALL

SELECT ColName1, ColName2, ColName3

FROM YourTable
INTO OUTFILE '/path/outfile'

However, I want to export a query formula as a new column to be added to the csv file. I tried adding an extra calculated column after the second SELECT statement. MySQL gave me an error saying "The used SELECT statements have a different number of columns".

Example formula: SELECT CAST((ColName1 * ColName2) AS DECIMAL(7,2)) AS ColNameX. I'm not sure where to input it in my export statement.

Community
  • 1
  • 1
hk101
  • 59
  • 8

1 Answers1

0

For the UNION to work you must have the same number of columns and they must be of the same type. As you creating the header row as text then all of your columns in the second query must also be text. Like so:

SELECT 'ColName1', 'ColName2', 'ColName3', 'New Column'
UNION ALL
SELECT 
  ColName1
  ,ColName2
  ,ColName3
  ,CAST(CAST((ColName1 * ColName2) AS DEC(5,2)) AS CHAR)
FROM YourTable
INTO OUTFILE '/path/outfile'
Dave Sexton
  • 10,768
  • 3
  • 42
  • 56
  • What if I wanted to make the New Column value trim at an exact precision, say, 3 digits, precision 2? Or do I trim the values via Excel (program I am importing the csv)? – hk101 Jun 18 '13 at 16:47
  • I'm getting a mysql syntax error near VARCHAR. The columns involved are both decimal. I solved this by doing `CAST((ColName1 * ColName2) AS DEC(5,2))` Everything is outputting as intended. – hk101 Jun 18 '13 at 18:07