IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Query for fetching matching data in two columns


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 74
Location: India

PostPosted: Mon Jul 09, 2018 6:06 pm
Reply with quote

Hi All,

I am trying to write a query for a scenario in which i need to fetch those Names from Employee table which are having same Dept and Salary. The structure of Employee table as follows :


Name Dept Salary

AAA HR 1000
BBB HR 2000
CCC IT 3000
DDD IT 3000
EEE FIN 5000
FFF FIN 5000

Result table should be like this :

CCC IT 3000
DDD IT 3000

This is the query which i have wrote :

SELECT Name FROM Employee WHERE row(Dept,Salary) IN (SELECT Dept, Salary FROM Employee GROUP BY Dept,Salary HAVING COUNT(*)>1);

But this query is giving all the rows because the Inner query selecting all 3 unique Dept & Salary.

Can anyone please let me know where i need to make the changes in the query so i can get the expected result.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2573
Location: NYC,USA

PostPosted: Mon Jul 09, 2018 6:29 pm
Reply with quote

Tell us why last two rows wouldn’t make it in the result ?
Back to top
View user's profile Send private message
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 74
Location: India

PostPosted: Mon Jul 09, 2018 6:37 pm
Reply with quote

Sorry Rohit. It was a typo. Please find the correct structure of Employee table below :


Name Dept Salary

AAA HR 1000
BBB HR 2000
CCC IT 3000
DDD IT 3000
EEE FIN 5000
FFF FIN 6000


Result table should be like this :

CCC IT 3000
DDD IT 3000
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Mon Jul 09, 2018 8:52 pm
Reply with quote

How are you running the ROW function in DB2? I don't know if it even exists for DB2. You should get an error.

ROW function is, as per my knowledge, for other DBMS such as MySQL.

.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2573
Location: NYC,USA

PostPosted: Mon Jul 09, 2018 9:24 pm
Reply with quote

RahulG31 is correct.
Poha Eater, Isn't it very simple to achieve what you asked, please search around on internet/this forum , hint is to use either sub query or EXIST .
Back to top
View user's profile Send private message
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 74
Location: India

PostPosted: Tue Jul 10, 2018 2:30 pm
Reply with quote

Hi All,

I made the slight changes in my above query and its working now. Below is the final query :

SELECT Name, Dept, Salary FROM Employee WHERE (Dept,Salary) IN (SELECT Dept, Salary FROM Employee GROUP BY Dept,Salary HAVING COUNT(*)>1);


Thanks All for your support !!

icon_smile.gif
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2573
Location: NYC,USA

PostPosted: Tue Jul 10, 2018 5:07 pm
Reply with quote

Good, EXISTS Is better to use it in such situations.
Back to top
View user's profile Send private message
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 74
Location: India

PostPosted: Tue Jul 10, 2018 6:01 pm
Reply with quote

Hi Rohit,

I was curious so wanted to ask if there is any specific difference between EXISTS and IN which can lead to performance improvement if the table size is huge ?
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2573
Location: NYC,USA

PostPosted: Wed Jul 11, 2018 3:03 am
Reply with quote

The Exists keyword evaluates true or false, but IN keyword compare all value in the corresponding sub query column. So the result data of the subquery needs to be loaded first to get compared against outer query regardless of matches
Back to top
View user's profile Send private message
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 74
Location: India

PostPosted: Wed Jul 11, 2018 8:32 pm
Reply with quote

Got it. Thanks again Rohit icon_smile.gif
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2573
Location: NYC,USA

PostPosted: Wed Jul 11, 2018 9:16 pm
Reply with quote

If you are executing the above query in online and the below table only gets updated in batch then I would suggest to create a new table and insert to it only when below condition is met and use that table in online as a inner join. This approach will avoid group by every time you hit the sp.
Code:
(SELECT Dept, Salary FROM Employee GROUP BY Dept,Salary HAVING COUNT(*)>1);

Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts compare two file to find position/dat... SYNCSORT 2
No new posts Secondary index error while loading d... IMS DB/DC 2
No new posts Matching and non matching records usi... DFSORT/ICETOOL 11
No new posts SKIP LOCKED DATA in UPDATE statement DB2 9
No new posts Need Help with on of the coalesce query DB2 4
Search our Forums:

Back to Top