SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SELECT DISTINCT
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

divan
Posting Yak Master

153 Posts

Posted - 02/21/2013 :  08:04:20  Show Profile  Reply with Quote
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_NAME
FROM 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

Switzerland
746 Posts

Posted - 02/21/2013 :  08:10:12  Show Profile  Reply with Quote

SELECT

DISTINCT C.CLAIMANT_NUMBER

FROM 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)

Edited by - slimt_slimt on 02/21/2013 08:10:39
Go to Top of Page

divan
Posting Yak Master

153 Posts

Posted - 02/21/2013 :  08:13:18  Show Profile  Reply with Quote
Have tried this and have been getting this error message

An expression of non-boolean type specified in a context where a condition is expected, near ')'.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3557 Posts

Posted - 02/21/2013 :  08:23:28  Show Profile  Reply with Quote
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_NAME
FROM   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
--       )
Go to Top of Page

divan
Posting Yak Master

153 Posts

Posted - 02/21/2013 :  08:31:12  Show Profile  Reply with Quote
The WHERE clause is trying to be used to only select distinct CLAIMANT_NUMBER from the CLAIM table
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 02/21/2013 :  08:34:14  Show Profile  Reply with Quote
You can put Distinct in SELECT clause only

SELECT DISTINCT C.CLAIMANT_NUMBER,
       C.CLAIMANT_NAME
FROM   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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000