Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
The table structure is like this;

Table - 1

PROCESS_DATE|CLEARING_DATE|BATCH_NO|SLIP_NO|DOC_TYPE|CHEQUE_NO|UNIQUE_IDENTIFIER

2020-02-22|2020-02-25|7135001-000850|1|3|251088|2020022571350010008500001
2020-02-22|2020-02-25|7135001-000850|1|3|012057|2020022571350010008500002
2020-02-22|2020-02-25|7135001-000850|1|3|000093|2020022571350010008500003
2020-02-22|2020-02-25|7135001-000850|1|3|389389|2020022571350010008500004
2020-02-22|2020-02-25|7135001-000850|1|3|679492|2020022571350010008500005
2020-02-22|2020-02-25|7135001-000850|2|3|389323|2020022571350010008500006
2020-02-22|2020-02-25|7135001-000850|2|3|012062|2020022571350010008500007
2020-02-22|2020-02-25|7135001-000850|2|3|679504|2020022571350010008500008
2020-02-23|2020-02-25|7135001-000850|2|3|389323|2020022571350010008500006
2020-02-23|2020-02-25|7135001-000850|2|3|012062|2020022571350010008500007 



Table - 2

PROCESS_DATE|CLEARING_DATE|BATCH_NO|SLIP_NO|DOC_TYPE|CHEQUE_NO|UNIQUE_IDENTIFIER
2020-02-22|NULL|7135001-000850|1|2|NULL|2020022271350010008500001
2020-02-22|NULL|7135001-000850|2|2|NULL|2020022271350010008500002
2020-02-23|NULL|7135001-000850|2|2|NULL|2020022371350010008500002
2020-02-25|NULL|7135001-000850|1|2|NULL|2020022571350010008500001
2020-02-25|NULL|7135001-000850|2|2|NULL|2020022571350010008500002




Out put i need
                 
PROCESS_DATE|CLEARING_DATE|BATCH_NO|SLIP_NO|DOC_TYPE|CHEQUE_NO|UNIQUE_IDENTIFIER

2020-02-22|NULL	     |7135001-000850|1|2|NULL  |2020022271350010008500001
2020-02-22|2020-02-25|7135001-000850|1|3|251088|2020022571350010008500001
2020-02-22|2020-02-25|7135001-000850|1|3|012057|2020022571350010008500002
2020-02-22|2020-02-25|7135001-000850|1|3|000093|2020022571350010008500003
2020-02-22|2020-02-25|7135001-000850|1|3|389389|2020022571350010008500004
2020-02-22|2020-02-25|7135001-000850|1|3|679492|2020022571350010008500005
2020-02-22|NULL	     |7135001-000850|2|2|NULL  |2020022271350010008500002
2020-02-22|2020-02-25|7135001-000850|2|3|389323|2020022571350010008500006
2020-02-22|2020-02-25|7135001-000850|2|3|012062|2020022571350010008500007
2020-02-22|2020-02-25|7135001-000850|2|3|679504|2020022571350010008500008
2020-02-23|NULL	     |7135001-000850|2|2|NULL  |2020022371350010008500002
2020-02-23|2020-02-25|7135001-000850|2|3|389323|2020022571350010008500006
2020-02-23|2020-02-25|7135001-000850|2|3|012062|2020022571350010008500007  
0

What I have tried:

SELECT * FROM (
SELECT
PROCESS_DATE,
CLEARING_DATE,
BATCH_NO, 
SLIP_NO,
DOC_TYPE,
CHEQUE_NO,
AMOUNT,
UNIQUE_IDENTIFIER,
COUNT(*) OVER (PARTITION BY PROCESS_DATE,SLIP_NO ) AS RN
FROM 
(
SELECT 
PROCESS_DATE,
CLEARING_DATE,
BATCH_NO, 
SLIP_NO,
DOC_TYPE,
CHEQUE_NO,
AMOUNT,
UNIQUE_IDENTIFIER  
FROM IMG_PDC_OUTWARDCLEARING
WHERE 1=1
AND CLEARING_DATE='2020-02-25'
AND BATCH_NO='7135001-000850' 
AND DOC_TYPE=3
UNION 
SELECT 
PROCESS_DATE,
CLEARING_DATE,
BATCH_NO, 
SLIP_NO,
DOC_TYPE,
CHEQUE_NO,
AMOUNT,
UNIQUE_IDENTIFIER  
FROM IMG_PDC_OUTWARDCLEARING
WHERE 1=1
AND BATCH_NO='7135001-000850' 
AND DOC_TYPE=2
)Y  )Z WHERE Z.RN <> 1
ORDER BY
PROCESS_DATE ASC,
CLEARING_CYCLE ASC,
BATCH_NO ASC,
SLIP_NO ASC,
CLEARING_DATE ASC,
SEQ_NO ASC,
UNIQUE_IDENTIFIER ASC
Posted
Updated 2-May-21 5:45am
v2
Comments
Wendelius 2-May-21 11:52am    
Can't see the reason why the data is split into two tables but beyond that if you need to combine the tables, union would be the way to do it. Rest of the conditions seem to be related to your logic.

So why do you seek an alternative way, something wrong with current approach?
k5054 2-May-21 12:01pm    
It would appear that IMG_PDC_OUTWARDCLEARING is "Table 1" in your data example, but I don't see where "Table 2" makes an appearance in your Query. Perhaps you could add actual table names to your data sample to help clear things up a bit?
[no name] 2-May-21 12:17pm    
You can do the selects to temp; THEN do the UNION on the (temp) result sets instead of one (long) statement. I also don't see you using 2 "different" FROM sources (if that's your intention).

And you don't need to list all columns when doing a UNION if everything lines up.
k5054 2-May-21 12:24pm    
Also, you have a ORDER BY CLEARING_CYCLE that appears in the query, but not in either of the tables, nor as an alias to a column in the query. Please clarify.
W Balboos, GHB 11-May-21 9:00am    
Considering how you are using it, you may as well (and would be human-readable better off) if you used and identity field instead of the unique identifier. The same value appearing in both tables . . . it's obviously not unique!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900