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



First of all - my apologies for not answering before now
A 4 day week end definitely throws a crimp in your style upon returning to work

I would like to thank everyone who replied to my question
I went with a CTE where a union all was performed on the 2 files
And I was able to get the results I was looking/hoping for


Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: Monday, July 03, 2017 12:23 AM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxx>
Subject: RE: Using SQl to obtain a field value from one file or another

Assumed field2 in the second and third table, do not contain NULL values and an inner join is used and both sub-selects are merged with an UNION clause, there is no need to convert any NULL value with COALESCE.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) "What is worse than training your staff and losing them? Not training them and keeping them!"
"Train people well enough so they can leave, treat them well enough so they don't want to." (Richard Branson)


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Krill, Coy
Sent: Freitag, 30. Juni 2017 20:09
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: Using SQl to obtain a field value from one file or another

Select field1, f2.field2
From file-1 f1
Join file-2 f2 using (field1)
union
Select field1, f3.field2
From file-1 f1
Join file-3 f3 using (field1)
Order by field1

Assuming you don't want anything with a null field2 (based on your sample) and assuming both field2s are compatible data types.

Then again you could have used coalesce(f2.field2, f3.field2) field2 in your original query as well.

Several ways to skin this cat.

Coy Krill
Core Processing Administrator/Analyst
Washington Trust Bank

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
Sent: 2017 June 30 10:50
To: midrange-l@xxxxxxxxxxxx
Subject: Using SQl to obtain a field value from one file or another
Importance: Low

Hi everyone
Before I forget - we are on V7r1
Happy 4th July weekend to the yanks
I may be pushing my luck - Friday afternoon before what could be a 4 day weekend for many people But - here goes I will try and keep this as simple as possible

I have 3 files
File-1 has one field with values
A
B
C
D
E
Etc.

File-2 contains 2 fields
Field 1 contains an alpha character with SOME of the values that are in
file-1 Field 2 is just data

File-3 also contains 2 fields
Field 1 contains an alpha character with SOME of the values that are in
file-1 Field 2 is just data

NOTE - the values A,B,C,D, E etc. will be in either File-2 or File-3 but NOT both

How can I combine whats in file-1 with File-2 field 2 and File-3 field 2 but in ONLY 2 columns For example - this is what I am looking to produce from an SQL statement

A Field2 from File-2
B Field2 from File-2
C Field2 from File-3
D Field2 from File-3
E Field2 from File-2

I tried the following
Select File-1.Field1, File-2.Field2, File-3.Field2 from File-1 left join
File-2 on
File-1.Field1 = File-2.Field1
left join File-3 on
File-1.Field1 = File-3.Field1

But that results in three columns - not 2

As always - all responses gratefully accepted

Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill

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

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD


---------------------------------------------------------------------
This electronic mail message and any attachments may contain confidential or privileged information and is intended for use solely by the above-referenced recipient. Any review, copying, printing, disclosure, distribution, or other use by any other person or entity is strictly prohibited under applicable law. If you are not the named recipient, or believe you have received this message in error, please immediately notify the sender by replying to this message and delete the copy you received

---------------------------------------------------------------------

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

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD

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

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: http://amzn.to/2dEadiD


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.