|
Try this:
insert into PREFERENCE_DETAIL (PROVIDER_ID, PROCEDURE_ID, REFERENCE_ID)
select distinct PROVIDER_ID, PROCEDURE_ID, :item_to_add
from PREFERENCE_DETAIL a
where RESOURCE_ID = :search_item
and not exist (select * from PREFERENCE_DETAIL where RESOURCE_ID =
:item_to_add and a.PROVIDER_ID = PROVIDER_ID and a.PROCEDURE_ID =
PROCEDURE_ID)
This is assuming that the REFERENCE_ID is the item field you are talking about. Not sure where SYSTEM_ID fits in this. Note the 'and not exists' clause will exclude any records where the item to add already exists for a found provider/procedure pair.
Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx
-----midrange-l-bounces@xxxxxxxxxxxx wrote: -----
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
From: jmmckee <jmmckee@xxxxxxxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
Date: 12/30/2009 05:03PM
Subject: Re: Adding a row to selected detail rows with SQL
Add rows to a table. The provider id and procedure id uniquely identify a set. If the new item is already in the set, do nothing. If the set contains the old item, add the new row, with existing provider id and procedure id. The table contains in excess of 10000 rows. Something like 700 need to be inserted. And, after that insert completes, the search value needs to be changed to another value. Changing the search value to a new value is easy - just replacing one item with another.
The new item won't likely be in any result set, but will need to be added IF another item IS in the result set. The new item >could< be in a result set (provider id/procedure id) IF somebody was EXTREMELY industrious. I don't know how likely that the new item exists at all, or for any provider id/procedure id result set.
John McKee
-----Original message-----
From: Charles Wilt charles.wilt@xxxxxxxxx
Date: Wed, 30 Dec 2009 16:18:57 -0600
To: Midrange Systems Technical Discussion midrange-l@xxxxxxxxxxxx
Subject: Re: Adding a row to selected detail rows with SQL
John do you want to actually--
1) add a record into a table
2) add a records into a result set as if it was in the table
My query was basically #2.
#1 would look like so:
insert into PREFERENCE_DETAIL
select distinct PROVIDER_ID, PROCEDURE_ID, :item_to_add
from PREFERENCE_DETAIL
where RESOURCE_ID = :search_item
and not exist (select * from PREFERENCE_DETAIL where RESOURCE_ID =
:item_to_add)
In either case, do you understand that
select distinct PROVIDER_ID, PROCEDURE_ID, :item_to_add
from PREFERENCE_DETAIL
where RESOURCE_ID = :search_item
is going to return
000355 001201 993366
when PREFERENCE_DETAIL contains
000355 001201 112344
and
item_to_add = 993366
search_item = 112344
HTH,
Charles
On Wed, Dec 30, 2009 at 2:47 PM, jmmckee <jmmckee@xxxxxxxxxxxxxx> wrote:
I need to add item 993366 to PREFERENCE_DETAIL where any PROVIDER_ID/PROCEDURE_ID returned at least one record that had 112344. Item 993366 does not exist at all in PREFERENCE_DETAIL. Goal is to add when a provider/procedure already has item 112344.--
So, with my EXTREMELY limited knowledge of SQL, I don't see how the first select can work. The new item >might< be in the table, although I doubt it. Still needs to be checked in the event some industrious person has not done some of this manually through the application. It is a mess, and I am wondering if I am even relating it clearly.
JOhn McKee
-----Original message-----
From: Charles Wilt charles.wilt@xxxxxxxxx
Date: Wed, 30 Dec 2009 14:22:25 -0600
To: Midrange Systems Technical Discussion midrange-l@xxxxxxxxxxxx
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.
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.
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.
midrange.com
