Finding differences between 2 files using sql

45 pts.
Tags:
SQL
V5R2
I am on V5R2 and am trying to find differences between a production file and an archived file using the following sql stmt: SELECT MAX(Filename), PORD, PLINE FROM (SELECT 'HPO' as Filename, PORD,PLINE from HPOL01 UNION ALL SELECT 'ARC' as Filename, PORD,PLINE from HPOARC) group by PORD, PLINE having count(*) = 1 When I try to execute the stmt, it tells me Keyword BY not expected. Can anyone tell me why?

Answer Wiki

Thanks. We'll let you know when a new response is added.

I think your sql should work, if it didn’t detect the closing ) …
So I would try adding a spaces before the last ).

SELECT MAX(Filename), PORD, PLINE FROM
( SELECT ‘HPO’ as Filename, PORD,PLINE from HPOL01
UNION ALL
SELECT ‘ARC’ as Filename, PORD,PLINE from HPOARC )

group by PORD, PLINE
having count(*) = 1

Phil

———————————————————
Ohhhhhhhhh … it expects the inner select to be named
see I’ve addedd as mergeFile after the close )

SELECT MAX(Filename), PORD, PLINE FROM
( SELECT ‘HPO’ as Filename, PORD,PLINE from HPOL01
UNION ALL
SELECT ‘ARC’ as Filename, PORD,PLINE from HPOARC ) as mergeFile
group by PORD, PLINE
having count(*) = 1

___________________________________________________________

Thanks Phil. That was the problem.

As you can see I am trying to detect the differences between 2 files. Is there a way to find which fields are different without having to list each field individually? Thanks for your help.

Phyllis

————————————————————
Your code is finding unmatched records – I would think you would want to run this twice
First – finds unmatch orders.

SELECT MAX(Filename), PORD FROM
( SELECT ‘HPO’ as Filename, PORD from HPOL01
UNION ALL
SELECT ‘ARC’ as Filename, PORD from HPOARC ) as mergeFile
group by PORD
having count(*) = 1

Second find unmatched lines that weren’t unmatched orders.

SELECT MAX(Filename), PORD, PLINE FROM
( SELECT ‘HPO’ as Filename, PORD,PLINE from HPOL01
UNION ALL
SELECT ‘ARC’ as Filename, PORD,PLINE from HPOARC ) as mergeFile
group by PORD, PLINE
having count(*) = 1
and PORD not in
(
SELECT PORD FROM
( SELECT ‘HPO’ as Filename, PORD from HPOL01
UNION ALL
SELECT ‘ARC’ as Filename, PORD from HPOARC ) as mergeFile
group by PORD
having count(*) = 1
)
—————————————————————————————–
Got it. Thanks for your help!

Here is an additional anwser that overwrote the previous work —

—————-
You could use EXCEPT to find record differences,

You can include all columns or specify columns to include in the comparision.

Select * FROM TEST/FILE1

EXCEPT

Select * FROM TEST/FILE2

or

SELECT TXNH_AGENT_ID, TXNH_ROUTING_TRANS, TXNH_ACH_ACCT_NBR,
TXNH_DESCRIPTION, TXNH_TXN_AMOUNT, TXNH_EFFECT_DATE,
TXNH_PROCESS_DATE, TXNH_TXN_CODE, TXNH_ACH_ACCT_TYPE,
TXNH_FAIL_IND, TXNH_BALANCE, TXNH_AGENT_COST, TXNH_CARD_COST,
TXNH_STATE_COST, TXNH_CTB_FEE, TXNH_CARD_CTB_FEE
FROM &A/TXNHIST

EXCEPT

SELECT TXNH_AGENT_ID, TXNH_ROUTING_TRANS, TXNH_ACH_ACCT_NBR,
TXNH_DESCRIPTION, TXNH_TXN_AMOUNT, TXNH_EFFECT_DATE,
TXNH_PROCESS_DATE, TXNH_TXN_CODE, TXNH_ACH_ACCT_TYPE,
TXNH_FAIL_IND, TXNH_BALANCE, TXNH_AGENT_COST, TXNH_CARD_COST,
TXNH_STATE_COST, TXNH_CTB_FEE, TXNH_CARD_CTB_FEE
FROM &B/TXNHIST

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Peeveeh
    I added spaces after the ( and before the ) but I stll got the same error. SELECT MAX(Filename), PORD, PLINE FROM ( SELECT 'HPO' as Filename, PORD,PLINE from HPOL01 UNION ALL SELECT 'ARC' as Filename, PORD,PLINE from HPOARC ) group by pord,pline having count(*) = 1 Keyword BY not expected. Valid tokens: FOR WITH FETCH ORDER UNION OP
    45 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following