MySQL Forums
Forum List  »  Falcon

Inline result in where clause
Posted by: Constantin Iliescu
Date: February 08, 2011 01:46AM

Hello, forum members!

I have this statement:

oxv_oxarticles_1.oxid as oxid,
(SELECT COUNT(oc2.oxid) FROM oxv_oxobject2category_1 AS oc2 WHERE oc2.oxobjectid = oxv_oxarticles_1.oxid AND oc2.oxcatnid = '66d99fc980354defa3c57fa29d73e049') AS discontinuedCount

FROM oxv_oxobject2category_1 as oc left join oxv_oxarticles_1 ON oxv_oxarticles_1.oxid = oc.oxobjectid

oxv_oxarticles_1.oxactive = 1 and
( oxv_oxarticles_1.oxstockflag != 2 or ( oxv_oxarticles_1.oxstock + oxv_oxarticles_1.oxvarstock ) > 0 )
and IF( oxv_oxarticles_1.oxvarcount = 0, 1, ( select 1 from oxv_oxarticles_1 as art where art.oxparentid=oxv_oxarticles_1.oxid and ( art.oxactive = 1 ) and ( art.oxstockflag != 2 or art.oxstock > 0 ) limit 1 ) )
and oxv_oxarticles_1.oxparentid = '' and oc.oxcatnid = '19je01b2c42882dbec9380169dc6e601'
and discontinuedCount = 0
GROUP BY oc.oxcatnid, oc.oxobjectid ORDER BY oc.oxpos, oc.oxobjectid

Without the 'and discontinuedCount = 0' condition in where clause, this works perfect, but with the condition, I get #1054 - Unknown column 'discontinuedCount' in 'where clause'. I doesn't seem normal to me, as long as, without the condition, the result contains a column called discontinuedCount.

Any ideas?

Thank you!

Options: ReplyQuote

Written By
Inline result in where clause
February 08, 2011 01:46AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.