sql formula query

B

BlueSpud

Registered User
Messages
879
I have a column called 'status' in a MS SQL DB and I want to derive it based on a series of other fields. The logic that I want to use in the formula is as follows:

if (f3 = 1)
status = "3"
else if (f2 = 1)
status = "2"
else if (f1 = 1)
status = "1"
else
status = "0"

Can anyone please give me the code for this?
 
Why are you storing a field that is derivable from other fields in the table? That goes against the basic principles of normalisation. Why not derive the value dynamically at query time?

Anyway - are you looking for an update query or a reporting query for this? Your question is not that clear.
 
I want to place the formula in the DB so that the DB will do the calcs automatically, i.e. every time a row is updated. A number of consumers will use the DB and the actual calc is quite longwinded, so I am puting this logic in one place and consumers can then interrogate this one column for selection purposes.
 
I think he means a calculated field in the table.

try putting this in your formula definition for your field...

case when f3 = 1 then 3
when f3=2 then 2 else 0 end

modify it as you need to.
 
Yes - but by having a virtual/derived column/field generated on the server by a stored procedure or what?
 
I am using the GUI on MS SQL Server 2000, Design Table, and there is a "formula" attribute for the status field. If putting a formula in here means a stored procedure, then yes. Please forgive the imprecise language as I only hit SQL from time to time.

If I have 'quantity', 'price' and 'amount' columns, I can put a formula on the 'amount' colums as follows:
quantity * price
and when I query the DB, I will see the result.
 
ClubMan said:
Yes - but by having a virtual/derived column/field generated on the server by a stored procedure or what?
Click to expand...

I dont think you can use an SP in a calculated field formula.
 
Dont back down, I was looking forward to a heated debate
 
a function isn't the best way as they are slow, inline code is better.

If I understand your problem try this:

SELECT
CASE
WHEN F1 = 1 THEN '1'
WHEN F2 = 1 THEN '2'
WHEN F3 = 1 THEN '3'
ELSE '0'
END AS Status,
*
FROM
(
SELECT 1 AS F1, 0 AS F2, 0 AS F3
UNION ALL
SELECT 0 AS F1, 1 AS F2, 0 AS F3
UNION ALL
SELECT 0 AS F1, 1 AS F2, 0 AS F3
UNION ALL
SELECT 0 AS F1, 0 AS F2, 1 AS F3
UNION ALL
SELECT 1 AS F1, 0 AS F2, 0 AS F3
UNION ALL
SELECT 0 AS F1, 0 AS F2, 0 AS F3
) AS TestData
 
BlueSpud said:
Get out of town, I'm looking for a solution, not entertainment.... Miles, I am sure you know the answer.
Click to expand...

Just use the formula property of the field in Table Design. Did it work?
 
SteelBlue05 said:
Just use the formula property of the field in Table Design. Did it work?
Click to expand...

That is what I want to do. I have entered the above formula (although I do not understand it) in Table Design, but the compiler simply tells me that there is an error in the formula. In my naievity, I did expect something simpler.

So I'm still stuck.
 
What I posted was a query you could use in a stored procedure (or however you intend to query the data) to calculate the status field on demand rather than as a formula.

The test data bit obviously should be replaced by your table / column names.
 
This is the bit I was looking for. This sets the value in the Table design, and I can now query on this field only, which is what I want. I know it may not be the best way, but it is intuitive and it works................

Thanks a million for the help.

CASE
WHEN F1 = 1 THEN '1'
WHEN F2 = 1 THEN '2'
WHEN F3 = 1 THEN '3'
ELSE '0'
END
 
Also you could use a Database Trigger, to automatically update one table/column based on a new insert/update record or whatever.
 
mayoman2 said:
Also you could use a Database Trigger, to automatically update one table/column based on a new insert/update record or whatever.
Click to expand...

Overkill for what he wants to do. Triggers can be complex and this person doesnt have that level of experience.

Calculated field is by far the simpliest option.
 
Nice one BlueSpud wanted to do something very similar myself your code worked perfect
 
You must log in or register to reply here.
Menu
Log in
Register