× 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.


  1.  Home
  2. MIDRANGE-L
  3. December 2009

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.



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.