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

Fine tuning the query 'WHERE' clause


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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
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
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
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

Moderator Emeritus


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

PostPosted: Wed Nov 25, 2009 10:14 pm
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
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

Moderator Emeritus


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

PostPosted: Thu Nov 26, 2009 1:08 am
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Query on edit primary command CLIST & REXX 5
No new posts Query on edit primary command CLIST & REXX 1
No new posts To search DB2 table based on Conditio... DB2 1
Search our Forums:

Back to Top