Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ODBC Error (Access 2000)

    I successfully connected to a DB2 table on the mainframe. Everything works perfectly in all my queries with the exception of performaing a 'Count' function. When I group on any number of fields and then count on one (or more) I receive the following error:
    The operand of a COUNT function is not valid. SQLSTATE=42607.
    I attached an XL file with the screen print of the error. I searched all over the Microsoft site but they offer little information on connectivity to competitor data s ources.
    Any help from the Forum would be greatly appreciated.
    Thanks!
    Andrew
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC Error (Access 2000)

    This is listed as the cause for that error message
    SQL0409N The operand of a COUNT function is not valid.
    Cause: As specified in the SQL statement, the operand of the COUNT function does not conform to the rules of SQL syntax. Only COUNT(*) and COUNT(DISTINCT column) are allowed.
    The statement cannot be processed.
    Action: Specify COUNT(*) or COUNT(DISTINCT column).
    NOTE: This message is only applicable to versions of DB2 prior to Version 2.
    sqlcode: -409
    sqlstate: 42607

    Can you check your Count statement

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC Error (Access 2000)

    Thanks Steve!
    I am using the following fields:
    Field: Salesperson
    Table: Sales
    Total: Group By

    Field: OrderNumbers
    Table: Sales
    Total: Count

    I am not certain how or where I would specify COUNT(*) or COUNT(DISTINCT column). I tried changing to the SQL view and typing in these specifications but Access wouldn't allow any of my combinations. Any other guidance would be appreciated.
    Cheers,
    Dashiell

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: ODBC Error (Access 2000)

    Could you please post the query or SQL view (preferred) so we can see how the Count is being performed. It sounds as if you are using the count functionality with the wrong grouping of data.
    Regards,

    Gary
    (It's been a while!)

  5. #5
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC Error (Access 2000)

    I used the sales example to illustrate what I was doing. Here is the exact SQL statement that causes the error:

    SELECT DB2PROD_HLT_REPORT_VIEW.[DTL DEPT CD], Count(DB2PROD_HLT_REPORT_VIEW.[CTC CONTACT ID]) AS [CountOfCTC CONTACT ID]
    FROM DB2PROD_HLT_REPORT_VIEW
    GROUP BY DB2PROD_HLT_REPORT_VIEW.[DTL DEPT CD];

    Everything seems to be correct and if I import the table, it runs fine. It generates the error only when the table is linked.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC Error (Access 2000)

    Well I'd try both
    SELECT <font color=red>Count(*) </font color=red>
    FROM DB2PROD_HLT_REPORT_VIEW
    GROUP BY DB2PROD_HLT_REPORT_VIEW.[DTL DEPT CD];
    and
    SELECT DB2PROD_HLT_REPORT_VIEW.[DTL DEPT CD], Count(<font color=red>Distinct </font color=red>DB2PROD_HLT_REPORT_VIEW.[CTC CONTACT ID]) AS [CountOfCTC CONTACT ID]
    FROM DB2PROD_HLT_REPORT_VIEW
    GROUP BY DB2PROD_HLT_REPORT_VIEW.[DTL DEPT CD];
    to see if they work, but I can't see what's wrong with your original query

  7. #7
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC Error (Access 2000)

    Steve,
    Bravo! That did the trick. I still don't know why the original SQL failed. I still receive odd errors from time to time but that's ok...I can work around them. This one had me stumped so thanks a lot!!
    Andrew

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC Error (Access 2000)

    Glad to hear sorted Andrew
    - I still find 'features' with connecting via ODBC, e.g. with our Oracle tables, why do I get an error using the Oracle ODBC driver when some of the records have a Null value in the field used in a join to a local Access table ??

  9. #9
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC Error (Access 2000)

    That's a good question. Every once in a while I receive strange errors using ODBC. I've never worked in Oracle so I can't be much help. Sorry.

Posting Permissions

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