Another query design question...

Hi,

Using VFP 9.0 to remotely query a SQL database and I'm stuck once again...
yeah I know, what else is new? :-)

This time I'm trying to make a sort of lookup table(view) from two related
tables:

Table name: billing_code
Fields:
sys_id (pk)
billing_code_set_ver_sys_id
billing_code
description
create_timestamp
modify_timestamp
create_user
modify_user

Table name: billing_code_charge_code
Fields:
sys_id (pk)
billing_code_sys_id
chg_codes_code
create_timestamp
modify_timestamp
create_user
modify_user

These tables are related on the billing_code.sys_id to billing_code_sys_id,
for every billing_code record there's many billing_code_charge_code records.
What I want is a table that returns chg_codes_code, billing_code and
description. Sounds simple doesn't it? Here's the catch...

The billing_code table is on the many side of a one to many relationship
with another table, billing_code_set_version. This means that there are
multiple records in billing_code with the same billing_code and description.
So far I haven't figured out how to get my query with just one record for
every chg_codes_code, billing_code and billing_code.description. The
closest I've gotten is this:

select distinct chg_codes_code, billing_code, description
from billing_code_charge_code
right join billing_code on billing_code.sys_id =
billing_code_charge_code.billing_code_sys_id
where left(chg_codes_code, 1) in ('D', 'F', 'E')
order by chg_codes_code

In many cases this returns duplicate chg_codes_code values. The main
difference I see in the records is that for one the description is in all
upper case and the description for the other record uses normal case.

Any suggestions? I've been trying all sorts of combinations but so far no
luck.

Thanks in advance,
Linn

Stephen
Fri Mar 24 11:25:30 CST 2006

Distinct is identifying, correctly, that the records are not the same
because of the upper/proper difference.

What would happen if you included

GROUP by chg_codes_code

Sincerely

Stephen

"Linn Kubler" <lkubler@chartwellwisc2.com> wrote in message
news:O8mFUW2TGHA.5172@TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Using VFP 9.0 to remotely query a SQL database and I'm stuck once again...
> yeah I know, what else is new? :-)
>
> This time I'm trying to make a sort of lookup table(view) from two related
> tables:
>
> Table name: billing_code
> Fields:
> sys_id (pk)
> billing_code_set_ver_sys_id
> billing_code
> description
> create_timestamp
> modify_timestamp
> create_user
> modify_user
>
> Table name: billing_code_charge_code
> Fields:
> sys_id (pk)
> billing_code_sys_id
> chg_codes_code
> create_timestamp
> modify_timestamp
> create_user
> modify_user
>
> These tables are related on the billing_code.sys_id to
> billing_code_sys_id, for every billing_code record there's many
> billing_code_charge_code records. What I want is a table that returns
> chg_codes_code, billing_code and description. Sounds simple doesn't it?
> Here's the catch...
>
> The billing_code table is on the many side of a one to many relationship
> with another table, billing_code_set_version. This means that there are
> multiple records in billing_code with the same billing_code and
> description. So far I haven't figured out how to get my query with just
> one record for every chg_codes_code, billing_code and
> billing_code.description. The closest I've gotten is this:
>
> select distinct chg_codes_code, billing_code, description
> from billing_code_charge_code
> right join billing_code on billing_code.sys_id =
> billing_code_charge_code.billing_code_sys_id
> where left(chg_codes_code, 1) in ('D', 'F', 'E')
> order by chg_codes_code
>
> In many cases this returns duplicate chg_codes_code values. The main
> difference I see in the records is that for one the description is in all
> upper case and the description for the other record uses normal case.
>
> Any suggestions? I've been trying all sorts of combinations but so far no
> luck.
>
> Thanks in advance,
> Linn
>
>

Stephen
Fri Mar 24 11:27:19 CST 2006

Sorry - just noticed it's vfp9 and the GROUP BY has changed.

Stephen

"Linn Kubler" <lkubler@chartwellwisc2.com> wrote in message
news:O8mFUW2TGHA.5172@TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Using VFP 9.0 to remotely query a SQL database and I'm stuck once again...
> yeah I know, what else is new? :-)
>
> This time I'm trying to make a sort of lookup table(view) from two related
> tables:
>
> Table name: billing_code
> Fields:
> sys_id (pk)
> billing_code_set_ver_sys_id
> billing_code
> description
> create_timestamp
> modify_timestamp
> create_user
> modify_user
>
> Table name: billing_code_charge_code
> Fields:
> sys_id (pk)
> billing_code_sys_id
> chg_codes_code
> create_timestamp
> modify_timestamp
> create_user
> modify_user
>
> These tables are related on the billing_code.sys_id to
> billing_code_sys_id, for every billing_code record there's many
> billing_code_charge_code records. What I want is a table that returns
> chg_codes_code, billing_code and description. Sounds simple doesn't it?
> Here's the catch...
>
> The billing_code table is on the many side of a one to many relationship
> with another table, billing_code_set_version. This means that there are
> multiple records in billing_code with the same billing_code and
> description. So far I haven't figured out how to get my query with just
> one record for every chg_codes_code, billing_code and
> billing_code.description. The closest I've gotten is this:
>
> select distinct chg_codes_code, billing_code, description
> from billing_code_charge_code
> right join billing_code on billing_code.sys_id =
> billing_code_charge_code.billing_code_sys_id
> where left(chg_codes_code, 1) in ('D', 'F', 'E')
> order by chg_codes_code
>
> In many cases this returns duplicate chg_codes_code values. The main
> difference I see in the records is that for one the description is in all
> upper case and the description for the other record uses normal case.
>
> Any suggestions? I've been trying all sorts of combinations but so far no
> luck.
>
> Thanks in advance,
> Linn
>
>

Linn
Fri Mar 24 12:11:34 CST 2006

Thanks for the suggestion Stephen but it didn't change the results. It
required me to put chg_codes_code, billing_code and description in the group
by clause. This didn't change the result set at all.

Thanks though,
Linn

"Stephen Ibbs" <stephen@ibbs.org.uk> wrote in message
news:O0ZI0f2TGHA.2244@TK2MSFTNGP14.phx.gbl...
> Distinct is identifying, correctly, that the records are not the same
> because of the upper/proper difference.
>
> What would happen if you included
>
> GROUP by chg_codes_code
>
> Sincerely
>
> Stephen
>
> "Linn Kubler" <lkubler@chartwellwisc2.com> wrote in message
> news:O8mFUW2TGHA.5172@TK2MSFTNGP12.phx.gbl...
>> Hi,
>>
>> Using VFP 9.0 to remotely query a SQL database and I'm stuck once
>> again... yeah I know, what else is new? :-)
>>
>> This time I'm trying to make a sort of lookup table(view) from two
>> related tables:
>>
>> Table name: billing_code
>> Fields:
>> sys_id (pk)
>> billing_code_set_ver_sys_id
>> billing_code
>> description
>> create_timestamp
>> modify_timestamp
>> create_user
>> modify_user
>>
>> Table name: billing_code_charge_code
>> Fields:
>> sys_id (pk)
>> billing_code_sys_id
>> chg_codes_code
>> create_timestamp
>> modify_timestamp
>> create_user
>> modify_user
>>
>> These tables are related on the billing_code.sys_id to
>> billing_code_sys_id, for every billing_code record there's many
>> billing_code_charge_code records. What I want is a table that returns
>> chg_codes_code, billing_code and description. Sounds simple doesn't it?
>> Here's the catch...
>>
>> The billing_code table is on the many side of a one to many relationship
>> with another table, billing_code_set_version. This means that there are
>> multiple records in billing_code with the same billing_code and
>> description. So far I haven't figured out how to get my query with just
>> one record for every chg_codes_code, billing_code and
>> billing_code.description. The closest I've gotten is this:
>>
>> select distinct chg_codes_code, billing_code, description
>> from billing_code_charge_code
>> right join billing_code on billing_code.sys_id =
>> billing_code_charge_code.billing_code_sys_id
>> where left(chg_codes_code, 1) in ('D', 'F', 'E')
>> order by chg_codes_code
>>
>> In many cases this returns duplicate chg_codes_code values. The main
>> difference I see in the records is that for one the description is in all
>> upper case and the description for the other record uses normal case.
>>
>> Any suggestions? I've been trying all sorts of combinations but so far
>> no luck.
>>
>> Thanks in advance,
>> Linn
>>
>>
>
>

Bernhard
Fri Mar 24 13:20:28 CST 2006

Hi Linn

> select distinct chg_codes_code, billing_code, description
> from billing_code_charge_code
> right join billing_code on billing_code.sys_id =
> billing_code_charge_code.billing_code_sys_id
> where left(chg_codes_code, 1) in ('D', 'F', 'E')
> order by chg_codes_code
>
> In many cases this returns duplicate chg_codes_code values. The main
> difference I see in the records is that for one the description is in all
> upper case and the description for the other record uses normal case.
If the difference is only upper/lower case, so use: upper(chg_codes_code) in
your SELECT command.

May be it's a good idea to use consistent writing of all chg_codes_code
throughout your database.

Regards
Bernhard Sander

Cindy
Sat Mar 25 20:13:32 CST 2006

Hi Linn,

Have you run a local query on the result set like:
Select ;
Upper(chg_codes_code), ;
Upper(billing_code, description) ;
Count(*) ;
From ;
QueryResult ;
Group ;
By 1, 2, ;
Having ;
Count(*) > 1

This will eliminate the upper/lower case problem. Are there any other
duplicates at all? If there are none then your issue is finding a way to
make text in different cases group the same. To do this you can use SQL
Server's UPPER() function.

If there are any duplicates that are not eliminated by the above query then
please post back describing them.

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn.com www.cindywinegarden.com


"Linn Kubler" <lkubler@chartwellwisc2.com> wrote in message
news:O8mFUW2TGHA.5172@TK2MSFTNGP12.phx.gbl...

> Table name: billing_code
> Fields:
> sys_id (pk)
> billing_code_set_ver_sys_id
> billing_code
> description
> create_timestamp
> modify_timestamp
> create_user
> modify_user
>
> Table name: billing_code_charge_code
> Fields:
> sys_id (pk)
> billing_code_sys_id
> chg_codes_code
> create_timestamp
> modify_timestamp
> create_user
> modify_user

> The billing_code table is on the many side of a one to many relationship
> with another table, billing_code_set_version. This means that there are
> multiple records in billing_code with the same billing_code and
> description. So far I haven't figured out how to get my query with just
> one record for every chg_codes_code, billing_code and
> billing_code.description. The closest I've gotten is this:
>
> select distinct chg_codes_code, billing_code, description
> from billing_code_charge_code
> right join billing_code on billing_code.sys_id =
> billing_code_charge_code.billing_code_sys_id
> where left(chg_codes_code, 1) in ('D', 'F', 'E')
> order by chg_codes_code
>
> In many cases this returns duplicate chg_codes_code values. The main
> difference I see in the records is that for one the description is in all
> upper case and the description for the other record uses normal case.

Linn
Tue Mar 28 08:06:41 CST 2006

Cindy,

Thanks for the tip. This code returned all the duplicate rows of data,
which is very helpful. It then occured to me that maybe I could reduce the
number of dups by putting the UPPER() function on the description in my view
query. Then I realized that there are subtle variences in how words are
spelled and use of spaces in the description field so I don't know how I'd
isolate them all.

I did figure out that if I drop the description field from my view I do get
a list with no duplicates and this is all I really need for my query. So
I'm just going to dissregard that field and I should be good to go.

But I've been trying to figure out an easy way to determine duplicates in
tables and this code looks to be the ticket. Very handy indeed.

Thanks much,
Linn

"Cindy Winegarden" <cindy_winegarden@msn.com> wrote in message
news:O6fjHuHUGHA.4960@TK2MSFTNGP12.phx.gbl...
> Hi Linn,
>
> Have you run a local query on the result set like:
> Select ;
> Upper(chg_codes_code), ;
> Upper(billing_code, description) ;
> Count(*) ;
> From ;
> QueryResult ;
> Group ;
> By 1, 2, ;
> Having ;
> Count(*) > 1
>
> This will eliminate the upper/lower case problem. Are there any other
> duplicates at all? If there are none then your issue is finding a way to
> make text in different cases group the same. To do this you can use SQL
> Server's UPPER() function.
>
> If there are any duplicates that are not eliminated by the above query
> then please post back describing them.
>
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy_winegarden@msn.com www.cindywinegarden.com
>
>
> "Linn Kubler" <lkubler@chartwellwisc2.com> wrote in message
> news:O8mFUW2TGHA.5172@TK2MSFTNGP12.phx.gbl...
>
>> Table name: billing_code
>> Fields:
>> sys_id (pk)
>> billing_code_set_ver_sys_id
>> billing_code
>> description
>> create_timestamp
>> modify_timestamp
>> create_user
>> modify_user
>>
>> Table name: billing_code_charge_code
>> Fields:
>> sys_id (pk)
>> billing_code_sys_id
>> chg_codes_code
>> create_timestamp
>> modify_timestamp
>> create_user
>> modify_user
>
>> The billing_code table is on the many side of a one to many relationship
>> with another table, billing_code_set_version. This means that there are
>> multiple records in billing_code with the same billing_code and
>> description. So far I haven't figured out how to get my query with just
>> one record for every chg_codes_code, billing_code and
>> billing_code.description. The closest I've gotten is this:
>>
>> select distinct chg_codes_code, billing_code, description
>> from billing_code_charge_code
>> right join billing_code on billing_code.sys_id =
>> billing_code_charge_code.billing_code_sys_id
>> where left(chg_codes_code, 1) in ('D', 'F', 'E')
>> order by chg_codes_code
>>
>> In many cases this returns duplicate chg_codes_code values. The main
>> difference I see in the records is that for one the description is in all
>> upper case and the description for the other record uses normal case.
>
>

Cindy
Tue Mar 28 12:08:56 CST 2006

Hi Linn,

You might try the Soundex() function to find descriptions that are similar.
Soundex is a great way to find things that are phonetically similar.

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn.com www.cindywinegarden.com


"Linn Kubler" <lkubler@chartwellwisc2.com> wrote in message
news:un1hWDnUGHA.2276@tk2msftngp13.phx.gbl...
> .... there are subtle variences in how words are spelled and use of spaces
> in the description field ...