Re: Adding a row to selected detail rows with SQL

What about something like the following???

This is in no way meant to be the exact solution, just an idea of the
structure that I would think you would use.

insert into PREFERENCE_DETAIL (PROVIDER_ID, PROCEDURE_ID, :item_to_add)
select PROVIDER_ID, PROCEDURE_ID, SYSTEM_ID
from PREFERENCE_DETAIL
where SYSTEM_ID = :search_item;


Thanks
Bryce Martin
Programmer/Analyst I
570-546-4777



Charles Wilt <charles.wilt@xxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
12/30/2009 02:22 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
cc

Subject
Re: Adding a row to selected detail rows with SQL






Sorry copy & paste error look at this

select PROVIDER_ID, PROCEDURE_ID, :item_to_add
from PREFERENCE_DETAIL
where RESOURCE_ID = :search_item
UNION ALL
select PROVIDER_ID, PROCEDURE_ID,RESOURCE_ID
from PREFERENCE_DETAIL
order by PROVIDER_ID, PROCEDURE_ID

the first select would return
000355 001201 993366

the second
000355 001201 112344
000355 001201 223355

which are then UNION'd together

Charles


On Wed, Dec 30, 2009 at 1:59 PM, jmmckee <jmmckee@xxxxxxxxxxxxxx> wrote:

Did not men to omit important details. SYSTEM_ID is in

PREFERENCE_DETAIL. If a given SYSTEM_ID exists for any
PROVIDER_ID/PROCEDURE_ID, and (just came to me) the new SYSTEM_ID is not
already associated with a given PROVIDER_ID/PROCEDURE_ID, it would need to
be inserted, with PROVIDER_ID, PROCEDURE_ID

I am unclear on your first SELECT. You have item_to_add listed. But,

it isn't in PREFERENCE_DETAIL yet, as it needs to be added.

I wish these goofy changes were more readily accomplished by the

application software. Simple stuff like replace or global add is easy.
But, selective add is not covered.

John McKee


-----Original message-----
From: Charles Wilt charles.wilt@xxxxxxxxx
Date: Wed, 30 Dec 2009 13:28:13 -0600
To: Midrange Systems Technical Discussion midrange-l@xxxxxxxxxxxx
Subject: Re: Adding a row to selected detail rows with SQL

Here's my quick attempt...note that your specs aren't complete. you
mention SYSTEM_ID, but it isn't shown in either table...

select PROVIDER_ID, PROCEDURE_ID, and :item_to_add
from PREFERENCE_DETAIL
where RESOURCE_ID = :search_item
UNION ALL
select RESOURCE_ID
from PREFERENCE_DETAIL
order by PROVIDER_ID, PROCEDURE_ID

Charles

On Wed, Dec 30, 2009 at 12:12 PM, jmmckee <jmmckee@xxxxxxxxxxxxxx>

wrote:

Given a detail table, PREFERENCE_DETAIL, that has columns headed

PROVIDER_ID, PROCEDURE_ID, RESOURCE_TYPE, and RESOURCE_ID.

Given a second table, PREFERENCE_HEADER, with columns PROVIDER_ID,

PROCEDURE_ID (among others):

Can an item be selectively added to the PREFERENCE_DETAIL table when

a given SYSTEM_ID exists for a particular PROVIDER_ID, PROCEDURE_ID pair?
No information is available on which PROVIDER_ID/PROCEDURE_ID pair has
the first item. I am told that approximately 700 PROVIDER_ID/PROCEDURE_ID
pairs are involved.

Is this too muddy?

PREFERENCE_HEADER
PROVIDER_ID
PROCEDURE_ID

PREFERENCE_DETAIL
PROVIDER_ID
PROCEDURE_ID
RESOURCE_TYPE
RESOURCE_ID


PREFERENCE_HEADER

000355 001200
000355 001201
000355 001202
000378 009988


PREFERENCE_DETAIL

000355 001201 112344
000355 001201 223355


If, for example the search item was 112344, and the item to add was

993366, PREFERENCE_DETAIl needs to look like this:

000355 001201 112344
000355 001201 223355
000355 001201 993366


I appreciate any assistance.


John McKee

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)

mailing list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.