Inline result in where clause
Hello, forum members!
I have this statement:
SELECT
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
WHERE (
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!
Subject
Written By
Posted
Inline result in where clause
February 08, 2011 01:46AM
February 08, 2011 03:11AM
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.