VFP6: Query Problem

What is wrong with
select left(workdesc,50) as wd,* from cwkt;
where left(funccode,1)="$" and "P#"$wd order by wd
where workdesc is a memo?

I do not get an error message, but I also get no rows returned.
I expected plenty, and the rows are there. If I replace the second
conjunct with
"P#"$workdesc
it works.

What is the difference in meaning between the two statements?
("P#" will normally be at the beginning of the memo.)

Sincerely,

Gene Wirchenko

Fred
Tue Mar 22 14:03:27 CST 2005

"wd" is not yet known by the WHERE condition parser. It doesn't become
known until the records are retrieved and needed for the ORDER BY. At
least, that's my SWAG on how it works, I have no inside actual knowledge of
the way the VFP SQL parser works.

--
Fred
Microsoft Visual FoxPro MVP


"Gene Wirchenko" <genew@ucantrade.com.NOTHERE> wrote in message
news:5fq0419s5v6lg87jitouvn0rkba75ckbtt@4ax.com...
> What is wrong with
> select left(workdesc,50) as wd,* from cwkt;
> where left(funccode,1)="$" and "P#"$wd order by wd
> where workdesc is a memo?
>
> I do not get an error message, but I also get no rows returned.
> I expected plenty, and the rows are there. If I replace the second
> conjunct with
> "P#"$workdesc
> it works.
>
> What is the difference in meaning between the two statements?
> ("P#" will normally be at the beginning of the memo.)
>
> Sincerely,
>
> Gene Wirchenko
>

Al
Tue Mar 22 14:43:30 CST 2005

will work if change to
having "P#"$wd

al

"Gene Wirchenko" <genew@ucantrade.com.NOTHERE> wrote in message
news:5fq0419s5v6lg87jitouvn0rkba75ckbtt@4ax.com...
> What is wrong with
> select left(workdesc,50) as wd,* from cwkt;
> where left(funccode,1)="$" and "P#"$wd order by wd
> where workdesc is a memo?
>
> I do not get an error message, but I also get no rows returned.
> I expected plenty, and the rows are there. If I replace the second
> conjunct with
> "P#"$workdesc
> it works.
>
> What is the difference in meaning between the two statements?
> ("P#" will normally be at the beginning of the memo.)
>
> Sincerely,
>
> Gene Wirchenko
>

Paul
Tue Mar 22 14:43:59 CST 2005

I agree.

Also, PADR(workdesc, 50) would be better than LEFT(workdesc, 50), because
you might run into a short workdesc some day, and your resulting field will
then be smaller than you expect.




"Fred Taylor" <ftaylor@mvps.org!REMOVE> wrote in message
news:Oi$r1oxLFHA.3832@TK2MSFTNGP12.phx.gbl...
> "wd" is not yet known by the WHERE condition parser. It doesn't become
> known until the records are retrieved and needed for the ORDER BY. At
> least, that's my SWAG on how it works, I have no inside actual knowledge
> of the way the VFP SQL parser works.
>
> --
> Fred
> Microsoft Visual FoxPro MVP
>
>
> "Gene Wirchenko" <genew@ucantrade.com.NOTHERE> wrote in message
> news:5fq0419s5v6lg87jitouvn0rkba75ckbtt@4ax.com...
>> What is wrong with
>> select left(workdesc,50) as wd,* from cwkt;
>> where left(funccode,1)="$" and "P#"$wd order by wd
>> where workdesc is a memo?
>>
>> I do not get an error message, but I also get no rows returned.
>> I expected plenty, and the rows are there. If I replace the second
>> conjunct with
>> "P#"$workdesc
>> it works.
>>
>> What is the difference in meaning between the two statements?
>> ("P#" will normally be at the beginning of the memo.)
>>
>> Sincerely,
>>
>> Gene Wirchenko
>>
>
>

Ook
Tue Mar 22 14:51:30 CST 2005

select PADR( left(workdesc,50), 50 ) as wd,* from cwkt;
where left(funccode,1)="$" and "P#"$wd order by wd

Selecting from a memo can be problematic. Always PAD the returned string.
However, in 7 and above I get an error. I'm surprised VFP6 doesn't also
choke on this. The following works for me:

select PADR( left(workdesc,50), 50 ) as wd,* from cwkt;
where left(funccode,1)="$" and "P#"$LEFT(workdesc,50) order by wd




"Gene Wirchenko" <genew@ucantrade.com.NOTHERE> wrote in message
news:5fq0419s5v6lg87jitouvn0rkba75ckbtt@4ax.com...
> What is wrong with
> select left(workdesc,50) as wd,* from cwkt;
> where left(funccode,1)="$" and "P#"$wd order by wd
> where workdesc is a memo?
>
> I do not get an error message, but I also get no rows returned.
> I expected plenty, and the rows are there. If I replace the second
> conjunct with
> "P#"$workdesc
> it works.
>
> What is the difference in meaning between the two statements?
> ("P#" will normally be at the beginning of the memo.)
>
> Sincerely,
>
> Gene Wirchenko
>

Ook
Tue Mar 22 15:00:21 CST 2005

Paul is right. PADR( LEFT( ... is a bad habit I picked up years ago.....
PADR( ... should be sufficient.

"Ook" <usenet@nospam.zootal.nomorespam.com> wrote in message
news:ua9ztDyLFHA.2252@TK2MSFTNGP15.phx.gbl...
> select PADR( left(workdesc,50), 50 ) as wd,* from cwkt;
> where left(funccode,1)="$" and "P#"$wd order by wd
>
> Selecting from a memo can be problematic. Always PAD the returned string.
> However, in 7 and above I get an error. I'm surprised VFP6 doesn't also
> choke on this. The following works for me:
>
> select PADR( left(workdesc,50), 50 ) as wd,* from cwkt;
> where left(funccode,1)="$" and "P#"$LEFT(workdesc,50) order by wd
>
>
>
>
> "Gene Wirchenko" <genew@ucantrade.com.NOTHERE> wrote in message
> news:5fq0419s5v6lg87jitouvn0rkba75ckbtt@4ax.com...
> > What is wrong with
> > select left(workdesc,50) as wd,* from cwkt;
> > where left(funccode,1)="$" and "P#"$wd order by wd
> > where workdesc is a memo?
> >
> > I do not get an error message, but I also get no rows returned.
> > I expected plenty, and the rows are there. If I replace the second
> > conjunct with
> > "P#"$workdesc
> > it works.
> >
> > What is the difference in meaning between the two statements?
> > ("P#" will normally be at the beginning of the memo.)
> >
> > Sincerely,
> >
> > Gene Wirchenko
> >
>
>

Anders
Wed Mar 23 04:14:09 CST 2005

Yes but that's because HAVING filters out from the full retrieved set, while
WHERE excludes rows from ever being added to the retrieved set. It can mean
that millions of unnecessary rows are captured into an intermediary set
before creating a final filtered set. HAVING is only supposed to be used
with gruoped calcululations such as GROUP BY key1 HAVING COUNT*() |
SUM(amount) >=n . The value of n for each key1 is only known after all the
rows have been counted or summed.
-Anders

"Al Marino" <amarino@NOT_THISgalaxymusic.com> wrote in message
news:#KFbs9xLFHA.3336@TK2MSFTNGP10.phx.gbl...
> will work if change to
> having "P#"$wd
>
> al
>
> "Gene Wirchenko" <genew@ucantrade.com.NOTHERE> wrote in message
> news:5fq0419s5v6lg87jitouvn0rkba75ckbtt@4ax.com...
> > What is wrong with
> > select left(workdesc,50) as wd,* from cwkt;
> > where left(funccode,1)="$" and "P#"$wd order by wd
> > where workdesc is a memo?
> >
> > I do not get an error message, but I also get no rows returned.
> > I expected plenty, and the rows are there. If I replace the second
> > conjunct with
> > "P#"$workdesc
> > it works.
> >
> > What is the difference in meaning between the two statements?
> > ("P#" will normally be at the beginning of the memo.)
> >
> > Sincerely,
> >
> > Gene Wirchenko
> >
>
>