Singleton Error - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Singleton Error

Author

Topic

Spionred
Starting Member

8 Posts

Posted - 2009-06-25 : 07:08:45

Hi,

I had the following Select statement embedded as one of the rows of a bigger query for an Interbase Database (OK, I know its an SQL Server forum but if it counts for anything I am putting the result into an SQL Database and lets face it, Interbase forums are the liveliest now are they?)...Anyway:

(SELECT DISTINCT CLASSIFICATION.READCODE
FROM CLASSIFICATION
WHERE CLASSIFICATION.PATIENTID = ANYBODYPAT.PATIENTID AND
CLASSIFICATION.READCODE = 'C10.00' AND
CLASSIFICATION.ROWINACTIVE = 0) As DIA,

This would return 'C10.00' if a record was found and leave the column NULL if not.

I now need to look for the existence of one of 3 codes: C10.00, C108.00 and C109.00

I have the following code:

(SELECT DISTINCT CLASSIFICATION.READCODE
FROM CLASSIFICATION
WHERE CLASSIFICATION.PATIENTID = ANYBODYPAT.PATIENTID AND
(CLASSIFICATION.READCODE = 'C10.00' OR CLASSIFICATION.READCODE = 'C108.00' OR CLASSIFICATION.READCODE = 'C109.00')
AND
CLASSIFICATION.ROWINACTIVE = 0) As DIA,

but as you can imagine I get an error as there are times when there could be more than one code.

I would just like it to return a 1 if it find at least one of these codes and a 0 if not.

How can I do this?

Cheers in advance.

Kevin

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-06-25 : 07:52:14

Using MIN or MAX instead of DISTINCT should get rid of the error.

If Interbase has the equivalent of CASE the result can then be converted to 1 or 0.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-25 : 13:17:43

why use subquery like this/ i this you can take a join with this from rest of query


Yourrest of query
INNER JOIN (SELECT CLASSIFICATION.PATIENTID,MAX(CASE WHEN CLASSIFICATION.READCODE = 'C10.00' OR CLASSIFICATION.READCODE = 'C108.00' OR CLASSIFICATION.READCODE = 'C109.00' THEN 1 ELSE 0 END) AS CodeExists
FROM CLASSIFICATION
WHERE CLASSIFICATION.ROWINACTIVE = 0
GROUP BY CLASSIFICATION .PATIENTID) t2
ON t2.PATIENTID = ANYBODYPAT.PATIENTID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources