× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.