Results 1 to 3 of 3

Thread: More SubQueries

  1. #1
    Join Date
    Jan 2004
    Posts
    1

    Red face Unanswered: More SubQueries

    Hi,

    I am trying to create a query using Oracle within ASP. I have managed to get the query working in Access as follows:

    SELECT CAnd.Name, Count(VotetoC.No2) AS CountOfNo2
    FROM CAnd INNER JOIN VotetoC ON CAnd.Cand_ID = VotetoC.Can_ID
    GROUP BY CAnd.Name
    HAVING (((Count(VotetoC.No2))=True));

    When I try this within Oracle with what I think is the corrrect syntax, I get an error every time relating to the HAVING clause. I need to count only the rows where VotetoC.No2 = True!

    Will be so grateful if anyone can help.

    Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: More SubQueries

    Wow, that's a lot of parentheses on that last line! 4 sets, where 1 would suffice!

    Probably in Access, True is a 1-bit integer equal to 1. In Oracle's SQL, True does not exist as a value. In any case, the COUNT of some values is a number, not a boolean value. So change the code to:

    SELECT CAnd.Name, Count(VotetoC.No2) AS CountOfNo2
    FROM CAnd INNER JOIN VotetoC ON CAnd.Cand_ID = VotetoC.Can_ID
    GROUP BY CAnd.Name
    HAVING Count(VotetoC.No2)=1;

    ... or whatever you meant by True.

  3. #3
    Join Date
    Jan 2004
    Location
    Issy les Moulineaux, France
    Posts
    24
    Do you want to get for each CAnd.Name the count of "true" VotetoC.No2 ?

    Try this query :
    SELECT CAnd.Name, Count(VotetoC.No2) AS CountOfNo2
    FROM CAnd,VotetoC
    where CAnd.Cand_ID = VotetoC.Can_ID
    and VotetoC.No2 = 'True'
    GROUP BY CAnd.Name;

    Replace VotetoC.No2 = 'True' by the condition you want. As andrewst said, there no boolean values in SQL.

    Please explain more if this is not what you want.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •