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.

2 Answers

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

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,

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy