[thelist] SQL Query Help after upgrade from Access

[Ken Schaefer]

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 !
>