Re: Adding a row to selected detail rows with SQL

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.