Re: WHERE does not work on calculated view field - Found word(s) list error in the Text body

[Daniel Kraft]

Hi,

On 04/25/11 20:45, Larry McGhaw wrote:
> My best advice is to not use a custom MySQL function in a view when the parameter to
> that function
> is a column or expression that has the potential to result in NULL because of being
> on the right side
> of a left outer join (or the left side of a right outer join).   This particular set
> of circumstances
> seems to cause MySQL to treat the resulting expression as "unknown" when used in a
> where clause on the view
> itself - such that any use of the expression in the where clause will evaluate to
> unknown/false.
>
> As a workaround - this view for example behaves as expected:
>
> CREATE VIEW `myview2` AS
>        SELECT a.*, IF(b.`Name` IS NULL, '', b.`Name`) AS `TypeName`
>          FROM `mytable` a
>            LEFT JOIN `types` b ON a.`Type` = b.`ID`;

now you mention it, it seems obvious -- but I didn't think about that 
solution before...  But 'inlining' my function this way seems to fix the 
problem also in my real application.

Thanks a lot!

Yours,
Daniel

-- 
http://www.pro-vegan.info/
--
Done:  Arc-Bar-Cav-Kni-Ran-Rog-Sam-Tou-Val-Wiz
To go: Hea-Mon-Pri