|
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.