December 12, 2014 at 9:57 am
Good Evening Guys,
I would like to ask if is it possible or is my syntax correct for the Select Case Statement below? what I need to achieve is to satisfy 2 conditions before executing a select query for this report
See my Select Case When Statement below
select
CASE WHEN PT.datatype = 7 And MVA.DateTimeValue is not null
then MVA.DateTimeValue
CASE WHEN PT.datatype = 5 And MVA.StringValue is not null
then MVA.StringValue
CASE WHEN PT.datatype = 5 And MVA.NumericValue is not null
then MVA.NumericValue
Else Null End as StoredValue
from ctc_v_processtags pt
inner join dbo.ctc_mde_valueaudit as mva on mva.UtagId = pt.utagid
inner join dbo.ctc_mde_header as mh on mh.id = mva.mdeheaderid
where mh.StartTime >= convert(datetime,@StartTime)
and mh.StartTime <= Convert(datetime,@EndTime)
and mva.AuditTime = mva.AuditTime
and pt.dssourceid in (193,19)
order by utagname desc
I would gladly appreciate Any help/critique that will come from you
Best Regards,
Noel
December 12, 2014 at 10:02 am
You just need a single CASE.
SELECT CASE
WHEN PT.datatype = 7 AND MVA.DateTimeValue IS NOT NULL
THEN MVA.DateTimeValue
WHEN PT.datatype = 5 AND MVA.StringValue IS NOT NULL
THEN MVA.StringValue
WHEN PT.datatype = 5 AND MVA.NumericValue IS NOT NULL
THEN MVA.NumericValue
ELSE NULL
END AS StoredValue
FROM ctc_v_processtags pt
INNER JOIN dbo.ctc_mde_valueaudit AS mva ON mva.UtagId = pt.utagid
INNER JOIN dbo.ctc_mde_header AS mh ON mh.id = mva.mdeheaderid
WHERE mh.StartTime >= convert(DATETIME, @StartTime)
AND mh.StartTime <= Convert(DATETIME, @EndTime)
AND mva.AuditTime = mva.AuditTime
AND pt.dssourceid IN (193,19)
December 12, 2014 at 10:09 am
Much Thanks!
would it be possible if I could also do this?
Case When Condition 1 = true then When Condition 2 = true then Execute Anything....
I would like to know what is the syntax for the case when condition I stated above if its possible
Best Regards
December 12, 2014 at 10:11 am
Stylez (12/12/2014)
Much Thanks!would it be possible if I could also do this?
Case When Condition 1 = true then When Condition 2 = true then Execute Anything....
I would like to know what is the syntax for the case when condition I stated above if its possible
Best Regards
Case is an expression which means it is used to control the results in a single column. It cannot be used to control flow and execute some other code when a row meets a certain condition.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 12, 2014 at 10:24 am
Thank you all the solution helped me a lot!
Best Regards,
Noel
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy