Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
divan
Posting Yak Master
153 Posts |
Posted - 2013-02-21 : 08:04:20
|
Good Morning Folks.. I have written a script that will be selecting various records from various tables all based on the CLAIMANT_NUMBER from the CLAIM file.. What I am trying to do is select only distinct CLAIMANT_NUMBER.. I can run a script to create a temp file and than use that but I was wondering if I can do some thing like this SELECT C.CLAIMANT_NUMBER,C.CLAIMANT_NAMEFROM CLAIM C INNER JOIN CLIENT C ON C.CLAIMANT_NUMBER=C.CLIENT_NUMBER INNER JOIN ADDRESS A2 ON C.CLAIMANT_NUMBER = A2.CLIENT_NUMBER ' ' OR A2.ADDRESS1 IS NOT NULL) AND A2.SEQUENCE_NUMBER = (SELECT MAX (SEQUENCE_NUMBER) FROM ADDRESS A4 WHERE A2.CLIENT_NUMBER = A4.CLIENT_NUMBER) A3.POLICY_NUMBER AND P.POLICY_DATE_TIME = A3.POLICY_DATE_TIME WHERE (SELECT DISTINCT C.CLAIMANT_NUMBER FROM CLAIM C)This is not working so i need help and I hope you get the idea what I am trying to do.. |
|
slimt_slimt
Aged Yak Warrior
746 Posts |
Posted - 2013-02-21 : 08:10:12
|
[code]SELECTDISTINCT C.CLAIMANT_NUMBERFROM CLAIM CINNER JOIN CLIENT C ON C.CLAIMANT_NUMBER=C.CLIENT_NUMBERINNER JOIN ADDRESS A2 ON C.CLAIMANT_NUMBER = A2.CLIENT_NUMBER' ' OR A2.ADDRESS1 IS NOT NULL)AND A2.SEQUENCE_NUMBER = (SELECT MAX (SEQUENCE_NUMBER) FROM ADDRESS A4 WHERE A2.CLIENT_NUMBER = A4.CLIENT_NUMBER)A3.POLICY_NUMBER AND P.POLICY_DATE_TIME = A3.POLICY_DATE_TIMEWHERE (SELECT DISTINCT C.CLAIMANT_NUMBER FROM CLAIM C)[/code] |
|
|
divan
Posting Yak Master
153 Posts |
Posted - 2013-02-21 : 08:13:18
|
Have tried this and have been getting this error messageAn expression of non-boolean type specified in a context where a condition is expected, near ')'. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-21 : 08:23:28
|
The following will parse without any syntax errors, but read through the join conditions to see if that is what you need SELECT C.CLAIMANT_NUMBER, C.CLAIMANT_NAMEFROM CLAIM C INNER JOIN CLIENT C ON C.CLAIMANT_NUMBER = C.CLIENT_NUMBER INNER JOIN ADDRESS A2 ON ( C.CLAIMANT_NUMBER = A2.CLIENT_NUMBER OR A2.ADDRESS1 IS NOT NULL ) AND A2.SEQUENCE_NUMBER = ( SELECT MAX(SEQUENCE_NUMBER) FROM ADDRESS A4 WHERE A2.CLIENT_NUMBER = A4.CLIENT_NUMBER ) AND A3.POLICY_NUMBER = 'SOMETHING HERE' AND P.POLICY_DATE_TIME = A3.POLICY_DATE_TIME--NOT SURE WHAT THIS WHERE CLAUSE IS FOR--WHERE (-- SELECT DISTINCT C.CLAIMANT_NUMBER-- FROM CLAIM C-- ) |
|
|
divan
Posting Yak Master
153 Posts |
Posted - 2013-02-21 : 08:31:12
|
The WHERE clause is trying to be used to only select distinct CLAIMANT_NUMBER from the CLAIM table |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-02-21 : 08:34:14
|
You can put Distinct in SELECT clause onlySELECT DISTINCT C.CLAIMANT_NUMBER, C.CLAIMANT_NAMEFROM CLAIM C INNER JOIN CLIENT C ON C.CLAIMANT_NUMBER = C.CLIENT_NUMBER INNER JOIN ADDRESS A2 ON ( C.CLAIMANT_NUMBER = A2.CLIENT_NUMBER OR A2.ADDRESS1 IS NOT NULL ) AND A2.SEQUENCE_NUMBER = ( SELECT MAX(SEQUENCE_NUMBER) FROM ADDRESS A4 WHERE A2.CLIENT_NUMBER = A4.CLIENT_NUMBER ) AND A3.POLICY_NUMBER = 'SOMETHING HERE' AND P.POLICY_DATE_TIME = A3.POLICY_DATE_TIME --Chandu |
|
|
|
|
|