Re: Concat alternative

[Gerard]

On 10/24/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:

 Gerard wrote:
  Currently I am running a concat statement to combine a field with a user
  name and domain to create and email address. In testing it looks like
  running the concat is a very slow command to run. The select statement
  currently looks like this.
 
  select concat(user,'@',domain),servername,port from database where
  concat(user,'@',domain)='[EMAIL PROTECTED]';
 

 Why do CONCAT() twice? Couldn't you just do:

 WHERE user = 'username' AND domain = 'domain.com'

 Or am i missing something?

 brian

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/[EMAIL PROTECTED]



This is done because the application is not flexible. I can only put one
condition in which goes for the where and select statement.

Concat alternative

[Gerard]

Currently I am running a concat statement to combine a field with a user
name and domain to create and email address. In testing it looks like
running the concat is a very slow command to run. The select statement
currently looks like this.

select concat(user,'@',domain),servername,port from database where
concat(user,'@',domain)='[EMAIL PROTECTED]';

Re: Query error

[Gerard van Beek]

The comma at the end of the SELECT statement needs to be removed

Naz Gassiep wrote:

Hi,
  I'm trying to execute this query:

 SELECT group_post.group_thread_id,
FROM group_post
LEFT OUTER JOIN group_post_moderation ON (group_post.group_post_id 
= group_post_moderation.group_post_id)
LEFT OUTER JOIN group_post_mod_option ON 
(group_post_moderation.group_post_moderation_option = 
group_post_mod_option.option_id)

   WHERE group_thread_id = '6'
GROUP BY group_post.group_thread_id
ORDER BY lft;

But when I do, I get this error:

ERROR 1064: You have an error in your SQL syntax.  Check the manual 
that corresponds to your MySQL server version for the right sy

ntax to use near 'FROM group_post
   LEFT OUTER JOIN group_post_moderation ON (grou

Can anyone please tell me what is causing that? I'm using MySQL4.

Thanks,
- Naz.

Re: Problem with DATE_ADD - Michael Stassen, July 16 2004 7:48am

[Gerard Gilliland]

Michael:
  Again, Thank you for your excellent suggestions and quick response !!
  I agree that Two types of data means you should use two columns.
  There is no excuse for bad design.
  (I inherited the database and moved it to MySQL -- I should have converted
then.)
  I will split the Source table into N and Period (probably with better
names.)
  (I am indeed working with a temp table.
  Only for debug purposes and to test before I sent the original problem.)

  Concerning:

  $qry = SELECT PlantName, Unit, TagName, CalDate, nPeriod,
  TRIM(MID(nPeriod,1,INSTR(nPeriod,' ')-1)) AS n,
  TRIM(MID(nPeriod,INSTR(nPeriod,' ')+1)) AS Period,
  CASE
WHEN Period = 'Month' THEN DATE_ADD(CalDate, INTERVAL n MONTH)
WHEN Period = 'Year' THEN DATE_ADD(CalDate, INTERVAL n YEAR)
  END
  AS DueDate
  FROM tblTemp;

  Did you try it?  (the above)
  -- Yes
  It should work.
   -- I agree.  However, It fails.
  It doesn't work with Temporary Calculated columns
  In other words (just for testing purposes because the results don't make
sense),
  I was successful calculating and showing (not using) the N and Period
columns and getting results with:

Works:
 $qry = SELECT PlantName, Unit, TagName, CalDate, nPeriod,
 TRIM(MID(nPeriod,1,INSTR(nPeriod,' ')-1)) AS n,
 TRIM(MID(nPeriod,INSTR(nPeriod,' ')+1)) AS Period,
 CASE
   WHEN TagName = 'FT0701' THEN DATE_ADD(CalDate, INTERVAL Unit
MONTH)
   WHEN TagName = 'FCV96008' THEN DATE_ADD(CalDate, INTERVAL Unit
YEAR)
 END
 AS DueDate
 FROM tblTemp;

I think it fails for the same reason, I have trouble with using criteria
against DueDate

First Let me re-define tblTemp:

I concur with your recommentation of the following (with the fields already
defined in the table)
-- This is the right solution.
A conversion in tblTemp is a follows.
(Actually nPeriod comes from an other table but for our discussion.)

NEW Table Definition with New fields:
PlantName, Unit, TagName, CalDate, CalPeriod, CalPeriodType

And new data:
'Valmont', '5', 'FCV96008', '2004-04-15', 1, 'Year'
'Valmont', '5', 'FT0701', '2004-03-10', 3, 'Month'

Returning to the TemporaryCalculation discussion:

Fails (in the WHERE clause):
$qry = SELECT PlantName, Unit, TagName, CalDate, CalPeriod,
CalPeriodType,
  CASE
WHEN CalPeriodType = 'Month' THEN DATE_ADD(CalDate, INTERVAL
CalPeriod MONTH)
WHEN CalPeriodType = 'Year' THEN DATE_ADD(CalDate, INTERVAL
CalPeriod YEAR)
  END
AS DueDate
FROM tblTemp
WHERE DueDate   '2005-01-01';
(Note: DueDate is a TemporaryCalculated field)


Works:
$qry = SELECT PlantName, Unit, TagName, CalDate, CalPeriod,
CalPeriodType,
  CASE
WHEN CalPeriodType = 'Month' THEN DATE_ADD(CalDate, INTERVAL
CalPeriod MONTH)
WHEN CalPeriodType = 'Year' THEN DATE_ADD(CalDate, INTERVAL
CalPeriod YEAR)
  END
AS DueDate
FROM tblTemp
WHERE
  CASE
WHEN CalPeriodType = 'Month' THEN DATE_ADD(CalDate, INTERVAL
CalPeriod MONTH)
WHEN CalPeriodType = 'Year' THEN DATE_ADD(CalDate, INTERVAL
CalPeriod YEAR)
  END
 '2005-01-01';
(Note: Where Criteria does not contain any TemporaryCalculated field.)

A simpler case of the above (but continuing the TemporaryCalculation field
discussion.)

Fails:
 $qry = SELECT PlantName, Unit, TagName, CalDate, CalPeriod,
CalPeriodType,
   (CalPeriod + Unit) AS CalNum
   FROM tblTemp
  WHERE CalNum  7;

Works:
 $qry = SELECT PlantName, Unit, TagName, CalDate, CalPeriod,
CalPeriodType,
   (CalPeriod + Unit) AS CalNum
   FROM tblTemp
  WHERE (CalPeriod + Unit)  7;


I do appreciate the thoroughness of your effort in coversion to Months,
However the potential for using Days looms ahead,
and the complexity of Days in combinations with Month lengths, and Leap
years
causes more confusion than the well defined CASE of Day, Month, Year.

Again -- Thank you for your excellent support and quick response.

Gerard Gilliland
[EMAIL PROTECTED]


- Original Message - 
From: Michael Stassen [EMAIL PROTECTED]
To: Gerard Gilliland [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Monday, July 19, 2004 13:47
Subject: Re: Problem with DATE_ADD - Michael Stassen, July 16 2004 7:48am



 Gerard Gilliland wrote:

  Michael:
 
  Thank you for your excellent suggestion and quick response.

 You're welcome.

  I now have an operable DATE_ADD.
  The solution to break nPeriod into n and Period would not work directly.
  That is, in the SELECT statement in the form of ...
 
  $qry = SELECT PlantName, Unit, TagName, CalDate, nPeriod,
  TRIM(MID(nPeriod,1,INSTR(nPeriod,' ')-1)) AS n,
  TRIM(MID(nPeriod,INSTR(nPeriod,' ')+1)) AS Period,
  CASE
WHEN Period = 'Month' THEN DATE_ADD

Re: Problem with DATE_ADD - Michael Stassen, July 16 2004 7:48am

[Gerard Gilliland]

Gerard Gilliland replied:

Michael: 

Thank you for your excellent suggestion and quick response.
I now have an operable DATE_ADD.
The solution to break nPeriod into n and Period would not work directly.
That is, in the SELECT statement in the form of ...

$qry = SELECT PlantName, Unit, TagName, CalDate, nPeriod,
TRIM(MID(nPeriod,1,INSTR(nPeriod,' ')-1)) AS n,
TRIM(MID(nPeriod,INSTR(nPeriod,' ')+1)) AS Period,
CASE 
  WHEN Period = 'Month' THEN DATE_ADD(CalDate, INTERVAL n MONTH)
  WHEN Period = 'Year' THEN DATE_ADD(CalDate, INTERVAL n YEAR)
END
AS DueDate
FROM tblTemp;

... would fail (I think) because n and Period were calculated values.
However, I was sucessful using your direction and the other form of CASE
That is, I used the logic in the CASE statement directly:

$qry = SELECT PlantName, Unit, TagName, CalDate, nPeriod,
CASE TRIM(MID(nPeriod,INSTR(nPeriod,' ')+1))
  WHEN 'Month' THEN DATE_ADD(CalDate, INTERVAL 
TRIM(MID(nPeriod,1,INSTR(nPeriod,' ')-1)) MONTH)
  WHEN 'Year' THEN DATE_ADD(CalDate, INTERVAL 
TRIM(MID(nPeriod,1,INSTR(nPeriod,' ')-1)) YEAR)
END
AS DueDate
FROM tblTemp;


Thank you,
Gerard Gilliland
[EMAIL PROTECTED]



Michael Stassen wrote:

With DATE_ADD(date,INTERVAL expr type), date and expr can be dynamic, but 
type must be literally one of the types in the list.  It cannot come from a 
column or function.  It would be nice if it could, but it cannot.

One solution would be to break nperiod into n, an int, and period, a 
char(x).  Then you could

   SELECT ...
   CASE WHEN period = 'month' THEN DATE_ADD(CalDate, INTERVAL n MONTH)
WHEN period = 'year' THEN DATE_ADD(CalDate, INTERVAL n YEAR)
  END
  AS DueDate
   FROM ...

A simpler option, if you can express all your intervals as the same type, 
would be to simply replace nperiod with n.  Given your example, one could 
replace 1 year with 12 month.  That is, put 12 in for n in the 1 year 
case, then run the query to always add months:

   SELECT ...
   DATE_ADD(CalDate, INTERVAL n MONTH) AS DueDate
   FROM ...

Michael


Gerard Gilliland wrote:

 I need some help with a DATE_ADD problem.
 I can't seem to run a query with DATE_ADD(date, INTERVAL expr type)
 where expr and type are dynamic.
 It works fine with date as dynamic.
 I am attempting to add 1 year to 2004-04-15 for a DueDate of 2005-04-15
 and add 3 months to 2004-03-10 for a DueDate of 2004-06-10.
 
 Field Names:
 PlantName, Unit, TagName, CalDate, nPeriod 
 
 Sample Data from Records:
 Valmont, 5, FCV96008, 2004-04-15, 1 Year 
 Valmont, 5, FT0701, 2004-03-10, 3 Month 
 
 This Works: (but it adds 1 year to both records)
 $qry = SELECT PlantName, Unit, TagName, CalDate, nPeriod, 
 DATE_ADD(CalDate, INTERVAL 1 Year) AS DueDate FROM tblTemp;
 Note: 1 Year is static.
 
 This Works: (but it adds 3 months to both records)
 $qry = SELECT PlantName, Unit, TagName, CalDate, nPeriod, 
 DATE_ADD(CalDate, INTERVAL 3 Month) AS DueDate FROM tblTemp;
 Note: 3 Month is static.
 
 This Fails: 
 $qry = SELECT PlantName, Unit, TagName, CalDate, nPeriod, 
 DATE_ADD(CalDate, INTERVAL nPeriod) AS DueDate FROM tblTemp;
 Note: nPeriod is dynamic and contains '1 Year' with FCV96008
  and '3 Month' with FT0701
 Warning message:
 Warning: mysql_num_fields(): supplied argument is not a valid MySQL result resource 
 
 I have also split nPeriod in Pexpr and Ptype using the MID() function 
 and placed them appropriately but that also fails.
 I have changed the data to all caps (YEAR and MONTH) but it also fails.
 
 General server information: July 15, 2004
 Operating system: Linux 
 Kernel version: 2.4.26 
 Apache version: 1.3.31 (Unix) 
 PERL version: 5.8.0 
 Path to PERL: /usr/bin/perl 
 Path to sendmail: /usr/sbin/sendmail 
 PHP version: 4.3.7 
 MySQL version: 4.0.18-standard 
 
 I would appreciate any help.
 Thank you,
 Gerard Gilliland
 [EMAIL PROTECTED]
 
 
 // Table definition:
   $tblName = tblTemp;
 
   $tblDef = PlantName VARCHAR(50), ;
   $tblDef .= Unit VARCHAR(10), ;
   $tblDef .= TagName VARCHAR(255), ;
   $tblDef .= CalDate DATE, ;
   $tblDef .= nPeriod VARCHAR(50) ;
  
   if(!mysql_query(CREATE TABLE $tblName ($tblDef)))
 die ('Cannot Create Table $tblName ' . mysql_error());
 
 // Table Data:
   if(!mysql_query(INSERT INTO $tblName VALUES(
 'Valmont', '5', 'FCV96008', '2004-04-15', '1 Year')))
 die ('Cannot Insert into $tblName ' . mysql_error());
   if(!mysql_query(INSERT INTO $tblName VALUES(
 'Valmont', '5', 'FT0701', '2004-03-10', '3 Month')))
 die ('Cannot Insert into $tblName ' . mysql_error());
  
 

Thread
Problem with DATE_ADD - Gerard Gilliland, July 16 2004 5:16am
Re: Problem with DATE_ADD - Michael Stassen, July 16 2004 7:48am 

© 1995-2004 MySQL AB. All rights reserved.
MySQL.com Home Site Map Contact Us Privacy Policy Trademark Info 

Problem with DATE_ADD

[Gerard Gilliland]

I need some help with a DATE_ADD problem.
I can't seem to run a query with DATE_ADD(date, INTERVAL expr type)
where expr and type are dynamic.
It works fine with date as dynamic.
I am attempting to add 1 year to 2004-04-15 for a DueDate of 2005-04-15
and add 3 months to 2004-03-10 for a DueDate of 2004-06-10.

Field Names:
PlantName, Unit, TagName, CalDate, nPeriod 

Sample Data from Records:
Valmont, 5, FCV96008, 2004-04-15, 1 Year 
Valmont, 5, FT0701, 2004-03-10, 3 Month 

This Works: (but it adds 1 year to both records)
$qry = SELECT PlantName, Unit, TagName, CalDate, nPeriod, 
DATE_ADD(CalDate, INTERVAL 1 Year) AS DueDate FROM tblTemp;
Note: 1 Year is static.

This Works: (but it adds 3 months to both records)
$qry = SELECT PlantName, Unit, TagName, CalDate, nPeriod, 
DATE_ADD(CalDate, INTERVAL 3 Month) AS DueDate FROM tblTemp;
Note: 3 Month is static.

This Fails: 
$qry = SELECT PlantName, Unit, TagName, CalDate, nPeriod, 
DATE_ADD(CalDate, INTERVAL nPeriod) AS DueDate FROM tblTemp;
Note: nPeriod is dynamic and contains '1 Year' with FCV96008
 and '3 Month' with FT0701
Warning message:
Warning: mysql_num_fields(): supplied argument is not a valid MySQL result resource 

I have also split nPeriod in Pexpr and Ptype using the MID() function 
and placed them appropriately but that also fails.
I have changed the data to all caps (YEAR and MONTH) but it also fails.

General server information: July 15, 2004
Operating system: Linux 
Kernel version: 2.4.26 
Apache version: 1.3.31 (Unix) 
PERL version: 5.8.0 
Path to PERL: /usr/bin/perl 
Path to sendmail: /usr/sbin/sendmail 
PHP version: 4.3.7 
MySQL version: 4.0.18-standard 

I would appreciate any help.
Thank you,
Gerard Gilliland
[EMAIL PROTECTED]


// Table definition:
  $tblName = tblTemp;

  $tblDef = PlantName VARCHAR(50), ;
  $tblDef .= Unit VARCHAR(10), ;
  $tblDef .= TagName VARCHAR(255), ;
  $tblDef .= CalDate DATE, ;
  $tblDef .= nPeriod VARCHAR(50) ;
 
  if(!mysql_query(CREATE TABLE $tblName ($tblDef)))
die ('Cannot Create Table $tblName ' . mysql_error());

// Table Data:
  if(!mysql_query(INSERT INTO $tblName VALUES(
'Valmont', '5', 'FCV96008', '2004-04-15', '1 Year')))
die ('Cannot Insert into $tblName ' . mysql_error());
  if(!mysql_query(INSERT INTO $tblName VALUES(
'Valmont', '5', 'FT0701', '2004-03-10', '3 Month')))
die ('Cannot Insert into $tblName ' . mysql_error());
 

Where do I find the warnings (explained)

[Gerard Petersen]

Hi all (again),

Mysql tells methere are warnings during a importW. here can I find the 
content of the warnings that occur.

Greetz Gerard, Thanx.

Who cares if it doesn't do anything?  It was made with our new
Triple-Iso-Bifurcated-Krypton-Gate-MOS process ...

GrtzG
~
:wq!



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

enclosed by clause ignored

[Gerard Petersen]

Hi all,

I'm doing a quick and dirty import/export thingy, using the command LOAD DATA 
INFILE ...
All fields in the table are of the type TEXT. and the text file (2be 
imported) is comma seperated and fields are double quote enclosed.

Without use of the 'ENCLOSED BY' clause field data looks like \foobar\ and 
with the 'ENCLOSED BY' clause they still do except the first field. (Even 
when escaping the chars with a backslash)

Any clue? ... thanx a lot.

Oh and is there a place where I can find the content of the warnings

For the details, here is the command

mysql LOAD DATA INFILE '/home/gerard/files/mysql/adress/jpilot.cvs' INTO 
TABLE adresses FIELDS TERMINATED BY  ',' ENCLOSED BY '' LINES TERMINATED BY 
'\n' IGNORE 1 LINES;

Query OK, 251 rows affected (0.06 sec)
Records: 251  Deleted: 0  Skipped: 0  Warnings: 794


-- 
Who cares if it doesn't do anything?  It was made with our new
Triple-Iso-Bifurcated-Krypton-Gate-MOS process ...

GrtzG
~
:wq!



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php