Need help with setting up a custom query using $_GET parameters

[ccorley]

I’m setting up a custom query to display the monthly totals based on some conditions (filters). The first part works well without the conditions, but as soon as I add the WHERE conditions, I get an error (see images). The GET parameters are used as filters and I was hoping to set-up a query with conditions similar to the “contains” condition in the standard wrappler query builder. I hope that makes sense.

So basically I have input fields on my webpage that are used to filter the data from the table. By the standard Wrappler query builder the condition would look something like “variable_name CONTAINS $_GET.variable_name”. How does this work with the custom query?

[sid]

You need to defined the parameters in the custom query using @ or :.
So, your where clause should read something like:
where expense.fy like :fy and expense.account_name like :account

Then, when you click the + icon to define parameters, it will automatically pickup the parameter names as :fy & :account etc one-by-one.

[ccorley]

Sid,

Sorry for the late reply. I was finally able to try out your suggestion. Unforutnately, I’m getting the same error. Here’s what I entered for the query:

[sid]

Sorry… My bad. I did not actually read the error.
The parameter thing was incorrect, but actually, the group by clause was the reason for the exception.

You will have to put ‘where’ clause before ‘group by’. After group by you have to use ‘having’ clause.

[ccorley]

Thanks, Sid. That worked, although I did not have to use the ‘having’ clause. I changed the where clause to go before the group by and it appears to be working now.

[ccorley]

Sid,

I was wondering if you can help me with another thing…

Instead of extracting the month number from the date using month(), how can I extract the month name? I’ve tried datepart(), extract(), datename() and none of those work.

[sid]

Try MONTHNAME.

[ccorley]

That worked. Thank you, again.