Results 1 to 4 of 4
  1. #1
    Join Date
    May 2009
    Posts
    4

    Unanswered: Can't find what's wrong with this query

    Hello, I'm not very familiar with the SQL language and I'm using Pervasive Data Manage to build a query.
    What I want to achieve is this:
    In Sage Business Visipon there is one table called CUSTOMER and another called SALES_HISTORY_HEADER. I want to know the how many orders a customer have placed in our system, for customers whose last order was before Nov. 31, 2008
    I have this query:
    Code:
    SELECT "CUSTOMER"."CUS_NO", "CUSTOMER"."LAST_DATE", COUNT("SALES_HISTORY_HEADER"."NUMBER") AS "TOTALORDERS" 
    FROM "CUSTOMER" INNER JOIN "SALES_HISTORY_HEADER"
    ON  "CUSTOMER"."CUS_NO" = "SALES_HISTORY_HEADER"."CUST_NO"
    WHERE "CUSTOMER"."LAST_DATE" < '20081131'
    GROUP BY "CUSTOMER"."CUS_NO"
    But then I get this error message: ODBC Error: SQLSTATE = 37000, Native error code = 0
    Column invalid. Must be a group by column: LAST_DATE in SELECT LIST.

    If I change the group by line with this:
    Code:
    GROUP BY "CUSTOMER"."LAST_DATE"
    ODBC Error: SQLSTATE = 37000, Native error code = 0
    Column invalid. Must be a group by column: CUS_NO in SELECT LIST.

    Could anybody help me with this syntax, please?

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    I haven't spent a lot of time on this but I think this might work:
    SELECT "CUSTOMER"."CUS_NO", "CUSTOMER"."LAST_DATE", COUNT("SALES_HISTORY_HEADER"."NUMBER") AS "TOTALORDERS"
    FROM "CUSTOMER" INNER JOIN "SALES_HISTORY_HEADER"
    ON "CUSTOMER"."CUS_NO" = "SALES_HISTORY_HEADER"."CUST_NO"
    WHERE "CUSTOMER"."LAST_DATE" < '20081131'
    GROUP BY "CUSTOMER"."CUS_NO", "CUSTOMER"."LAST_DATE"
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    May 2009
    Posts
    4
    Wow, that actually worked. So I needed to have to grouping, heh? Thanks a lot!

  4. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Glad it helped. Basically, you have to have all of the select list excluding functions (like count) in the GROUP BY. For example:
    select F1, F2, F3, count(F4) from TABLE group by F3,F2,F1
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

Posting Permissions

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