Finding differences between 2 files using sql
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?
Software/Hardware used:
Software/Hardware used:
ASKED:
July 31, 2009 2:32 PM
UPDATED:
August 3, 2009 3:45 PM
Answer Wiki:
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
To see all answers submitted to the Answer Wiki: View Answer History.
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