bad case statement?

CASE WHEN u.sImage1_nm is NULL then 0
ELSE WHEN u.sImage2_nm is NULL then 1
ELSE WHEN u.sImage3_nm is NULL then 2
ELSE WHEN u.sImage4_nm is NULL then 3
ELSE WHEN u.sImage5_nm is NULL then 4
end
-- what's wrong here?Looks good to me. Can you show the whole (simplified) query? Or, better yet, a repro?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
news:1faa8782.0408082134.496c2e97@.posting.google.com...
> CASE WHEN u.sImage1_nm is NULL then 0
> ELSE WHEN u.sImage2_nm is NULL then 1
> ELSE WHEN u.sImage3_nm is NULL then 2
> ELSE WHEN u.sImage4_nm is NULL then 3
> ELSE WHEN u.sImage5_nm is NULL then 4
> end
>
> -- what's wrong here?|||Tibor
It is wrong syntax assuming that below query (he missed addition CASE and
END respectively)
select CASE WHEN EndDate is NULL then 0
ELSE CASE WHEN InDate is NULL then 1
ELSE CASE WHEN Stardate is NULL then 2
end END END
from table
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e8UlrvdfEHA.2468@.TK2MSFTNGP12.phx.gbl...
> Looks good to me. Can you show the whole (simplified) query? Or, better
yet, a repro?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
> news:1faa8782.0408082134.496c2e97@.posting.google.com...
> > CASE WHEN u.sImage1_nm is NULL then 0
> > ELSE WHEN u.sImage2_nm is NULL then 1
> > ELSE WHEN u.sImage3_nm is NULL then 2
> > ELSE WHEN u.sImage4_nm is NULL then 3
> > ELSE WHEN u.sImage5_nm is NULL then 4
> > end
> >
> >
> > -- what's wrong here?
>|||The extra else's are what is killing you... Try
select
CASE
WHEN u.sImage1_nm is NULL then 0
WHEN u.sImage2_nm is NULL then 1
WHEN u.sImage3_nm is NULL then 2
WHEN u.sImage4_nm is NULL then 3
WHEN u.sImage5_nm is NULL then 4
end from yourtable
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
news:1faa8782.0408082134.496c2e97@.posting.google.com...
> CASE WHEN u.sImage1_nm is NULL then 0
> ELSE WHEN u.sImage2_nm is NULL then 1
> ELSE WHEN u.sImage3_nm is NULL then 2
> ELSE WHEN u.sImage4_nm is NULL then 3
> ELSE WHEN u.sImage5_nm is NULL then 4
> end
>
> -- what's wrong here?