View previous topic :: View next topic
|
Author |
Message |
tecnokrat Warnings : 1 Active User
Joined: 22 May 2009 Posts: 160 Location: Bangalore
|
|
|
|
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 |
|
|
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008 Posts: 1249 Location: Richfield, MN, USA
|
|
|
|
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 |
|
|
tecnokrat Warnings : 1 Active User
Joined: 22 May 2009 Posts: 160 Location: Bangalore
|
|
|
|
Thanks a lot it solved my problem |
|
Back to top |
|
|
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008 Posts: 1249 Location: Richfield, MN, USA
|
|
|
|
Please share your solution for the benefit of others. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
omg : use distinct is a donkey solution.
maybe you should specify a join condition when joining 2 tables ? |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
How will the join eliminate duplicates? |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hi Guy,
Thanks for the clarification
d |
|
Back to top |
|
|
tecnokrat Warnings : 1 Active User
Joined: 22 May 2009 Posts: 160 Location: Bangalore
|
|
|
|
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 |
|
|
|