Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Fine tuning the query 'WHERE' clause

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
tecnokrat
Warnings : 1

Active User


Joined: 22 May 2009
Posts: 160
Location: Bangalore

PostPosted: Wed Nov 25, 2009 9:56 am    Post subject: Fine tuning the query 'WHERE' clause
Reply with quote

Hi all,
I have a query which should fetch me the exact number of records for example:
Code:
 
   EXEC SQL 
      SELECT
            AA,AB,BC
           
          FROM TABLE_A,TABLE_B
       
          WHERE
                 AA   <= 06
          AND BC    = 06
     
      ORDER BY AB DESC
     
   END-EXEC.
   


The above query is fetching me the records like

06
06
05
05
04
04
03
03
02
02
01
01

any fine tiuning in the query so as to get my result as like

06
05
04
03
02
01 only from the above query.
Back to top
View user's profile Send private message

Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Wed Nov 25, 2009 10:02 am    Post subject:
Reply with quote

Please show the result of either SPUFI or QMF or ?? instead of manually keying the result. Also, have you read up on the DISTINCT function?
Back to top
View user's profile Send private message
tecnokrat
Warnings : 1

Active User


Joined: 22 May 2009
Posts: 160
Location: Bangalore

PostPosted: Wed Nov 25, 2009 2:52 pm    Post subject:
Reply with quote

Thanks a lot it solved my problem icon_smile.gif
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Wed Nov 25, 2009 9:16 pm    Post subject:
Reply with quote

Please share your solution for the benefit of others.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Nov 25, 2009 10:04 pm    Post subject:
Reply with quote

omg : use distinct is a donkey solution.

maybe you should specify a join condition when joining 2 tables ?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Nov 25, 2009 10:14 pm    Post subject:
Reply with quote

Hello,

How will the join eliminate duplicates?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Nov 25, 2009 10:27 pm    Post subject:
Reply with quote

Table_A obviously has 6 rows that qualify AA <= 06
Table_B apparently has 2 rows that qualify BC = 06

However if you specify two tables in a select, it is very likely that there is a relation between the two.
for example :
4 rows of A have a relation with one row of B, and 2 rows of A have a relation with the second row of B.

Establishing the correct relation via a join-condition wil neatly get you exactly 6 rows without the need for distinct
Code:

Table A  TableB
AA  AB     AB BC
 1   x     x   6
 2   x     y   6
 3   x
 4   x
 5   y
 6   y

Select AA from TabA , TabB where AA <= 6 and BC = 6
==> 12 rows
Select AA from TabA , TabB where AA <= 6 and BC = 6 and TabA.AB = TabB.AB
==> 6 rows
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Nov 26, 2009 1:08 am    Post subject: Reply to: Fine tuning the query 'WHERE' clause
Reply with quote

Hi Guy,

Thanks for the clarification icon_smile.gif

d
Back to top
View user's profile Send private message
tecnokrat
Warnings : 1

Active User


Joined: 22 May 2009
Posts: 160
Location: Bangalore

PostPosted: Thu Nov 26, 2009 10:36 am    Post subject:
Reply with quote

thanks guyc i have tried the option as you suggested to get me the unique records pertained to a particular person.

Code:

SELECT AA,AB,BC

FROM TABLE_A, TABLE_B

WHERE
       TABLE_A.AA = :TABLE_A.AA
AND TABLE_A.AB = :TABLE_A.AB
AND TABLE_B.AA =  TABLE_A.AA
AND TABLE_B.AB =  TABLE_A.AB
AND TABLE_A.AA <= 6
AND TABLE_B.BC = 6



actually terry, as you suggested the DISTINCT it fetches me the unique records pertained to one person but if the other one has some records
it was fetching those too.

like if the person A has records 1 thru 10 and person B has only record 11 in the tables so the query with DISTINCT is fetching me the results from 1 thru 11 records.
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
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts Batch job tuning sgandhla Testing & Performance analysis 5 Fri Mar 24, 2017 9:41 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us