From: Peter Brawley Date: June 8 2006 2:42am Subject: Re: functions in AS List-Archive: http://lists.mysql.com/mysql/198705 Message-Id: <44878E96.7000007@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-44878E973A52=======" --=======AVGMAIL-44878E973A52======= Content-Type: multipart/alternative; boundary=------------040102030508090206040404 --------------040102030508090206040404 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Jay, > [snip] > >> We all know that you cannot do something like this; >> sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS curdate() >> nor can you use user variables even though they get set properly >> set @d1 = curdate(); >> sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS @d1 >> So, does anyone know of a work around? >> > How about PREPARE? > [/snip] > > PREPARE treats SELECT statements the same, unless I am missing > something. I have done some testing, and have been able to obtain the > desired results. Even the simplest example; > > PREPARE stmt1 FROM 'SELECT curdate() AS ?'; > SET @a = curdate(); > EXECUTE stmt1 USING @a; > > Gives a syntax error on the PREPARE statement since AS cannot be a > function. Perhaps there is something to a prepare that I should be more > aware of? > SET @sql=CONCAT('SELECT SUM(IF(SUBSTRING(updated,1,10)=CURDATE(), 1, 0)) AS ', char(39), CURDATE(), CHAR(39), ' FROM tablename GROUP BY group by psDealer' ); PREPARE stmt FROM @sql; PB ----- ; > > > --------------040102030508090206040404 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Jay,
[snip]
  
We all know that you cannot do something like this;
sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS curdate()
nor can you use user variables even though they get set properly
set @d1 = curdate();
sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS @d1
So, does anyone know of a work around? 
    
How about PREPARE?
[/snip]

PREPARE treats SELECT statements the same, unless I am missing
something. I have done some testing, and have been able to obtain the
desired results. Even the simplest example;

PREPARE stmt1 FROM 'SELECT curdate() AS ?';
SET @a = curdate();
EXECUTE stmt1 USING @a;

Gives a syntax error on the PREPARE statement since AS cannot be a
function. Perhaps there is something to a prepare that I should be more
aware of?
SET @sql=CONCAT('SELECT SUM(IF(SUBSTRING(updated,1,10)=CURDATE(), 1, 0)) AS ',
                char(39),
                CURDATE(),
                CHAR(39),
                ' FROM tablename GROUP BY group by psDealer' );
PREPARE stmt FROM @sql;

PB

-----                ;
--------------040102030508090206040404-- --=======AVGMAIL-44878E973A52======= Content-Type: text/plain; x-avg=cert; charset=us-ascii Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Description: "AVG certification" No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.8.3/358 - Release Date: 6/7/2006 --=======AVGMAIL-44878E973A52=======--