Complex SQL Math Statement

korthof
korthof used Ask the Experts™
on
I need help deriving a sql statement that can select a specific criteria from my database.

<%
Set Con = Server.CreateObject("ADODB.Connection")
Con.Open Application("DSN_Pipeline")

Set catRS2 = Server.CreateObject( "ADODB.Recordset" )
catRS2.Open "SELECT * FROM AllJobTbl", Con

sqlString = "SELECT CASE CurrentValue WHEN 0 THEN 0 ELSE (Balance / CurrentValue) END AS column_alias FROM AllJobTbl"
Dim objrs
Set objrs = Con.Execute(sqlString)
%>

I need All Balance / Current Value to display those that are <= .65

also where "Credit" = "Fair" or "Poor"

WHERE "State" = AL, CA

And I need it to response a viewable table back to an asp page.
I cant seem to understand how to prevent buffer overflow on response.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SELECT CASE CurrentValue WHEN 0 THEN 0 ELSE (Balance / CurrentValue) END AS column_alias
FROM AllJobTbl
where CASE CurrentValue WHEN 0 THEN 0 ELSE (Balance / CurrentValue) END < .65
and Credit in ('Fair', 'Poor')
and State in ('AL','CA')


or to save calculating twice use a derived table.
Also what datatype are the Balance, CurrentValue fields?
I assume they are decimal(x,2) otherwise you might have problems with rounding or the display format.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial