A Perplexing Query 
Author Message
 A Perplexing Query


Newsgroups: comp.databases.paradox
Subject: Post #863
Date: Sun, 16 Jan 1994 18:55:41 GMT

I submitted this question before, but only got one response.

What query would I use to get a count of everyone with "hoco" for the first
four letters vs everyone else (unique occurrences of other zip codes)?

eg,

=======
 Table
=======

Name    Address
====    =======
John    hoco21044
Betty   21225
Annie   21725
Kelly   21225
Jean    hoco 21046
Alain   hoco21046

================
 Desired Result
================

Address         Count
=======         =====
hoco            3
21225           2
21725           1
============================================================================

In other words, "hoco.." is unique and every other distinct zip code is unique.



Sat, 06 Jul 1996 04:16:37 GMT
 A Perplexing Query

Your query appears to ask for a function like SUBSTR(). But since such
a function is not available in the QBE, and even if it did, it would
have been a very slow one. You may wanna construct your query based on
ASCII value comparison. Following is an example. It is based on the
assumption that _only_ the "hoco..." postal codes have non-digits in
them. And also that the field type is "A" (quite obvious from the
presence of "hoco...").

Query

    ...| ZIP CODE                   |
       | CHECK <"h",calc count all  |
       | CHECK >="h",calc count all |

Endquery

hope it helps...

--nasir



Sat, 06 Jul 1996 21:12:17 GMT
 A Perplexing Query

: I submitted this question before, but only got one response.

: What query would I use to get a count of everyone with "hoco" for the first
: four letters vs everyone else (unique occurrences of other zip codes)?

: eg,

: =======
:  Table
: =======

: Name  Address
: ====  =======
: John  hoco21044
: Betty 21225
: Annie 21725
: Kelly 21225
: Jean  hoco 21046
: Alain hoco21046

: ================
:  Desired Result
: ================

: Address               Count
: =======               =====
: hoco          3
: 21225         2
: 21725         1
: ============================================================================

: In other words, "hoco.." is unique and every other distinct zip code is
: unique.

I do not have my PC with me, so I cannot verify this solution, but here is
a stab at a solution.  It requires two queries:

Table.DB | Name | Address                                      |
         |      | Check hoco.., calc hoco as Addr2             |
         |      | Check _EG01, not hoco.., calc _EG01 as Addr2 |

------------------------------------------------------------------------

:PRIV:Answer.db | Address                 | Addr2 |
                | calc count all as Count | Check |

The first query consolidates all addresses that begin with hoco.  The
second query counts unique addresses.

Dan Milliron



Sun, 07 Jul 1996 01:44:03 GMT
 A Perplexing Query
I don't see an obvious way to do this with a single query, but it
is easy to do with two queries and a merge:

        Address           | Name
        check, not hoco.. | calc count

followed by:

        Address                          | Name
        hoco.., calc "hoco.." as Address | calc count

I would be curious to know if anyone can do this in a single query.

|>

|> Newsgroups: comp.databases.paradox
|> Subject: Post #863
|> Date: Sun, 16 Jan 1994 18:55:41 GMT
|>
|> I submitted this question before, but only got one response.
|>
|> What query would I use to get a count of everyone with "hoco" for the first
|> four letters vs everyone else (unique occurrences of other zip codes)?
|>
|> eg,
|>
|> =======
|>  Table
|> =======
|>
|> Name      Address
|> ====      =======
|> John      hoco21044
|> Betty     21225
|> Annie     21725
|> Kelly     21225
|> Jean      hoco 21046
|> Alain     hoco21046
|>
|>
|>
|> ================
|>  Desired Result
|> ================
|>
|> Address           Count
|> =======           =====
|> hoco              3
|> 21225             2
|> 21725             1
|> ============================================================================
|>
|> In other words, "hoco.." is unique and every other distinct zip code is unique.

--
----------------------------------------------------------



Sun, 07 Jul 1996 05:03:34 GMT
 A Perplexing Query

Thanks to Dan Milliron for a successful solution to the following problem:

==========================================================================
==========================================================================

Quote:
> : What query would I use to get a count of everyone with "hoco" for the first
> : four letters vs everyone else (unique occurrences of other zip codes)?

> : eg,

> : =======
> :  Table
> : =======

> : Name     Address
> : ====     =======
> : John     hoco21044
> : Betty    21225
> : Annie    21725
> : Kelly    21225
> : Jean     hoco 21046
> : Alain    hoco21046

> : ================
> :  Desired Result
> : ================

> : Address          Count
> : =======          =====
> : hoco             3
> : 21225            2
> : 21725            1

> : In other words, "hoco.." is unique and every other distinct zip code is
> : unique.

> : ============================================================================
> : ============================================================================


> I do not have my PC with me, so I cannot verify this solution, but here is
> a stab at a solution.  It requires two queries:

> Table.DB | Name | Address                                      |
>          |      | Check hoco.., calc hoco as Addr2             |
>          |      | Check _EG01, not hoco.., calc _EG01 as Addr2 |

> ------------------------------------------------------------------------

> :PRIV:Answer.db | Address                 | Addr2 |
>                 | calc count all as Count | Check |

> The first query consolidates all addresses that begin with hoco.  The
> second query counts unique addresses.

==========================================================================
==========================================================================

It did the trick, but the solution required a check plus instead of a check
for the queries to Table.DB.

Someone else wanted to see if desired table could be produced with a single
query, so would I.



Mon, 08 Jul 1996 04:03:42 GMT
 A Perplexing Query

Quote:

>What query would I use to get a count of everyone with "hoco" for the first
>four letters vs everyone else (unique occurrences of other zip codes)?

>eg,

>=======
> Table
>=======

>Name        Address
>====        =======
>John        hoco21044
>Betty       21225
>Annie       21725
>Kelly       21225
>Jean        hoco 21046
>Alain       hoco21046

>================
> Desired Result
>================

>Address             Count
>=======             =====
>hoco                3
>21225               2
>21725               1
>In other words, "hoco.." is unique and every other distinct zip code is unique.

My first thought is that you won't be able to do a query to get the answer
you want.  If it were me I would use a SCAN ... ENDSCAN loop to count all
the necessary records.  It would look something like:

;------------------------------------;
; NOTE: I have not tested this code! ;
;------------------------------------;
dynarray Ct []     ; To hold the count of each address type
Ct ["HOCO"] =  0   ; Initialize the count of the "hoco.." records
                   ; Other indexes for CT, such as "21225" will
                   ; be created during the scan loop
view "table"       ; The table you want to query
moveto [address]   ; Address is the field name
scan
  zipString = []   ; Assign variable for contents of Address field
  if upper (substr (zipString, 1, 4)) = "HOCO"
   then Ct ["HOCO"] = Ct ["HOCO"] + 1
   else
     if isassigned (Ct [zipString])  ; Have we already seen this zip code?
       then Ct [zipString] = Ct [zipString] + 1
       else Ct [zipString] = 1
     endif
  endif
endscan

; At this point the Ct array would contain the answer you sought.
; You could copy this info to a table if that's the format you needed.



Tue, 09 Jul 1996 00:40:47 GMT
 A Perplexing Query

: My first thought is that you won't be able to do a query to get the answer
: you want.  If it were me I would use a SCAN ... ENDSCAN loop to count all
: the necessary records.  It would look something like:

: ;------------------------------------;
: ; NOTE: I have not tested this code! ;
: ;------------------------------------;
: dynarray Ct []     ; To hold the count of each address type
: Ct ["HOCO"] =  0   ; Initialize the count of the "hoco.." records
:                    ; Other indexes for CT, such as "21225" will
:                    ; be created during the scan loop
: view "table"       ; The table you want to query
: moveto [address]   ; Address is the field name
: scan
:   zipString = []   ; Assign variable for contents of Address field
:   if upper (substr (zipString, 1, 4)) = "HOCO"
:    then Ct ["HOCO"] = Ct ["HOCO"] + 1
:    else
:      if isassigned (Ct [zipString])  ; Have we already seen this zip code?
:        then Ct [zipString] = Ct [zipString] + 1
:        else Ct [zipString] = 1
:      endif
:   endif
: endscan

: ; At this point the Ct array would contain the answer you sought.
: ; You could copy this info to a table if that's the format you needed.

This code is okay except for the use of isassigned() to determine if the
dynamic array already contains an index.  Instead, use the contains()
method.

Dan Milliron



Wed, 10 Jul 1996 01:15:40 GMT
 A Perplexing Query

Quote:

>This code is okay except for the use of isassigned() to determine if the
>dynamic array already contains an index.  Instead, use the contains()
>method.

I attempted to send mail, but Dan's From: line is munged.

Anyway, this advice is incorrect for PdoxDOS; Dan seems to be under the
impression that everything is PdoxWin.
--

Hugs and backrubs -- I break Rule 6
{*filter*} vanilla {*filter*} het

Member of the Groucho Marx Fan Club



Sat, 13 Jul 1996 02:01:17 GMT
 A Perplexing Query


: >
: >This code is okay except for the use of isassigned() to determine if the
: >dynamic array already contains an index.  Instead, use the contains()
: >method.

: I attempted to send mail, but Dan's From: line is munged.

: Anyway, this advice is incorrect for PdoxDOS; Dan seems to be under the
: impression that everything is PdoxWin.
: --

My mistake.  I apologize for my narrow view of Paradox.  I reviewed the
original posting and see that the code was obviously for the DOS version.
I am in favor of separating this news group into DOS and Windows branches
so that these oversights can be more easily avoided.

Dan Milliron



Sun, 14 Jul 1996 01:05:46 GMT
 
 [ 9 post ] 

 Relevant Pages 

1. Perplexing Query

2. Perplexing Query Performance

3. A perplexing QUERY

4. SQL Query Question - Perplexed Me for Days

5. Perplexing SQL Query

6. FPW26 Query Problem - I'm perplexed

7. A perplexing SQL Server problem

8. perplexing SQL authentication from ASP.net issue

9. this very perplexing problem.

10. this very perplexing problem

11. Perplexed with NT and SQL Server security

12. Perplexing Problem!


 
Powered by phpBB® Forum Software