[V4RB] SQL again ... arrrgh!

[Ivan Smahin]

Hello Pedro,

Tuesday, October 26, 2004, 7:04:24 PM, you wrote:

Pf> G'day Folks

Pf> I'm trying to construct a set of canned searched in my current project
Pf> & while most are working fine I'm stumbling on one pair. They are to
Pf> find records where one VString field is empty and not empty 
Pf> respectively. As a precaution I've tried to construct my searches to
Pf> cover both possibilities, that an empty field is an empty string and
Pf> that an empty field is null.

Pf> The SQL strings I've tried so far are (in pairs for the empty field
Pf> then the not empty field)  ...

Pf> SELECT ... WHERE ( NOT extention LIKE '' and NOT extention = NULL )
Pf> ORDER BY name ASC
Pf> SELECT ... WHERE ( extention LIKE '' or extention = NULL ) ORDER BY
Pf> name ASC

Pf> SELECT ... WHERE ( NOT extention = '' and NOT extention = NULL ) ORDER
Pf> BY name ASC
Pf> SELECT ... WHERE ( extention = '' or extention = NULL ) ORDER BY name
Pf> ASC

Pf> SELECT ... WHERE ( NOT extention = '' and NOT extention IS null ) ORDER
Pf> BY name ASC
Pf> SELECT ... WHERE ( extention = '' or extention IS null ) ORDER BY name
Pf> ASC

Pf> SELECT ... WHERE NOT ( extention = '' or extention IS null ) ORDER BY
Pf> name ASC
Pf> SELECT ... WHERE ( extention = '' or extention IS null ) ORDER BY name
Pf> ASC

Pf> Any tips on where I'm going wrong? If possible I'd prefer to negate
Pf> outside the parentheses as in my last attempt but it's not a big issue
Pf> if that's not possible.

Pf> Cheers, Pedro :-)

Pf> Web: <http://www.pedro.net.au>                    PGP Key ID: 387CD96F
Pf> Instant messaging...  AIM: bandidoOfOz  ICQ: 27671678  Jabber: pedrofp
Pf>                        MSN: mail at pedro.net.au           Yahoo: pedro_fp
Pf> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Pf> "How would we think if we saw the twenty-first century as
Pf> 'The Healing Century' and left the economic emphasis behind
Pf> in the twentieth?"
Pf>                                        Robert Theobald, 1998

Pf> _______________________________________________
Pf> Valentina mailing list
Pf> Valentina at lists.macserve.net
Pf> http://lists.macserve.net/mailman/listinfo/valentina


I'm not familiar with 1.0 but anyway you should not think that it is possible
to compare NULL-value with any another value. Strictly speaking the result of such
comparison is undefined. So you will get a zero result.
Some DBMS allow to set such mode, but it's allowed for "equal" only.
(I mean - mode when NULL is equal to NULL and not-NULL is NOT equal to NULL).
But I believe this is not good thing. You, or another person might be
confused with such query.
As far as I know valentina does not have such mode.


-- 
Best regards,
 Ivan                            mailto:IvanSmahin at public.kherson.ua