Sql Server, Select Statement Inside a 'Case'

Question

Can anyone help me with the right syntax in SQL Server, I have something that looks like this inside a larger query

VSCS.ISCOTEACH,
VSCS.NoMARKS,
CASE (SELECT COUNT(*) FROM tbl_CoTeacher COTH WHERE    COTH.CLAS_METG_TIME_PK=VSCS.CLAS_METG_TIME_PK
        AND ISNULL(COTH.DELT_FLAG,0)=0) > 0 THEN 1 ELSE 0 END AS HASCOTEACH),

The third line on is not correct of course, pretty much I want a column called HASCOTEACH to contain a 1 if the query SELECT COUNT(*) FROM tbl_CoTeacher COTH WHERE COTH.CLAS_METG_TIME_PK=VSCS.CLAS_METG_TIME_PK AND ISNULL(COTH.DELT_FLAG,0)=0 returns a value greater than 1, 0 otherwise.

Accepted Answer

You can try like this..

(CASE when (SELECT COUNT(*) FROM tbl_CoTeacher COTH WHERE    COTH.CLAS_METG_TIME_PK=VSCS.CLAS_METG_TIME_PK
        AND ISNULL(COTH.DELT_FLAG,0)=0) > 0 THEN 1 ELSE 0 END) AS HASCOTEACH

Answer 2

Try Something like following :

VSCS.ISCOTEACH,
VSCS.NoMARKS,
CASE WHEN 
      (
       SELECT COUNT(*) FROM tbl_CoTeacher COTH WHERE COTH.CLAS_METG_TIME_PK=VSCS.CLAS_METG_TIME_PK
       AND ISNULL(COTH.DELT_FLAG,0)=0
       ) > 0 THEN 1 ELSE 0 END AS HASCOTEACH,

Sql Server, Select Statement Inside a 'Case'

2 Answers

Must Read

Your Answer

Sophia Al-Mansoor

Sql Server, Select Statement Inside a 'Case'

2 Answers

Must Read

Your Answer

Sign up or log in

Post as a guest

Sophia Al-Mansoor