List:General Discussion« Previous MessageNext Message »
From:Daniel Kraft Date:April 22 2011 8:05pm
Subject:Re: WHERE does not work on calculated view field
View as plain text  
Hi,

thanks for the fast reply!

On 04/22/11 21:39, Daevid Vincent wrote:
>> DROP DATABASE `test`;
>> CREATE DATABASE `test`;
>> USE `test`;
>>
>> CREATE TABLE `mytable`
>>     (`ID` SERIAL,
>>      `Type` INTEGER UNSIGNED NULL,
>>      PRIMARY KEY (`ID`));
>> INSERT INTO `mytable` (`Type`) VALUES (NULL);
>>
>> CREATE TABLE `types`
>>     (`ID` SERIAL,
>>      `Name` TEXT NOT NULL,
>>      PRIMARY KEY (`ID`));
>> INSERT INTO `types` (`Name`) VALUES ('Type A'), ('Type B');
>>
>> DELIMITER |
>> CREATE FUNCTION `EMPTY_STRING` (value TEXT)
>> RETURNS TEXT
>> DETERMINISTIC
>> BEGIN
>>     RETURN IF(value IS NULL, '', value);
>> END|
>> DELIMITER ;
>>
>> CREATE VIEW `myview` AS
>>     SELECT a.*, EMPTY_STRING(b.`Name`) AS `TypeName`
>>       FROM `mytable` a
>>         LEFT JOIN `types` b ON a.`Type` = b.`ID`;
>>
>> SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NULL;
>> SELECT COUNT(*) FROM `myview` WHERE `TypeName` IS NOT NULL;
>> SELECT COUNT(*) FROM `myview` WHERE `TypeName` LIKE '%';
>>
>> (I tried to simplify my problem as far as possible.)  When I run this
>> against MySQL 5.0.24a, I get three times "0" as output from the SELECTs
>> at the end -- shouldn't at least one of them match the single row?
>> (Preferably first and third ones.)
>>
>> What am I doing wrong here?  I have no clue what's going on...  Thanks a
>> lot!
>
> Try this maybe:
>
>   SELECT COUNT(*) FROM `myview` HAVING `TypeName` IS NULL;
>   SELECT COUNT(*) FROM `myview` HAVING `TypeName` IS NOT NULL;
>   SELECT COUNT(*) FROM `myview` HAVING `TypeName` LIKE '%';

When I try those, I get:

ERROR 1054 (42S22) at line 35: Unknown column 'TypeName' in 'having clause'

What would be the difference?  (I've never used HAVING before.)

Yours,
Daniel

-- 
http://www.pro-vegan.info/
--
Done:  Arc-Bar-Cav-Kni-Ran-Rog-Sam-Tou-Val-Wiz
To go: Hea-Mon-Pri
Thread
WHERE does not work on calculated view fieldDaniel Kraft22 Apr
  • RE: WHERE does not work on calculated view fieldDaevid Vincent22 Apr
    • Re: WHERE does not work on calculated view fieldDaniel Kraft22 Apr
      • RE: WHERE does not work on calculated view field - Found word(s)list error in the Text bodyLarry McGhaw22 Apr
        • Re: WHERE does not work on calculated view field - Found word(s)list error in the Text bodyCarsten Pedersen22 Apr
        • Re: WHERE does not work on calculated view field - Found word(s)list error in the Text bodyDaniel Kraft24 Apr
          • RE: WHERE does not work on calculated view field - Found word(s)list error in the Text bodyLarry McGhaw25 Apr
            • Re: WHERE does not work on calculated view field - Found word(s)list error in the Text bodyDaniel Kraft25 Apr
            • RE: WHERE does not work on calculated view field - Foundword(s) list error in the Text bodyhsv26 Apr
              • Re: WHERE does not work on calculated view field - Found word(s)list error in the Text bodyDaniel Kraft26 Apr
  • Re: WHERE does not work on calculated view fieldCarsten Pedersen22 Apr
    • Re: WHERE does not work on calculated view fieldDaniel Kraft22 Apr
      • Re: WHERE does not work on calculated view fieldars k23 Apr
        • Re: WHERE does not work on calculated view fieldDaniel Kraft23 Apr