July 18, 2005 at 8:18 am
Hi,
I'm trying to convert (-) to (+) and vice versa. The field datatype for f2 is float,
Here is the SQL:
SELECT f1,f2,f3
CASE
WHEN f1 = 'K' THEN
-(view_v.f2)
WHEN f1 = 'D' THEN
+(view_v.f2)
END 'glamount'
FROM view_v
WHERE f3 = 'somevalue';
This SQL is not working, is this the correct way of doing this or is there another way of conversion.
Thanks,
Joe
July 18, 2005 at 8:38 am
Joe,
Try this:
SELECT f1,f2,f3
CASE
WHEN f1 = 'K' THEN
-1*(view_v.f2)
WHEN f1 = 'D' THEN
-1*(view_v.f2)
END 'glamount'
FROM view_v
WHERE f3 = 'somevalue'
It will convert negatives to positives and vice versa as desired.
HTH,
Ravinder
July 18, 2005 at 9:00 am
Hi Again,
This is what I came up with after reviewing the data more carefully.
SELECT voucherdate,vouchernumber,vouchertype,accountnumber,
debitcreditcode,
CASE
WHEN debitcreditcode = 'K' AND amount > 0 THEN
-1*(amount)
WHEN debitcreditcode = 'D' AND amount < 0 THEN
-1*(amount)
ELSE
amount
END 'glamount'
FROM glentries_v
The criteria determines the type of sign. In case one if the code = K and amount is less than > 0 then convert that amount to a negative value case two for code = D and amount < 0 then convert that amount to positive value otherwise use the existing calculation. In my opinion it's just weird how this ERP system stores values.
Thanks for everything, as usual I receive a quick response and correct answer.
Joe
July 18, 2005 at 12:08 pm
If you plan to use this on an update I would recomment to move those criterias to the where clause
SELECT voucherdate,vouchernumber,vouchertype,accountnumber,
debitcreditcode, -1*(amount) as 'glamount'
FROM glentries_v
WHERE (debitcreditcode = 'D' AND amount < 0 ) or (debitcreditcode = 'K' AND amount > 0)
* Noel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply