List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:June 8 2006 2:42am
Subject:Re: functions in AS
View as plain text  
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

-----                ;

>
>
>   

Attachment: [text/html]
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
Thread
functions in ASJay Blanchard7 Jun
  • Re: functions in ASPeter Brawley7 Jun
RE: functions in ASJay Blanchard7 Jun
  • Re: functions in ASPeter Brawley8 Jun
RE: functions in ASJay Blanchard8 Jun