Prev: I am trying to match a negative number to its exact opposite positive number
Next: Trying to migrate from SQL Server 2005 to Oracle 10g Express Edition
From: SoccerFan on 23 Jun 2010 11:39
You can also take the column and multiply by *-1 and it will reverse the sign.
If you always need positive numbers though, then use the ABS() function.

"Sh0t2bts" wrote:

> Hi All,
>
> I am trying to match a negative number to its exact opposite positive
> number, in my table ill use -1520.90 (Variable A) to match 1520.90
> (Variable B) as the match I want.
>
> First off I did a little maths and subtracted Variable A from its self
> twice A-A-A = 1520.90 (Variable B)
> In my Where cause wrote where Variable A = Variable B but I get
> nothing back.
>
> I have tried casting the values as Decimal and floats but still have
> no luck.
>
> If I copy the result into Excel and use the IF statement to match them
> I get a match.
>
> Below is my Table and statement, I only expect to get a match on lines
> 5 and 11
> 0 DocNo Z1POLine Z1Value Z1MatchedOn
> 1 6100263619 122698/1 -1550.87 1550.87
> 2 6100261249 122698/1 414.45 -414.45
> 3 6100261247 122698/2 391.97 -391.97
> 4 6100261244 122698/2 2137.5 -2137.5
> 5 6100255433 122698/2 1520.9 -1520.9
> 6 6100261245 122698/2 884.36 -884.36
> 7 6100261246 122698/1 708.32 -708.32
> 8 6100261003 122698/1 -1387.42 1387.42
> 9 6100263620 122698/2 -292.81 292.81
> 10 6100261248 122698/1 393.42 -393.42
> 11 6100255330 122698/2 -1520.9 1520.9
> 12 6100261004 122698/2 -3770.6 3770.6
>
>
> SELECT DocNo, Z1POLine, Z1Value, Z1MatchedOn FROM(
> SELECT Z1.DocNo, Z1.Type, Z1.text as Z1POLine, Z2.text as Z2POLine,
> Z1.Value as Z1Value, Z2.Value as Z2Value, Z2.Value-Z2.Value-Z2.Value
> as Z2MatchedOn, Z1.Value-Z1.Value-Z1.Value as Z1MatchedOn
> FROM Z1
> LEFT OUTER JOIN Z2 on Z1.text = Z2.text and Z1.Value = Z2.Value -
> Z2.Value-Z2.Value
> WHERE Z1.Text like '122698/%' and Z2.text is null
> ) as Data
> WHERE Z1POLine = Z1POLine and Cast(Z1Value as decimal) =
> Cast(Z1MatchedOn as decimal)
>
> Any Help is really appreachiated
> .
>
 | 
Pages: 1
Prev: I am trying to match a negative number to its exact opposite positive number
Next: Trying to migrate from SQL Server 2005 to Oracle 10g Express Edition