-1

I have created a prepared statement to output a file and add a date to the filename. That part works if I select the entire database. I want to select specific columns to include in the query, not all of them.

I'm a newbie to SQL syntax, so please be aware of that when answering. :)

Here is what I started with 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 am selecting individual columns as per my requirement. That works well. However, I can't figure out how to select individual columns like this in the next query...

Here is what I have now after the date requirement was 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 '\r\n';
"

If I replace "custtype.CustType, custtype.CustTypeDesc" in the 2nd example with *, the file generates properly and it's named "Activity_Log_m_d_Y.csv" as expected.

LATEST UPDATE

I can now select all the columns I originally intended with the following code. However, the WHERE clause breaks it.

USE db_name;
SELECT
   @cmd : = concat( "
   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_Report_",
 DATE_FORMAT(now(), '%m_%d_%Y'), ".csv'
 FIELDS TERMINATED 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 % " ");
PREPARE stmt 
   FROM
      @cmd;
EXECUTE stmt;

Question

How can I select only specific columns as opposed to all columns in the example above?

  • FROM log but columns stem from table custtype - is that intended? – eagle275 Nov 11 '19 at 7:41
  • @Akina, I removed the tag. – dentalhero Nov 11 '19 at 15:43
  • @eagle275, yes, that was intended. I'm pulling from 3 different tables different tables. – dentalhero Nov 11 '19 at 15:44
0

After my understanding of mysql - you don't change anything about

FROM, JOIN and most likely WHERE

only change desired columns .... if your 3 tables work together you cant easily break up this "team" for the report even if you only want to see a single column

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 
    INTO OUTFILE", @FOLDER , @TS, @EXT ," FIELDS ENCLOSED BY '\"' TERMINATED BY ';' ESCAPED BY '\"' LINES TERMINATED BY '\r\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 % ");

"
  • I added the FROM, JOIN and WHERE clauses back into my latest code for completeness. Thanks. – dentalhero Nov 12 '19 at 4:29
0

Missing some quotes:

CONCAT("... INTO OUTFILE", @FOLDER , @TS, @EXT ," FIELDS ...

-->

CONCAT("... INTO OUTFILE'", @FOLDER , @TS, @EXT ,"' FIELDS ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.