Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Query for fetching matching data in two columns

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Poha Eater

New User


Joined: 31 Aug 2016
Posts: 45
Location: India

PostPosted: Mon Jul 09, 2018 6:06 pm    Post subject: Query for fetching matching data in two columns
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

Senior Member


Joined: 21 Sep 2010
Posts: 2038
Location: NY,USA

PostPosted: Mon Jul 09, 2018 6:29 pm    Post subject:
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: 45
Location: India

PostPosted: Mon Jul 09, 2018 6:37 pm    Post subject:
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: 444
Location: USA

PostPosted: Mon Jul 09, 2018 8:52 pm    Post subject: Reply to: Query for fetching matching data in two columns
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

Senior Member


Joined: 21 Sep 2010
Posts: 2038
Location: NY,USA

PostPosted: Mon Jul 09, 2018 9:24 pm    Post subject:
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: 45
Location: India

PostPosted: Tue Jul 10, 2018 2:30 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 2038
Location: NY,USA

PostPosted: Tue Jul 10, 2018 5:07 pm    Post subject:
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: 45
Location: India

PostPosted: Tue Jul 10, 2018 6:01 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 2038
Location: NY,USA

PostPosted: Wed Jul 11, 2018 3:03 am    Post subject:
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: 45
Location: India

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

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

Senior Member


Joined: 21 Sep 2010
Posts: 2038
Location: NY,USA

PostPosted: Wed Jul 11, 2018 9:16 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. DFSORT output in CSV for data spans i... hiravibk DFSORT/ICETOOL 2 Tue Nov 13, 2018 7:55 pm
No new posts REFORMATTING WITH JFY CLOBBERING OUTP... speermb DFSORT/ICETOOL 10 Thu Nov 08, 2018 7:20 pm
No new posts Migrating AS400 to HIVE; Data type ma... sindhu.jose All Other Mainframe Topics 1 Tue Nov 06, 2018 1:06 pm
No new posts Query on secondary index Suja.Sai IMS DB/DC 0 Wed Oct 31, 2018 9:47 pm
No new posts Query regarding STOP REGION XX ABDU... ashek15 IMS DB/DC 11 Fri Oct 19, 2018 10:13 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us