Link to home
Create AccountLog in
Avatar of rfwoolf
rfwoolfFlag for South Africa

asked on

SQL: Return a field value that's calculated according to a certain condition.

Below you will find a table with 4 fields and I'm showing 3 dummy records
Basically I need a SELECT statement that will also create a 5th field in the result set called 'RESULT' that's calculated like this:
*IF* "SELL USING MARKUP" [Field 2] is True, *THEN*
           RESULT = BASE VALUE[Field1] * MARKUP[Field3]
*IF* "SELL USING MARKUP" [Field 2] is False, *THEN*
           RESULT = PRICE SET[Field 4]

Examples:
Record 1 the result is $1.00
Record 2 the result is $2.80
Record 3 the result is $1.10

* The table is Paradox, the SQL implementation is Borland's "LOCAL SQL" (BDE)
* I have left out other fields - there is a key field and an index etc.
* In your solution feel free to not use these field names - the real field names are "compliant" (no spaces, etc).

If this isn't really possible, it's not the end of the world, I will just return all 4 fields in my result set and do the calculations later.
BASE VALUE | SELL USING MARKUP ? | MARKUP(IF USING MARKUP)| PRICE SET(IF SETTING)
     ($)           (LOGICAL)                 NUMBER                     ($)
1. $      1 |        True         |             100  (%)   |     $       0
2. $   1.40 |        False        |             [null]     |     $    2.80       
3. $   0.55 |        True         |             200  (%)   |     $       0

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of tigermatt
tigermatt
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of rfwoolf

ASKER

I just checked the Local SQL Help documents and "CASE" is unsupported in this implementation.

If there are no SQL solutions I will award points to the CASE solutions
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of rfwoolf

ASKER

Thanks to all. I have awarded points as best I can. Keep up the good work.