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



Also, I agree with Glenn and Vern that the last line should be

AND (T2.POSITIVE = T1.POSITIVE OR T2.NEGATIVE = T1.NEGATIVE);

Rob

On 2013-09-18 2:19 PM, Robert Rogerson wrote:

Try an LEFT OUTER JOIN...
SELECT *
FROM (SELECT ACCT,BRCH,TYC,YRM,FLAG
FROM FILE2) T2
LEFT OUTER JOIN (SELECT ACCT, BRCH, TYC,
YRM,FLAG,POSITIVE,NEGATIVE
FROM FILE1) T1
ON T2.ACCT = T1.ACCT
AND T2.BRCH = T1.BRCH
AND T2.TYC = T1.TYC
AND T2.YRM = T1.YRM
AND (T2.POSITIVE = T1.POSITIVE) OR (T2.NEGATIVE = T1.NEGATIVE);

Rob
On 2013-09-18 1:42 PM, A Paul wrote:

FILE1
=====

ACCT brch TYC yrm flag QUANTITY positive negative


ABCPQ 1 21 201312 2 645 0 645
ABCPQ 1 25 201312 1 1920 1920 0
ABCPQ 1 26 201312 2 192 0 192
1234C 1 25 201312 1 144 144 270


FILE2
=====

ACCT brch TYC yrm flag QUANTITY positive negative


ABCPQ 1 21 201312 2 645 0 645
ABCPQ 1 25 201312 1 1920 1920 0
ABCPQ 1 26 201312 2 192 0 192
1234C 1 25 201312 1 144 144 0
1234C 1 25 201312 1 144 0 270

SELECT * FROM
(SELECT ACCT,BRCH,TYC,YRM,FLAG FROM FILE2) T2

INNER JOIN(
SELECT
ACCT, BRCH, TYC, YRM,FLAG,POSITIVE,NEGATIVE
FROM FILE1
) T1
ON T2.ACCT = T1.ACCT
AND T2.BRCH = T1.BRCH
AND T2.TYC = T1.TYC
AND T2.YRM = T1.YRM
AND (T2.POSITIVE = T1.POSITIVE) OR (T2.NEGATIVE = T1.NEGATIVE)
I am expecting to see my outfile from this SQL have all 5 records from file2 ( they match with file1 on the conditions above), but i am getting only 4records match.
Can someone tell me what need to be fixed in my SQL to get all 5 records. I am having troule with record for account 1234C.
For account 1234C I have two records and they both match to 1234C record in FILE1(if you see T2.positive = t1.POSITIVE AND T2.NEGATIVE = T1.NEGATIVE)

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.