Query/SQL wrong data type

[JoeCastor]

I am receiving “Wrong Data Type in Statement [ SELECT etc.” error message, underlying Error Code: -16 but the message doesn’t give any clues as what to be looking for. The error showed up when I included the SUM and GROUP functions. The query ran prior to that. Any direction?
The SQL is:

SELECT "PaymentHistory"."PHMemberID" AS "Badge",
       "MemberMaster"."LastName" || ' ' || "MemberMaster"."FirstName" AS "Name",
       "PaymentHistory"."PaymentDate",
       "PaymentHistory"."CheckNumber",
       SUM("PaymentHistory"."PaymentAmount"),
       SUM("PaymentHistory"."Dues"),
       SUM("PaymentHistory"."Astro"),
       SUM("PaymentHistory"."Media"),
       SUM("PaymentHistory"."Food"),
       SUM("PaymentHistory"."Other")
FROM { oj "MemberMaster"
  RIGHT OUTER JOIN "PaymentHistory" ON "MemberMaster"."MemberID" = "PaymentHistory"."PHMemberID" }
GROUP BY "PaymentHistory"."PHMemberID",
         "PaymentHistory"."PaymentDate",
         "PaymentHistory"."CheckNumber",
         "MemberMaster"."LastName",
         "MemberMaster"."FirstName"
HAVING ("PaymentHistory"."PaymentDate" >= :Date_Since)
ORDER BY "MemberMaster"."LastName" ASC,
         "MemberMaster"."FirstName" ASC

Edited to format for readability.

[Ratslinger]

@JoeCastor In future please enter code/SQL in a format which is easier to read. Once entered, highlight all of it and then select the preformatted test icon on the toolbar.

[Ratslinger]

Sorry for other posts. Afternoon a little goofy.

This statement works:

SELECT "PaymentHistory"."PHMemberID" AS "Badge",
       "MemberMaster"."LastName" || ' ' || "MemberMaster"."FirstName" AS "Name",
       "PaymentHistory"."PaymentDate",
       "PaymentHistory"."CheckNumber",
       SUM("PaymentHistory"."PaymentAmount")
FROM { oj "MemberMaster"
  RIGHT OUTER JOIN "PaymentHistory" ON "MemberMaster"."MemberID" = "PaymentHistory"."PHMemberID" }
Where ("PaymentHistory"."PaymentDate" >= :Date_Since)
GROUP BY "PaymentHistory"."PHMemberID",
         "PaymentHistory"."PaymentDate",
         "PaymentHistory"."CheckNumber",
         "MemberMaster"."LastName",
         "MemberMaster"."FirstName"
ORDER BY "MemberMaster"."LastName" ASC,
         "MemberMaster"."FirstName" ASC

Eliminated most of the SUM statements (only because of no data to test with).

Biggest changes:

Having changed to Where - was causing syntax error.

Moved the Where to before Group By & that eliminated the problem.

[JoeCastor]

Thanks for the review and correction. I had remodeled the PaymentHistory table yesterday so it’s now different. Sorry. I realized that I misunderstood what SUM was actually doing and that I need to look for a different solution. I wanted to have a total at the end of each column (not row) of data as a total row (like a Calc SUM [range]. Not sure this is possible. Back to the manuals.

[Ratslinger]

Based upon your SQL and now your comment, it is not clear what totals you actually want. It is sounding like you want a LIST of all payments and then a total record at the end showing the total amount for each category. Is that it or something else? It could be done with two selects & a union.

[Ratslinger]

Before going too far with my last comment, although Union will work in Base SQL, you need to use Run SQL directly which then negates the use of a parameter. Catch 22. Instead, maybe use Report Builder based on the Detail list which can then generate totals from there. Another possibility may be a form. I’ve generated totals there too.

[JoeCastor]

That’s it. You are correct in my goal. I’m looking into doing it with Report. I’ll keep you posted.

[JoeCastor]

With multiple crashes to contend with in Report, I was able to accomplish what I wanted (except for formatting that seems to crash each time I get it the way I want and just before I save it). Any way, this case is closed. Thanks for all you do for me.