hello
I have a very strange case where the my result depend
on the fields I want return from query :-(
A simplified query (Extracted the Sub queries out for read easyness):
select POL1.*,PRO.ID from oPositionLine POL1 , (SUB2) T4
join pproduct PRO on POL1.ProductID=PRO.ID
where POL1.positionID = T4.PositionID
this works perfect, BUT if I just change to
vvvvvv
select POL1.*,PRO.ID from oPositionLine POL1 , (SUB2) T4
join pproduct PRO on POL1.ProductID=PRO.ID
where POL1.positionID = T4.PositionID
I get empty result
SUB2 = Select OPOL.PositionID from oPositionLine OPOL,oeventtype OET
, (SUB1) t3 where OPOL.PositionID = t3.positionID and
OET.ID=OPOL.EventtypeID
group by OPOL.PositionID,OPOL.ProductID
having sum(OET.SignValue*OPOL.LocalPrice) <>0
Sub1 = select PositionID from opositionline OPOL ,oeventtype OET
,oposition POS
where OET.ID=OPOL.EventtypeID and OET.kind=1 and POS.ID=OPOL.positionID
and POS.finish='false' group by OPOL.PositionID having
sum(OET.signvalue*OPOL.ProductAmount) =0
********** FULL QUERY **************
select POL1.ID,PRO.ID from oPositionLine POL1 ,
(Select OPOL.PositionID from oPositionLine OPOL,oeventtype OET
,(select OPOL.PositionID from opositionline OPOL ,oeventtype OET
,oposition POS
where OET.ID=OPOL.EventtypeID and OET.kind=1 and POS.ID=OPOL.positionID
and POS.finish='false' group by OPOL.PositionID having
sum(OET.signvalue*OPOL.ProductAmount) =0) t3
where OPOL.PositionID = t3.positionID and OET.ID=OPOL.EventtypeID
group by OPOL.PositionID,OPOL.ProductID
having sum(OET.SignValue*OPOL.LocalPrice) <>0 ) T4
POS2.InvoiceAddrID = A.ID
join pproduct PRO on POL1.ProductID=PRO.ID
where POL1.positionID = T4.PositionID
********** FULL QUERY END **************
Using 4.1.0-alfa-log
regards
Kim G. Pedersen
macaos/elprint Development
+45 35373808
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
