|
View previous topic :: View next topic
|
| Author |
Message |
santohsks1987
Warnings : 1
New User
.jpg)
Joined: 29 Dec 2010
Posts: 31
Location: Mumbai
|
|
|
|
Hi,
I have three fields namely A,B,C in a table and a file. I have to write a SQL query in which i ll pull out some records fro the table if any of the above 2 fields are equal. That is I ll write the query as
SELECT FIRST_NM, REL_CD, BITRTH_DT
FROM T_NEUT_MBR
WHERE
REL_CD NOT = ‘E’
AND ((FIRST_NM = WS-FIRST-NAME(SUB)
AND ((REL_CD = WS-REL-CD-SUB)
OR BIRTH_DT = WS-BIRTH_DT(SUB)))
OR (REL_CD = WS-REL_CD(SUB)
AND BIRTH_DT = WS-BIRTH_DT(SUB))
But if it gives more than 1 record than i will have to check the equality of the other field also. How should i do this. |
|
| Back to top |
|
 |
balakrishna reddy.bala
New User
Joined: 15 Sep 2010
Posts: 17
Location: india
|
|
| Back to top |
|
|
santohsks1987
Warnings : 1
New User
Joined: 29 Dec 2010
Posts: 31
Location: Mumbai
|
|
|
|
| but i dont want to use the cursors..... |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Code: |
SELECT FIRST_NM, REL_CD, BITRTH_DT,
case when FIRST_NM = :WS-FIRST-NAME(SUB) then 1 else 0 end
+ case when REL_CD = :WS-REL-CD (SUB) then 1 else 0 end
+ case when BIRTH_DT = :WS-BIRTH_DT(SUB) then 1 else 0 end
as numCondTrue
FROM T_NEUT_MBR
WHERE REL_CD NOT = ‘E’
AND (( FIRST_NM = :WS-FIRST-NAME(SUB)
AND ( (REL_CD = :WS-REL-CD (SUB)
OR BIRTH_DT = :WS-BIRTH_DT(SUB)))
OR ( REL_CD = :WS-REL_CD(SUB)
AND BIRTH_DT = :WS-BIRTH_DT(SUB))
order by 4 desc
fetch first 1 row only |
|
|
| Back to top |
|
|
santohsks1987
Warnings : 1
New User
Joined: 29 Dec 2010
Posts: 31
Location: Mumbai
|
|
|
|
@GuyC:it looks fine...... can u please give a brief explanation of the query.....
Thank u for the reply....... |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
each case instructions just returns 1 if the condition is true 0 if false.
These are added together
so you get 0,1,2 or 3 as numcondtrue
ofcourse only 2 and 3 will come out of the select because of the where-clause.
order by 4 desc : measn order by the fourth column in the result , which is numcondtrue , and DESCENDING meaning 3 first then all the 2s
fetch first 1 row only : will give you only 1 row even when there would have been more. |
|
| Back to top |
|
|
santohsks1987
Warnings : 1
New User
Joined: 29 Dec 2010
Posts: 31
Location: Mumbai
|
|
|
|
| oh its excellent..... thank you...... |
|
| Back to top |
|
|
|
|
