T
tthomas via AccessMonster.com
Greetings,
I am having very odd results with a query that is performing a comparison on
a calculated value
My base query sql is:
SELECT dbo_table.DataDate, dbo_table.MarketId, dbo_table.Duration1, dbo_table.
Duration2, ([Duration1]/[Duration2]) AS Percentof
FROM dbo_table
WHERE (((dbo_table.MarketId)=230))
ORDER BY dbo_table.DataDate, ([Duration1]/[Duration2]) DESC;
Works fine. The numbers are correct. Percent of is correct Order is
correct
UNTIL
i do a comparison on the Percentof Field. say I add < .8 so now my sql
looks like this:
SELECT dbo_table.DataDate, dbo_table.MarketId, dbo_table.Duration1, dbo_table.
Duration2, ([Duration1]/[Duration2]) AS Percentof
FROM dbo_table
WHERE (((dbo_table.MarketId)=230) AND ((([Duration1]/[Duration2]))<0.8))
ORDER BY dbo_table.DataDate, ([Duration1]/[Duration2]) DESC;
My results will include values that are clearly above the .8 threshold. (.
95, .97, .91 etc). If I do > .8, I only get percentof values of 1.0. I
have ensured that the duration values are numerical. I have 'Int'ed both
duration1 and duration 2 in the calculation. I have tried multiplying the
percentof value by 100 - the value is correct, but I can't do the comparison.
When I export the data to excel or another table - i CAN do the comparison on
that data - it is just going to add a wicked lot of effort to the project to
create the temp table and then display results in the form off of the temp
table and the delete it afterwards.
My gut is the dsn isn't working right, but why can i do math calcs except for
comparisons.
I am running against Sybase using ODBC and a standard ASE driver. Also, I
don't seem to be able to do it with a file dsn - it has to be machine or user
dsn's.
Any ideas or has anyone ever run into anything like this?
Regards,
Teresa
I am having very odd results with a query that is performing a comparison on
a calculated value
My base query sql is:
SELECT dbo_table.DataDate, dbo_table.MarketId, dbo_table.Duration1, dbo_table.
Duration2, ([Duration1]/[Duration2]) AS Percentof
FROM dbo_table
WHERE (((dbo_table.MarketId)=230))
ORDER BY dbo_table.DataDate, ([Duration1]/[Duration2]) DESC;
Works fine. The numbers are correct. Percent of is correct Order is
correct
UNTIL
i do a comparison on the Percentof Field. say I add < .8 so now my sql
looks like this:
SELECT dbo_table.DataDate, dbo_table.MarketId, dbo_table.Duration1, dbo_table.
Duration2, ([Duration1]/[Duration2]) AS Percentof
FROM dbo_table
WHERE (((dbo_table.MarketId)=230) AND ((([Duration1]/[Duration2]))<0.8))
ORDER BY dbo_table.DataDate, ([Duration1]/[Duration2]) DESC;
My results will include values that are clearly above the .8 threshold. (.
95, .97, .91 etc). If I do > .8, I only get percentof values of 1.0. I
have ensured that the duration values are numerical. I have 'Int'ed both
duration1 and duration 2 in the calculation. I have tried multiplying the
percentof value by 100 - the value is correct, but I can't do the comparison.
When I export the data to excel or another table - i CAN do the comparison on
that data - it is just going to add a wicked lot of effort to the project to
create the temp table and then display results in the form off of the temp
table and the delete it afterwards.
My gut is the dsn isn't working right, but why can i do math calcs except for
comparisons.
I am running against Sybase using ODBC and a standard ASE driver. Also, I
don't seem to be able to do it with a file dsn - it has to be machine or user
dsn's.
Any ideas or has anyone ever run into anything like this?
Regards,
Teresa