Newbie Visual Foxpro 6.0 SQL Question 
Author Message
 Newbie Visual Foxpro 6.0 SQL Question

I'm new to all this stuff, so please forgive my newbie-ness. ;-)

I am using Visual FoxPro 6.0.  I have a table named "customer" with
two fields in it.  One is "id" and one is "name".  They are both
character fields ("id" is 12 chars and "name" is 64 chars).  The table
contains about 50,000 records.  The "id" field often contains the
exact same ID for many different customers.  

I want to make a few select statements, one of which has stumped me. I
have two select statements that gather the info I need so far that
have the output of:

example query 1 results (ordered by "name"):
Z001 - Customer A
V222 - Customer B
XX03 - Customer C
P001 - Customer D
1J09 - Customer E
V222 - Customer F
Z001 - Customer G

example query 2 results (ordered by "id"):
1J09 - Customer E
P001 - Customer D
V222 - Customer B
V222 - Customer F
XX03 - Customer C
Z001 - Customer A
Z001 - Customer G

Those two were easy.  However the third one has totally alluded me.  I
want to be able to pick any "id" to put at the top of the list and
then sort the rest by name.  Is this possible to do in a single SQL
statement?

For example, I'd like to pick all "Z001" and place them at the top of
the query results in ascending order and then put the rest of the
results in ascending order by name.

example results:
Z001 - Customer A
Z001 - Customer G
V222 - Customer B
XX03 - Customer C
P001 - Customer D
1J09 - Customer E
V222 - Customer F

I hope that all made sense.  Any advice or help would be hugely
appreciated!

Thanks in advance,
D.B. Stepp



Sun, 16 May 2004 06:32:05 GMT  
 Newbie Visual Foxpro 6.0 SQL Question
Here's one approach . . .

LOCAL cID
cID = 'Z001'

SELECT ID, NAME, IIF(ID == cID, SPACE(LEN(ID)), ID) AS SortKey ;
    FROM Customer ;
    ORDER BY SortKey, Name

or:

SELECT ID, NAME, STRTRAN(ID, cID, SPACE(LEN(ID))) AS SortKey ;
    FROM Customer ;
    ORDER BY SortKey, Name

Note that the generated SortKey field must be a string of spaces as wide as
the ID field - an empty string will cause problems if cID matches the first
record processed by the SELECT command.

I haven't tested either, but I suspect that the first version would be
quicker - especially if you have an index tag on ID.  (And if you don't, DO
SO!)

 - Rush


Quote:
> I'm new to all this stuff, so please forgive my newbie-ness. ;-)

> I am using Visual FoxPro 6.0.  I have a table named "customer" with
> two fields in it.  One is "id" and one is "name".  They are both
> character fields ("id" is 12 chars and "name" is 64 chars).  The table
> contains about 50,000 records.  The "id" field often contains the
> exact same ID for many different customers.

> I want to make a few select statements, one of which has stumped me. I
> have two select statements that gather the info I need so far that
> have the output of:

> example query 1 results (ordered by "name"):
> Z001 - Customer A
> V222 - Customer B
> XX03 - Customer C
> P001 - Customer D
> 1J09 - Customer E
> V222 - Customer F
> Z001 - Customer G

> example query 2 results (ordered by "id"):
> 1J09 - Customer E
> P001 - Customer D
> V222 - Customer B
> V222 - Customer F
> XX03 - Customer C
> Z001 - Customer A
> Z001 - Customer G

> Those two were easy.  However the third one has totally alluded me.  I
> want to be able to pick any "id" to put at the top of the list and
> then sort the rest by name.  Is this possible to do in a single SQL
> statement?

> For example, I'd like to pick all "Z001" and place them at the top of
> the query results in ascending order and then put the rest of the
> results in ascending order by name.

> example results:
> Z001 - Customer A
> Z001 - Customer G
> V222 - Customer B
> XX03 - Customer C
> P001 - Customer D
> 1J09 - Customer E
> V222 - Customer F

> I hope that all made sense.  Any advice or help would be hugely
> appreciated!

> Thanks in advance,
> D.B. Stepp



Sun, 16 May 2004 07:54:08 GMT  
 Newbie Visual Foxpro 6.0 SQL Question


Quote:
> I am using Visual FoxPro 6.0.  I have a table named "customer" with
> two fields in it.  One is "id" and one is "name".  They are both
> character fields ("id" is 12 chars and "name" is 64 chars).  The table
> contains about 50,000 records.  The "id" field often contains the
> exact same ID for many different customers.  

> I want to make a few select statements, one of which has stumped me. I
> have two select statements that gather the info I need so far that
> have the output of:

> example query 1 results (ordered by "name"):
> Z001 - Customer A
> V222 - Customer B
> XX03 - Customer C
> P001 - Customer D
> 1J09 - Customer E
> V222 - Customer F
> Z001 - Customer G

> example query 2 results (ordered by "id"):
> 1J09 - Customer E
> P001 - Customer D
> V222 - Customer B
> V222 - Customer F
> XX03 - Customer C
> Z001 - Customer A
> Z001 - Customer G

> Those two were easy.  However the third one has totally alluded me.  I
> want to be able to pick any "id" to put at the top of the list and
> then sort the rest by name.  Is this possible to do in a single SQL
> statement?

> For example, I'd like to pick all "Z001" and place them at the top of
> the query results in ascending order and then put the rest of the
> results in ascending order by name.

> example results:
> Z001 - Customer A
> Z001 - Customer G
> V222 - Customer B
> XX03 - Customer C
> P001 - Customer D
> 1J09 - Customer E
> V222 - Customer F

> I hope that all made sense.  Any advice or help would be hugely
> appreciated!

Here is one way to do it :

SELECT !(id="Z001") AS lid, Name FROM ... ORDER BY lid, Name

Christian Desbourse (Belgium)



Sun, 16 May 2004 09:42:29 GMT  
 Newbie Visual Foxpro 6.0 SQL Question


<snip>

Quote:
> Here is one way to do it :

> SELECT !(id="Z001") AS lid, Name FROM ... ORDER BY lid, Name

> Christian Desbourse (Belgium)

Elegant!

 - Rush



Sun, 16 May 2004 13:50:59 GMT  
 Newbie Visual Foxpro 6.0 SQL Question


Quote:
>LOCAL cID
>cID = 'Z001'

>SELECT ID, NAME, IIF(ID == cID, SPACE(LEN(ID)), ID) AS SortKey ;
>    FROM Customer ;
>    ORDER BY SortKey, Name

This one worked perfectly for me - thank you!

I'd like to ask one other question, if I may.

I'd like to do the same type of thing as the above example, but
instead of using a single ID, I'd like to use a table of IDs.  I'd
also like to select from the list by "company" and not ID.

For example, I'd like to select "APlus Hardware Store" from a list and
get the results below.  All records that have the company name "APlus
Hardware Store" are at the top of the list, sorted by product in
ascending order.  Following "APlus Hardware Store" comes any other
store sorted by product (not sorted by store name and then product,
just by product).  

Results:
APlus Hardware Store - Drill
APlus Hardware Store - Screwdriver
APlus Hardware Store - Wrench
Wendy's HandMade Hammers - Hammer
Tom's Widgets - Widget

using these tables:

product.dbf
id char(16) - product char(64)
ABC123 - Widget
567XYZ - Screwdriver
A4R5 - Hammer
T678Y - Wrench
BB - Drill

accounts.dbf
id char(16) - company char(64)
BB - APlus Hardware Store
A4R5 - Wendy's HandMade Hammers
ABC123 - Tom's Widgets
T678Y - APlus Hardware Store
567XYZ - APlus Hardware Store

I learn best through example, so if anyone has any offerings on this
SELECT statement, I'd love to hear them.  I read through the stuff
that comes with VFP 6.0 for optimizing and speeding up code, but is
there a good website that explains simple ways to improve performance?
The Microsoft documentation isn't always as clear as I'd like it (or
need it) to be.  Are they any well written Visual Foxpro 6 books
available?  Websites?  Are there any good books with information on
using SQL SELECT statements in VFP 6?  

Thank you in advance,
D.B.



Mon, 17 May 2004 05:38:13 GMT  
 Newbie Visual Foxpro 6.0 SQL Question
On Wed, 28 Nov 2001 02:42:29 +0100, Christian Desbourse

Quote:

>Here is one way to do it :

>SELECT !(id="Z001") AS lid, Name FROM ... ORDER BY lid, Name

This method worked great, too.  So, I'm to understand that when
sorting on a logical field, .f. results come first then .t.?  I was
wondering what I should do in times when the case is different or
there are spaces but I still want a match?  Can I use:

SELECT !(alltrim(lower(id))="Z001") AS lid, Name FROM ... ORDER BY
lid, Name

Would that work? Or is there a better, faster way?

Thank you very much for your help,
D.B.



Mon, 17 May 2004 05:52:37 GMT  
 Newbie Visual Foxpro 6.0 SQL Question


Quote:
> This method worked great, too.  So, I'm to understand that when
> sorting on a logical field, .f. results come first then .t.?  I was
> wondering what I should do in times when the case is different or
> there are spaces but I still want a match?  Can I use:

Yes, .F. comes before .T., this is why you need the ! before the
logical expression.

Quote:
> SELECT !(alltrim(lower(id))="Z001") AS lid, Name FROM ... ORDER BY
> lid, Name

> Would that work? Or is there a better, faster way?

I suppose you meant !(ALLTRIM(UPPER(id))="Z001") or
!(ALLTRIM(LOWER(id))="z001")

This means you want to group all id's starting with Z001 or z001

You can also use == instead of = in the expression if you want Z001 but
not Z001X

Christian Desbourse (Belgium)



Mon, 17 May 2004 10:06:52 GMT  
 Newbie Visual Foxpro 6.0 SQL Question

Quote:


> >LOCAL cID
> >cID = 'Z001'

> >SELECT ID, NAME, IIF(ID == cID, SPACE(LEN(ID)), ID) AS SortKey ;
> >    FROM Customer ;
> >    ORDER BY SortKey, Name

> This one worked perfectly for me - thank you!

> I'd like to ask one other question, if I may.

> I'd like to do the same type of thing as the above example, but
> instead of using a single ID, I'd like to use a table of IDs.  I'd
> also like to select from the list by "company" and not ID.

> For example, I'd like to select "APlus Hardware Store" from a list and
> get the results below.  All records that have the company name "APlus
> Hardware Store" are at the top of the list, sorted by product in
> ascending order.  Following "APlus Hardware Store" comes any other
> store sorted by product (not sorted by store name and then product,
> just by product).

> Results:
> APlus Hardware Store - Drill
> APlus Hardware Store - Screwdriver
> APlus Hardware Store - Wrench
> Wendy's HandMade Hammers - Hammer
> Tom's Widgets - Widget

> using these tables:

> product.dbf
> id char(16) - product char(64)
> ABC123 - Widget
> 567XYZ - Screwdriver
> A4R5 - Hammer
> T678Y - Wrench
> BB - Drill

> accounts.dbf
> id char(16) - company char(64)
> BB - APlus Hardware Store
> A4R5 - Wendy's HandMade Hammers
> ABC123 - Tom's Widgets
> T678Y - APlus Hardware Store
> 567XYZ - APlus Hardware Store

> I learn best through example, so if anyone has any offerings on this
> SELECT statement, I'd love to hear them.

SQL is pretty flexible with respect to ORDERing:

    SELECT ;
            Company, ;
            Product, ;
            IIF(Company = cCompany, SPACE(LEN(Company)), Company) AS
xCompany ;
        FROM Product, Accounts ;
        ORDER BY xCompany DESC, Product

Note the DESC modifier which will cause the result set to be sorted first in
DESCending xCompany order, then by Product (in ascending order) within a
common xCompany list.

Modifying Christian Desbourse's approach would be simpler:

    SELECT ;
            Company, ;
            Product, ;
            Company = cCompany AS xCompany ;
        FROM Product, Accounts ;
        ORDER BY xCompany, Product

Quote:
> that comes with VFP 6.0 for optimizing and speeding up code, but is
> there a good website that explains simple ways to improve performance?
> The Microsoft documentation isn't always as clear as I'd like it (or
> need it) to be.  Are they any well written Visual Foxpro 6 books
> available?  Websites?  Are there any good books with information on
> using SQL SELECT statements in VFP 6?

The primo source for VFP books is:

    www.hentzenwerke.com

where "The Hacker's Guide" is probably the all time most recommended volume.
Another source is

    http://www.free-ed.net/

which has a free, on-line course in VFP.

 - Rush



Wed, 19 May 2004 00:30:24 GMT  
 Newbie Visual Foxpro 6.0 SQL Question
On Thu, 29 Nov 2001 03:06:52 +0100, Christian Desbourse

Quote:

>Yes, .F. comes before .T., this is why you need the ! before the
>logical expression.

Gotcha.

Quote:
>> SELECT !(alltrim(lower(id))="Z001") AS lid, Name FROM ... ORDER BY
>> lid, Name

>> Would that work? Or is there a better, faster way?

>I suppose you meant !(ALLTRIM(UPPER(id))="Z001") or
>!(ALLTRIM(LOWER(id))="z001")

You are right, I actually did mean !(ALLTRIM(LOWER(id))="z001").  

Quote:
>This means you want to group all id's starting with Z001 or z001

Correct.

Quote:
>You can also use == instead of = in the expression if you want Z001 but
>not Z001X

Thanks for that tip as I didn't want "Z001X".  You've been very
helpful and I'm made great progress this past week in learning all
sorts of new stuff.  Thanks much!

-D.B.



Tue, 25 May 2004 00:19:10 GMT  
 Newbie Visual Foxpro 6.0 SQL Question


Quote:
>SQL is pretty flexible with respect to ORDERing:

>    SELECT ;
>            Company, ;
>            Product, ;
>            IIF(Company = cCompany, SPACE(LEN(Company)), Company) AS
>xCompany ;
>        FROM Product, Accounts ;
>        ORDER BY xCompany DESC, Product

>Note the DESC modifier which will cause the result set to be sorted first in
>DESCending xCompany order, then by Product (in ascending order) within a
>common xCompany list.

>Modifying Christian Desbourse's approach would be simpler:

>    SELECT ;
>            Company, ;
>            Product, ;
>            Company = cCompany AS xCompany ;
>        FROM Product, Accounts ;
>        ORDER BY xCompany, Product

These both helped me get where I wanted to be - thanks so much.  I
really understand a lot more what it is I'm looking for and how to get
there.

Quote:
>The primo source for VFP books is:

>    www.hentzenwerke.com

>where "The Hacker's Guide" is probably the all time most recommended volume.
>Another source is

>    http://www.free-ed.net/

>which has a free, on-line course in VFP.

I'll be checking both of these sites out, thank you for the info!

Regards,
D.B.



Tue, 25 May 2004 00:24:58 GMT  
 
 [ 10 post ] 

 Relevant Pages 

1. old newbie and sql edit window in VFP 6.0 or SQL editor for VFP 6.0

2. Newbie: Converting from FoxPro 2.6 DOS to Visual FoxPro 6.0

3. visual foxpro 6.0 and SQL Server 7.0 DTS files

4. Visual FoxPro 6.0 + SQL-server 7.0

5. Visual FoxPro 6.0 Client Server To Oracle 7.3 ODBC Question

6. Visual FoxPro 6.0 Client Server To Oracle 7.3 ODBC Question ANSWERED

7. Newbie question on VFP 6.0

8. Fox 6.0 Newbie distribution question

9. VFP 6.0 Newbie Question...

10. visual foxpro and sql server question

11. Visual Foxpro 6.0 vs Visual Foxpro5.0/Foxprow 2.6

12. visual basic interacting with visual foxpro 6.0

 

 
Powered by phpBB® Forum Software