SQL frustrations

[Ruslan Zasukhin]

on 2/11/04 3:15 PM, Robert Brenstein at rjb at rz.uni-potsdam.de wrote:

> First, a number of queries reported 640 error. They all were fixed by
> including the SORT fields in SELECT. This is weird because the
> release notes state (for one of 1.9.5 betas) that:
> 
> [SQL][Fix] Fixed broken ability to sort on not SELECTed field.

This was changed it seems after adding GROUP BY.

> So this was fixed but apparently broken later again. I wonder whether
> it works in newer versions.

In 1.10 also no.

Actually by Standard ORDER BY must be on SELECTed field only.


> Second, I have a number of queries that now return wrong results (too
> many records). I am still struggling to figure out why, so any tips
> are appreciated. They all involve more complex comparisons with a
> series of and's and or's. I hope applying some extra parenthesis will
> cure these but haven't found the receipe yet.

Rule is simple:

1) use  ()

2) in the WHERE you should group fields of the same table together

        (T1.f1 and T1.f2 and T1.f ... )
    and
        (T2. And or T2 ... )

3) OR on fields from different tables not works.
Can be replaced on 
            fld IN (subselect)


-- 
Best regards,
Ruslan Zasukhin      [ I feel the need...the need for speed ]
-------------------------------------------------------------
e-mail: ruslan at paradigmasoft.com
web: http://www.paradigmasoft.com

To subscribe to the Valentina mail list go to:
http://lists.macserve.net/mailman/listinfo/valentina
-------------------------------------------------------------