Query Problem

[stevehatpa]

I'm somewhat of a novice with SQL 2000, so please bear with me.

I have created a large query in SQL Query Designer. Unfortunately I cannot use CASE statements in this. Essentially I need to all values in column TX_PA_HOMESTEAD.HOMESTEAD_12, that are less than 51 to become 50, and all other values to stay what they are. The statement below is what I tried and it did not work.

WHEN TX_PA_HOMESTEAD.HOMESTEAD_12<51 THEN 50 ELSE TX_PA_HOMESTEAD.HOMESTEAD_12

Does anyone have ideas as to what I can put in query designer to get the results I need? I understand everyone will suggest to use query analyzer, but I really need the answere to be in designer.

 

[gmmastros]

>> I really need the answer to be in designer.

Can you please explain that statement?

You see, I was able to come up with a query that will probably work for you. However, I did this because I was curious to see if it could be done. As such, it takes 3 functions, a multiplication, and a division in order to accomplish this. From a performance perspective, using Case/When will perform better because it is a single if check instead of messy functions and math.

Furthermore, it will be easier to maintain because it will be more obvious what the query is doing.

That being said....

Select Coalesce(NullIf(TX_PA_HOMESTEAD.HOMESTEAD_12 * Abs(Sign(TX_PA_HOMESTEAD.HOMESTEAD_12 / 50)), 0), 50) As HOMESTEAD_12_Modified

This will not return the expected results if you have values less than -50. Any value less than -50 will return it's original value.

Anyway...

Sign returns 1 of 4 values. -1, 0, 1 or NULL. So, divide your number by 50 and run the Sign function on. Then, multiply this value back to your original column. If the value was less than 50, it will be multiplied by 0, otherwise it will be multiplied by 1.

Now, your values will be 0 for those less than 50, and it's original value if over 50. So, you can use NullIf for the 0 values, and then Coalesce to convert the Null values to 50.

As you can see, this is a rather complicated mess, where a simple Case/When would be better.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Sysgrunt]

Please consider the following:

update TX_PA_HOMESTEAD
set HOMESTEAD_12 = 50
where HOMESTEAD_12 < 51

 

[ESquared]

CASE is what you need.
The query designer doesn't support that.
=
Cannot use the query designer for any reasonable solution. Period.

 

[ESquared]

Here is another unreasonable solution.

SELECT
   Coalesce(Bounds.Val, Y.Val)
FROM
   YourTable Y
   INNER JOIN (
      SELECT -2147483648, 50, 50
      UNION ALL SELECT 50, 2147483647, NULL
   ) Bounds (L, U, Val) ON T.Value > Bounds.L and T.Value <= Bounds.U

But it is still SO ugly and could even have a performance impact. A simple CASE statement is what you need.

 

[stevehatpa]

Thanks everyone for responding.

GMMASTROS-

I tried inserting your "Select Coalesce" code into a new column in Query Designer and received a "Invalid or Missing Expression" message. Any ideas as to why I would be receiving this?

Thanks.

Steve

 

[gmmastros]

If you are going to paste the code in to the column, you should NOT have SELECT as part of the code you paste in.

Paste this:

[tt][blue]Coalesce(NullIf(TX_PA_HOMESTEAD.HOMESTEAD_12 * Abs(Sign(TX_PA_HOMESTEAD.HOMESTEAD_12 / 50)), 0), 50)[/blue][/tt]



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[AlexCuse]

Step away from the database, and put your hands on top of the monitor. Don't make any sudden moves.

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page