[thelist] SQL Query Help after upgrade from Access
Ken Schaefer
ken.schaefer at gmail.com
Mon Sep 13 19:13:47 CDT 2004
Two things:
a) You are running into a type issue. COUNT() returns an integer.
Dividing an integer by another integer results in an integer. Integers
are whole numbers - so the closest whole number to your result happens
to be 0.
b) You don't need to use two queries - you can do this in one query,
and avoid all the inline, messy SQL.
SELECT
CAST(COUNT(facttbl.tableID) AS Decimal) /
SELECT CAST(COUNT(facttbl.tableID) AS Decimal)
FROM
facttbl
WHERE
Gender = 'male'
You could even put that into a stored procedure:
CREATE PROC usp_GetMales
@MalePercentage int OUTPUT
AS
SELECT
@MalePercentage =
CAST
(
CAST(COUNT(facttbl.tableID) AS Decimal) /
SELECT CAST(COUNT(facttbl.tableID) AS Decimal) * 100
AS Integer
)
FROM
facttbl
WHERE
Gender = 'male'
GO
and then you ASP code would be:
Set objCommand = Server.CreateObject("ADODB.Command")
Set objCommand.ActiveConnection = objConn
objCommand.Parameters.Append _
objComand.CreateParameter("@MalePercentage", adInteger, adParamOutput, 4)
objCommand.Execute
intPercentage = objCommand.Parameters("@MalePercentage").Value
Set objCommand = Nothing
Cheers
Ken
On Mon, 13 Sep 2004 16:46:04 -0400, Michael Pack <michaelpack at wvdhhr.org> wrote:
> Hi all, I'm using ADO and SQL Server 2000. I'm upgrading an application
> from Access to SQL and ran into a problem with no error response.
>
> What worked against an Access database:
>
> strSelCount = "SELECT count(gender)/" & strRecCount & " as GenderCnt
> FROM facttbl WHERE gender = 'female'"
> set rs = cn.Execute(strSelCount)
> Response.Write("Females: " & FormatPercent(rs("GenderCnt")) & vbcrlf )
>
> and also worked of course with the divisional number hardcoded......
>
> strSelCount = "SELECT count(gender)/588 as GenderCnt FROM facttbl WHERE
> gender = 'male'"
> set rs = cn.Execute(strSelCount)
> Response.Write("Males: " & FormatPercent(rs("GenderCnt")) & vbcrlf )
>
> I cannot get to work against the SQL database, it's returning 0 with no
> errors....
>
> I'm getting my record count number with no problems with
>
> set rs1 = oConn.execute("SELECT COUNT(pk) as RecCount FROM facttbl")
> strRecCount = rs1("RecCount")
>
> and passing it in as
>
> set rs2 = oConn.execute("SELECT count(gender)/" & strRecCount & " as
> GenderCnt FROM facttbl WHERE gender = 'male'")
> Response.Write("Males: " & FormatPercent(rs2("GenderCnt")) & vbcrlf )
>
> I've ran the rs2 query directly through Query Analyzer and it parses
> fine but returns a 0 as well. So I must assume I am making a mistake
> with the calculation portion of the query.
>
> Any assistance is greatly appreciated.
>
> TIA,
>
> MP
> --
>
> News! - Evolt.org conference for web professionals.
> 17-19 September 2004 in Toronto, Canada.
> Details at http://TOevolt.org
>
> * * Please support the community that supports you. * *
> http://evolt.org/help_support_evolt/
>
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>
More information about the thelist
mailing list