>>>I have been getting unusual results from queries containing inner joins, repeated records (just one or two) or nonexistent records (again only one or two). Are there any known bugs in sql select's? and if so, do you know any ways of avoiding them?
>>>
>>>Thanks.
>>>
>>>Ricardo.
>>
>>Can you post sample SQL statement?
>
>SELECT *;
> FROM socios;
> GROUP BY Socios.cuenta;
> INTO CURSOR socs
>
>SELECT Movimtos.fecha, Socs.nombre, Movimtos.cuenta,;
> operacio.descripcio, ;
> iif( Movimtos.importe >= 0, Movimtos.importe, 000000000.00) as debe,;
> iif( Movimtos.importe < 0, -Movimtos.importe, 000000000.00) as haber;
> FROM movimtos LEFT OUTER JOIN socs ;
> LEFT OUTER JOIN operacio ;
> ON Movimtos.cuenta = Socs.cuenta ;
> ON Movimtos.operacion = Operacio.operacion ;
> ORDER BY Movimtos.Fecha, Movimtos.cuenta;
> INTO CURSOR Listmovs
>
>this one will put the same Socs.nombre in all the resulting records.
Hi Ricardo,
Outer join results depend upon the order in which the operations are performed. The results of:
(movimtos outer join socs) outer join operacio on..
will be different than those of:
movimtos left outer join socs on movimtos.cuenta=socs.cuenta, ;
movimtos left outer join operacio on movimtos.operacion=operacio.operacio
That is, if you're trying to get all records from movimtos and matching ones from socs and operacio. Be prepared to wait a bit on this kind of join though, if you have a lot of records. Didn't look much into other details, but since you're already breaking it up in two statements, it looks like if you brake it down further you'll get a faster response.
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only