Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Cetin Basoz
  Where is Cetin Basoz?
 Izmir
 Turkey
 Cetin Basoz
 To: suhas hegde
  Where is suhas hegde?
 sirsi
 India
 suhas hegde
 Tags
Subject: RE: Help with SQL statement
Thread ID: 154422 Message ID: 154692 # Views: 2 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Tuesday, December 18, 2007 9:06:34 PM         
   


> >
> > Hi Mike,
> > Instead of empty use values representing empty values for that field datatyep so it can utilize existing indexes. ie:
> >
> >
myNumField = 0
> > myDate_Or_DT_Field = {}
> > myCharField == ""
> > !myLogical

> >
> > Second put fields that have indexes defined and/or likely to evaluate false-(with and)/true-(with or) in front in where clause to utilize VFP's shortcut feature.
> >
> > Something like:
> >
Select Project.* From Project
> >    Where Project.cprojno In  ;
> >    (;
> >      Select Data.cprojno ;
> >    	From ('Data') dt ;
> >    	inner join ('param') prm on prm.cparamurn = dt.cparamurn ;
> >    	Where ;
> >    	  Prm.niddoctype = 0 and ;
> >  	  dt.nquantity = 0 And ;
> >  	  !Dt.lprojspec And ;
> >  	  Dt.mnotes == "" And ;
> >  	  Dt.cbox1 == "" And ;
> > 	... ;
> >         !(dt.cparamdata == "") ;
> >    ) ;
> >  	ORDER By Project.cprojno ;
> >  	Into Cursor _2920FR6OG ;
> >  	Readwrite

> >
> > PS: Cursor name is a little hard to read sys15 value, I would use a menaingfull name:)
> >
> > Cetin Basoz
>
>
> hi,
> What would happen if the empty field would contain a Space ?
>
> For example
>
> Create cursor foo (f1 c(10))
> Append blank
> ?'Empty status =' ,Empty(f1)
> ?'Comparision =' ,f1 == ""
> 

>
> suhashegde

Suhas,
With SQL == implies an ANSI compatible serach which means == "" would be treated as:

FieldContent = space(len(field))

With ?, seek() etc you can't test that effect because it uses "set exact" and its behavior is totally different - then it should be like:

? field == space(len(field))

SQL ignore "set exact" and by definition in SQL:

LeftOperand = RightOperand

is equivalant to:

RightOperand = LeftOperand

(That's not the case with xBase series commands).

IOW with SQL series commands:

FirstName == "suhas"
FirstName == "suhas               "
"suhas" == FirstName
"suhas"+space(10) == FirstName+space(20)

all yield the same result meaning firstName is exactly "suhas" (trailing spaces are ignored no matter how many). Thus:

Field == ""


always means empty(Field) in SQL (char field) no matter what the current EXACT or ANSI is - EXACT has no effect anyway.

Cetin Basoz

ENTIRE THREAD

Help with SQL statement Posted by Mike Gagnon @ 12/15/2007 1:30:11 PM
RE: Help with SQL statement Posted by Borislav Borissov @ 12/15/2007 2:54:07 PM
RE: Help with SQL statement Posted by Mike Gagnon @ 12/15/2007 3:05:27 PM
RE: Help with SQL statement Posted by Borislav Borissov @ 12/15/2007 3:20:50 PM
RE: Help with SQL statement Posted by Bernard Bout @ 12/15/2007 3:20:21 PM
RE: Help with SQL statement Posted by Mike Gagnon @ 12/15/2007 3:56:26 PM
RE: Help with SQL statement Posted by suhas hegde @ 12/16/2007 4:43:13 PM
RE: Help with SQL statement Posted by Tamar Granor @ 12/17/2007 10:24:26 PM
RE: Help with SQL statement Posted by Mike Gagnon @ 12/17/2007 11:23:35 PM
RE: Help with SQL statement Posted by Cetin Basoz @ 12/18/2007 1:14:39 PM
RE: Help with SQL statement Posted by suhas hegde @ 12/18/2007 2:48:28 PM
RE: Help with SQL statement Posted by Cetin Basoz @ 12/18/2007 9:06:34 PM
RE: Help with SQL statement Posted by suhas hegde @ 12/19/2007 4:03:39 AM
RE: Help with SQL statement Posted by Cetin Basoz @ 12/19/2007 12:30:56 PM
RE: Help with SQL statement Posted by suhas hegde @ 12/19/2007 12:53:16 PM
RE: Help with SQL statement Posted by Cetin Basoz @ 12/19/2007 1:22:51 PM
RE: Help with SQL statement Posted by suhas hegde @ 12/19/2007 3:55:12 PM
RE: Help with SQL statement Posted by Mike Gagnon @ 12/18/2007 4:04:56 PM