Ask Your Question
0

Query/SQL wrong data type [closed]

asked 2017-08-01 22:59:02 +0200

Joe Castor gravatar image

updated 2017-08-02 01:14:56 +0200

Ratslinger gravatar image

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Joe Castor
close date 2017-08-03 02:41:52.626798

Comments

@Joe Castor 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 gravatar imageRatslinger ( 2017-08-02 01:35:58 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2017-08-02 03:15:00 +0200

Ratslinger gravatar image

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.

edit flag offensive delete link more

Comments

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.

Joe Castor gravatar imageJoe Castor ( 2017-08-02 14:59:18 +0200 )edit

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 gravatar imageRatslinger ( 2017-08-02 15:20:28 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2017-08-02 16:30:37 +0200 )edit

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

Joe Castor gravatar imageJoe Castor ( 2017-08-02 18:10:56 +0200 )edit

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.

Joe Castor gravatar imageJoe Castor ( 2017-08-03 02:41:21 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2017-08-01 22:59:02 +0200

Seen: 75 times

Last updated: Aug 02 '17