In a medical office, one provider is planning to separate from the
group, and wants her data.  I wonder if there is an SQL way of doing
this elegantly, or if I should just iterate through the ttables.

The patient table is the master table, and has a provider field

The Payor table is linked to the master by the ACCTNUMBER field; there
are zero to 3 payors.

The TCODE table is linked to the master by the ACCT field; there are
zero to many detail records; the tcode table also has a provider field

The TCharges, TPayment, TAuth, TDx, and TMisch tables are linked to the
TCode table by ACCT and a LINK field, but since they do have an ACCT
field, they can be accessed via that field.  (Each TCode record has a
link to one of these tables.)

Is there an SQL way to do this?

TIA,
Jerry

"Jerry Blumenthal" <jerry@blumenthalsoftware.com> wrote in message
news:FCE8EBCD-FA33-451A-8C37-22C2405AD034@news.elevatesoft.com...
> In a medical office, one provider is planning to separate from the group,
> and wants her data.  I wonder if there is an SQL way of doing this
> elegantly, or if I should just iterate through the ttables.
>

If you can do a SELECT of the records you want, you can also pull them into
a new table. Say we create a newaddress table, and we want to pull some
records from address to newaddress

INSERT INTO NEWADDRESS
SELECT ADDRESS.* FROM CUSTTABLE
JOIN ADDRESS ON ADDRESS.ADDR_CUST = CUSTTABLE.ID
WHERE CUSTTABLE.ID IN (2,3,4);
DELETE FROM ADDRESS WHERE ADDR_CUST IN SELECT ID FROM CUSTTABLE WHERE ID IN
(2,3,4);

Of course, the JOIN with custtable is not really required in the above
script, I just put it in to show you how you could join other tables in case
they were needed for the WHERE clause.

You can create newaddress by using dbsys reverse engineering on address,
changing the table name, and putting the create table on top of the previous
script. Do the same for the other tables.

Robert

Robert wrote:
> "Jerry Blumenthal" <jerry@blumenthalsoftware.com> wrote in message
> news:FCE8EBCD-FA33-451A-8C37-22C2405AD034@news.elevatesoft.com...
>> In a medical office, one provider is planning to separate from the group,
>> and wants her data.  I wonder if there is an SQL way of doing this
>> elegantly, or if I should just iterate through the ttables.
>>
>
> If you can do a SELECT of the records you want, you can also pull them into
> a new table. Say we create a newaddress table, and we want to pull some
> records from address to newaddress
>
> INSERT INTO NEWADDRESS
> SELECT ADDRESS.* FROM CUSTTABLE
> JOIN ADDRESS ON ADDRESS.ADDR_CUST = CUSTTABLE.ID
> WHERE CUSTTABLE.ID IN (2,3,4);
> DELETE FROM ADDRESS WHERE ADDR_CUST IN SELECT ID FROM CUSTTABLE WHERE ID IN
> (2,3,4);
>
> Of course, the JOIN with custtable is not really required in the above
> script, I just put it in to show you how you could join other tables in case
> they were needed for the WHERE clause.
>
> You can create newaddress by using dbsys reverse engineering on address,
> changing the table name, and putting the create table on top of the previous
> script. Do the same for the other tables.
>
> Robert

Robert-

Thanks.  I think I see the logic.  However, I'm not sufficiently
confident of myself in sql to do all that.  Tell me what you think about
this:

1. Copy the source file to the target folder and then empty it there.
2. INSERT INTO NEWADDRESS
   SELECT * FROM OLDADDRESS
   WHERE OLDADDRESS.ID IN (2);

THAT should get all of the master records for person #2 into the target
table.

3. Iterate through the target table to get the account ID#, and each
time I get one, do
   INSERT INTO NEWTcode
   SELECT  * FROM OLDTcode
   WHERE OLDTCODE.ID =2

and then do something similar for the other T tables (tCharges,
tPayments, etc...)

Would that work ok do you think?
I'd just as soon not have to deal with joins and stuff like that that I
am not familiar with.

Jerry

"Jerry Blumenthal" <jerry@blumenthalsoftware.com> wrote in message
news:E1B5D4D4-1267-4F8C-9266-CA5BA35B40E8@news.elevatesoft.com...
> Robert wrote:
>> "Jerry Blumenthal" <jerry@blumenthalsoftware.com> wrote in message
>> news:FCE8EBCD-FA33-451A-8C37-22C2405AD034@news.elevatesoft.com...
>>> In a medical office, one provider is planning to separate from the
>>> group, and wants her data.  I wonder if there is an SQL way of doing
>>> this elegantly, or if I should just iterate through the ttables.
>>>
>>
>> If you can do a SELECT of the records you want, you can also pull them
>> into a new table. Say we create a newaddress table, and we want to pull
>> some records from address to newaddress
>>
>> INSERT INTO NEWADDRESS
>> SELECT ADDRESS.* FROM CUSTTABLE
>> JOIN ADDRESS ON ADDRESS.ADDR_CUST = CUSTTABLE.ID
>> WHERE CUSTTABLE.ID IN (2,3,4);
>> DELETE FROM ADDRESS WHERE ADDR_CUST IN SELECT ID FROM CUSTTABLE WHERE ID
>> IN (2,3,4);
>>
>> Of course, the JOIN with custtable is not really required in the above
>> script, I just put it in to show you how you could join other tables in
>> case they were needed for the WHERE clause.
>>
>> You can create newaddress by using dbsys reverse engineering on address,
>> changing the table name, and putting the create table on top of the
>> previous script. Do the same for the other tables.
>>
>> Robert
>
> Robert-
>
> Thanks.  I think I see the logic.  However, I'm not sufficiently confident
> of myself in sql to do all that.  Tell me what you think about this:
>
> 1. Copy the source file to the target folder and then empty it there.

So far, so good.

> 2. INSERT INTO NEWADDRESS
>    SELECT * FROM OLDADDRESS
>    WHERE OLDADDRESS.ID IN (2);
>
> THAT should get all of the master records for person #2 into the target
> table.
>

OK. The key thing here is that you can run the SELECT portion by itself, see
if you actually are selecting the records you want, refine the SELECT
portion as required, and only then add the INSERT code to your script. As
the selects get more complex, this comes in handy. And you can do it all in
DBSYS, without even starting the compiler.

> I'd just as soon not have to deal with joins and stuff like that that I am
> not familiar with.
>

LOL. Are you the guy who wrote "I wonder if there is an SQL way of doing
this elegantly, or if I should just iterate through the ttables."? How are
you going to do anything other than the most basic things in SQL if you
don't use JOIN? Especially if you want to code "elegant" SQL, by Jove!

A JOIN is basically the same thing you are doing when you set a
master/detail relationship on your tables. You

SELECT FIELDS FROM MASTER
JOIN DETAIL ON MASTER.ID = DETAIL.POINTERTOMASTER
WHERE

In the above, "FIELDS" can be both master or detail fields, or a combination
of both. If you want all the fields for just the details, do a select
details.*. You will get a record for every DETAIL, as long as the WHERE
clause (which can also apply to either  master or detail or both) is
satisfied.

Trust me, practice a bit, you'll never go back to "iterating thru tables".

Robert

Jerry Blumenthal <jerry@blumenthalsoftware.com> wrote on Mon, 25 Sep 2006 13:11:13 -0400

>1. Copy the source file to the target folder and then empty it there.
>2. INSERT INTO NEWADDRESS
> SELECT * FROM OLDADDRESS
> WHERE OLDADDRESS.ID IN (2);
>
>THAT should get all of the master records for person #2 into the target
>table.
>
>3. Iterate through the target table to get the account ID#, and each
>time I get one, do
> INSERT INTO NEWTcode
> SELECT * FROM OLDTcode
> WHERE OLDTCODE.ID =2
>
>and then do something similar for the other T tables (tCharges,
>tPayments, etc...)
>
>Would that work ok do you think?
>I'd just as soon not have to deal with joins and stuff like that that I
>am not familiar with.
>
>Jerry
>
Jerry


if you are going to copy the source file to the target file - why not simply delete the unwanted records instead of emptying it and reinserting?

DELETE FROM NEWADDRESS WHERE ID <> 2;

Either way you'll want to optimise the file to suck out the empty space.

Cheers


Jeff
--
Jeff Cook
Aspect Systems Ltd
Phone: +64-9-424 5388
Skype: jeffcooknz
www.aspect.co.nz

Robert wrote:
> "Jerry Blumenthal" <jerry@blumenthalsoftware.com> wrote in message
> news:E1B5D4D4-1267-4F8C-9266-CA5BA35B40E8@news.elevatesoft.com...
>> Robert wrote:
>>> "Jerry Blumenthal" <jerry@blumenthalsoftware.com> wrote in message
>>> news:FCE8EBCD-FA33-451A-8C37-22C2405AD034@news.elevatesoft.com...
>>>> In a medical office, one provider is planning to separate from the
>>>> group, and wants her data.  I wonder if there is an SQL way of doing
>>>> this elegantly, or if I should just iterate through the ttables.
>>>>
>>> If you can do a SELECT of the records you want, you can also pull them
>>> into a new table. Say we create a newaddress table, and we want to pull
>>> some records from address to newaddress
>>>
>>> INSERT INTO NEWADDRESS
>>> SELECT ADDRESS.* FROM CUSTTABLE
>>> JOIN ADDRESS ON ADDRESS.ADDR_CUST = CUSTTABLE.ID
>>> WHERE CUSTTABLE.ID IN (2,3,4);
>>> DELETE FROM ADDRESS WHERE ADDR_CUST IN SELECT ID FROM CUSTTABLE WHERE ID
>>> IN (2,3,4);
>>>
>>> Of course, the JOIN with custtable is not really required in the above
>>> script, I just put it in to show you how you could join other tables in
>>> case they were needed for the WHERE clause.
>>>
>>> You can create newaddress by using dbsys reverse engineering on address,
>>> changing the table name, and putting the create table on top of the
>>> previous script. Do the same for the other tables.
>>>
>>> Robert
>> Robert-
>>
>> Thanks.  I think I see the logic.  However, I'm not sufficiently confident
>> of myself in sql to do all that.  Tell me what you think about this:
>>
>> 1. Copy the source file to the target folder and then empty it there.
>
> So far, so good.
>
>> 2. INSERT INTO NEWADDRESS
>>    SELECT * FROM OLDADDRESS
>>    WHERE OLDADDRESS.ID IN (2);
>>
>> THAT should get all of the master records for person #2 into the target
>> table.
>>
>
> OK. The key thing here is that you can run the SELECT portion by itself, see
> if you actually are selecting the records you want, refine the SELECT
> portion as required, and only then add the INSERT code to your script. As
> the selects get more complex, this comes in handy. And you can do it all in
> DBSYS, without even starting the compiler.
>
>> I'd just as soon not have to deal with joins and stuff like that that I am
>> not familiar with.
>>
>
> LOL. Are you the guy who wrote "I wonder if there is an SQL way of doing
> this elegantly, or if I should just iterate through the ttables."? How are
> you going to do anything other than the most basic things in SQL if you
> don't use JOIN? Especially if you want to code "elegant" SQL, by Jove!
>
> A JOIN is basically the same thing you are doing when you set a
> master/detail relationship on your tables. You
>
> SELECT FIELDS FROM MASTER
> JOIN DETAIL ON MASTER.ID = DETAIL.POINTERTOMASTER
> WHERE
>
> In the above, "FIELDS" can be both master or detail fields, or a combination
> of both. If you want all the fields for just the details, do a select
> details.*. You will get a record for every DETAIL, as long as the WHERE
> clause (which can also apply to either  master or detail or both) is
> satisfied.
>
> Trust me, practice a bit, you'll never go back to "iterating thru tables".
>
> Robert
>
>

c'mon, Robert, be gentle with me, it's my first time
Jerry

"Jerry Blumenthal" <jerry@blumenthalsoftware.com> wrote in message
news:1BE60554-5CAC-47FF-ACD3-CE043B335FD3@news.elevatesoft.com...
>
> c'mon, Robert, be gentle with me, it's my first time
> Jerry

You'll love this SQL thing. Here, have another glass of Cabernet.

R

Robert wrote:
> "Jerry Blumenthal" <jerry@blumenthalsoftware.com> wrote in message
> news:1BE60554-5CAC-47FF-ACD3-CE043B335FD3@news.elevatesoft.com...
>> c'mon, Robert, be gentle with me, it's my first time
>> Jerry
>
> You'll love this SQL thing. Here, have another glass of Cabernet.
>
> R
>
>

I'm actually drinking some PInot Noir.
Jerry

Jerry,

Be sure you understand the situation before you delete any "obsolete" records from the
patient database.  Suppose patient X has Doctor A as her PCP (primary care provider).  One
day she asks for an appointment, and the receptionist tells her "Doctor A is on vacation,
do you want to see Doctor B instead?"  X says yes, that's fine.  Now Doctor B has billing
records linked to patient X, even though B isn't X's "provider".  Doctor A is leaving the
practice.  If you delete X from the database (because she "is A's patient"), you'll orphan
some records that B needs to keep.

Jerry,

>In a medical office

Are you designing a medical application using DBISAM? If yes could you provide
more feedback?

Manfredt Kavetu