[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

Another tip:
You say " the string field is empty" - so I think that NULL value is the best
description. "Empty string" approach  is possible but not so clear.
I would say that empty string is value with zero length but it is DEFINED
value - you see?

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