Can someone tell me what this Where clause (from MS Access) should be T-SQL?
Basically, if the value in the [reason] field contains 'DIST' then the row should return as long as the value in [movement] is greater than or equal to 1.
Where IIf([reason] LIKE '%DIST%',Val([movement]),1)>=1
Thanks!
something like this. "iif" is not a valid sql fucntion
where reason like '%dist%' and movement=> 1
val is also not valid so you may use cast or convert
therefore
where reason like '%dist%' and cast (movement, money) => 1
|||
You can do below:
where case when reason like '%DIST%' then sign(Movement) end = 1
-- or
where reason like '%DIST%'
and sign(Movement) = 1
-- or below which will use index on Movement if available
where reason like '%DIST%'
and Movement >= 1
|||Replace the IIF() with Case:
Case when [reason] like '%DIST%' then...
Am I correct in intrepreting Val() as converting to a number (saves me a google)? Then you want to use Convert().
Final syntax:
Where Case When [reason] Like '%Dist%' then Convert(int, [movement] ) Else 1 End >= 1
You could also use NULL with the Else case.
....Guess I'm a slow typist...
|||Thanks anomolous! That was the right answer. I just had to change int to real because [movement] holds a decimal value.
Yes, Val() in Access converts a string to a number And if it's NULL, Val() will return 0. Will Convert() return a 0 for a NULL value? Or do I need to use a different function so that I get a 0 if [movement] holds a NULL value?
|||You have to use coalesce or isnull to check for NULL values. All built-ins return NULL for NULL input. Do something like:
where reason like '%DIST%'
and cast(coalesce(Movement, '') as int) >= 1
Note that CAST or CONVERT in TSQL will return error if the value cannot be converted. I don't know the behavior of VAL in Access. So in that case, you will have to do additional checks like:
where reason like '%DIST%'
and cast isnumeric(Movement) when 1 then cast(coalesce(Movement, '') as int) end >= 1
Even use of ISNUMERIC will not work for all cases since it checks for integer, numeric and money data type conversion semantics. So it is possible that you could have values that can convert to money but not int. So you will be better off actually modifying the schema such that Movement column is integer and it stores only integer values. Mixing different data types in a string column and manipulating it using TSQL is problematic in lot of ways. And it is often done incorrectly leading to bad performance due to conversions, wrong results, run-time errors and so on.
No comments:
Post a Comment