SQL SELECT statement debugging

[MarkGibson]

I am working in PowerChurch Plus 10 on an old Windows 2000 machine. We have recently upgraded from version 9 to 10, and we have also had problems keeping files in sync between our different data entry locations (church secretary, volunteer donation entry, administrator), so a field may have changed name or contents. (Sigh. What we really need is a way to network computers across the web). But we got everything working again, except for one report. (This report worked last week, before the upgrade and "repairs" were done)

This custom report returns an error titled "Error building sort key". It says that "The SQL SELECT statement that is being executed by Query Maker has an error in it. This error may be in the join condition. It could also be int he spelling of a field or file name." It goes on to give instructions for identifying its cause by setting the ldebug_mode parameter that is passed into QM_MAN() to true. This sounds like it might be useful, but I don't know how to do it.

Here is another clue: When I rebuilt the report from scratch (identical to the original one), it now puts up a warning that no fields are identified for sorting, and that it will sort them in the order of the Grouping fields, which it then lists. Then it gives the same error message.

Thank you in advance for your suggestions.

[Zorak]

The error message you are referencing points to something missing in the query condition, usually an "AND / OR" connector. We have had problems with that happening, seemingly at random, in the past. If you look through your query in the effected report, you should see that one or more of your conditions are missing the AND/OR connector.

Click on that query connection and hit the And/Or switch button to replace the missing item.

That should fix it!

[MarkGibson]

When I go to the Edit Record Filter section of this Custom Report, I can switch from OR to AND, to no avail. (With AND, no records are found, which makes sense, but there is no SQL SELECT error). With both AND and ORt, it still complains about the absence of a sort field.

Any other ideas?

[Zorak]

To keep it from complaining about a lack of a sort field, simply assign a sort field. Be sure to use a field that is already in your list of output fields.

If you choose an OR connector, do you still get SQL errors?

[MarkGibson]

I get the same error with an OR connector.

When I went looking for where to assign a sort field, I couldn't see anything other than grouping and counting. Where do I do this?

[MarkGibson]

I can't add a sort field, because I am using a Detail/Summary Report (or if I can, I can't see how...).

When I have made a copy of the report and played with it, I can make the SQL SELECT error go away if I take off the count of the City field (or any other field), but this means that I also have to take off the Grouping in the Edit Output Fields section, and I no longer have a report grouped by one of my custom codes.

I find this all very strange, and the report programming language very hard to work with....

[Zorak]

If you choose to run it as a column layout report, there is a whole separate set of in-report data grouping. The tools available there are much more robust.

For certain, the Custom Report Writer included in PowerChurch Plus can be a real pain to use, but again, as an added feature that you don't really have to pay anything for, we can't complain too much.

It is significantly easier than mapping all those tables out and building your own through an ODBC connection with a proper reporting tool

[MarkGibson]

Thank you for the suggestion to abandon the built-in features and work with the detailed tools within the column layout report section.

I finally got the program to do what I need, even if it is not beautiful. It will do for a statistical count, which is what I was trying to produce.

On the other hand, I didn't appreciate being "reminded" that this part of the program which works so poorly was "free". If it hadn't been there, we would not have bought the program, and it is part of what will restrain me from recommending it to others. Your job, Zorak, is to be helpful to us, the clients, and to warn management about the problem areas (like this one) which will kill their future sales. I'm sure you have a tough job, dealing with frustrated people all the time, but you could have won me over by being sympathetic about how hard it is ("and management is considering ways to try to make this easier"--hopefully that is true!) and guiding me to someplace where I could learn how to make that 20 year old technology work.

All the best to you and PowerChurch,

[Zorak]

Nothing in my post was meant to be offensive.

While my job is made up of a lot of things, posting on the users forum is something extra that I do to try and help out where I can.

In working in Custom Reports myself every day, the "we can't complain too much" bit in my post was intended as conversational input into the topic at hand.

If the tone of my post came across as somehow aggressive or sarcastic, I sincerely apologize.

[NeilZ]

FWIW ... the report writer is not 20 year technology, but part of Crystal Reports 9 or 10, which is only a few years old. As such, there is little that the PC+ staff can do to modify that to make the interface easier. As Zorak said, its free (in the sense that it comes as part of the entire Crystal Reports runtime), but it is not very user friendly.

Having used PC+ for over 6 years now, I completely understood where Zorak was coming from, I can also understand your frustration, and how you took Zorak's comment about the report writer.