I've created a prepared statement to print a file and add a date to the file name. This part works if I select the entire database. I want to select specific columns for the query, not all.
I'm a newbie in SQL syntax. Please note this when answering. 🙂
Here's what I started before the date in filename was a requirement:
USE dbx; SELECT custtype.custtype, custtype.custtypedesc, USER.jobtitle, USER.branch, log.custid, log.username, log.actualuser, log.timestamp, log.action, log.data INTO outfile '/inetpub/wwwroot/reports/Activity_Log.csv' fields terminated BY ',' optionally enclosed BY '"' lines terminated BY 'n' FROM log LEFT OUTER JOIN USER ON log.custid = USER.custid AND log.username = USER.username LEFT OUTER JOIN custtype ON custtype.custtype = USER.custtype WHERE USER.email NOT LIKE " % @sample.com" AND USER.email NOT LIKE " % @google.com" AND log.username NOT LIKE "Guest % "
You can see that I select individual columns according to my request. This works fine, but I can not figure out how to select individual columns in the next query as follows …
Here's what I have now, after the date request has been added:
SET @TS = DATE_FORMAT(NOW(), '_%m_%d_%Y'); SET @FOLDER = '/inetpub/wwwroot/reports/'; SET @PREFIX = 'Activity_Report_'; SET @EXT = '.csv'; USE webcat_336; SET @CMD = concat(" SELECT custtype.CustType, custtype.CustTypeDesc FROM log INTO OUTFILE", @FOLDER , @TS, @EXT); "' FIELDS ENCLOSED BY '"' TERMINATED BY '; ' ESCAPED BY '"'", " LINES TERMINATED BY 'rn'; "
If I replace * "custtype.CustType, custtype.CustTypeDesc" with * in the second example, the file is generated correctly and is expected to be named Activity_Log_m_d_Y.csv.
How can I select only certain columns, unlike all the columns in the example above?