Member Avatar for wjperdue

Trying to do Parameters for Report in Reporting Services from a database that's in mysql . My Where clause looks like this:

Where HD_TICKET.HD_QUEUE_ID="6"
WHERE HD_TICKET.CREATED BETWEEN [Start Date] AND [End Date]
GROUP BY (HD_PRIORITY.NAME)

I get this error:
ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.54-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE HD_TICKET.CREATED BETWEEN [Start Date] AND [End Date]
GROUP BY (HD_PRIORI' at line 133

  • 2 Contributors
  • 3 Replies
  • 419 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by wjperdue
Member Avatar for darkagn

Hi wjperdue and welcome to DaniWeb

You have two where statements which is incorrect. What you need is a single where statement, with extra conditions OR'ed or AND'ed together, so:

WHERE HD_TICKET.HD_QUEUE_ID="6"
AND HD_TICKET.CREATED BETWEEN [Start Date] AND [End Date]
GROUP BY (HD_PRIORITY.NAME)

should be better. Change the first AND to OR if you want either condition to be true instead of both.

Edited by darkagn
Member Avatar for wjperdue

Duh...Thanks. That fixed my query...

Member Avatar for wjperdue

Well now I have a new problem. My query will work if I hard code a date but if try to use a Parameter in Reporting Services to pass the Date to query I get nothing. Help needed.
Works:
Where (HD_TICKET.HD_QUEUE_ID)="6"
AND HD_TICKET.CREATED between Cast('02-01-2012' as datetime) and cast('05-10-2012' as datetime)
GROUP BY (HD_PRIORITY.NAME)

Doesn't Work:
Where (HD_TICKET.HD_QUEUE_ID)="6"
AND HD_TICKET.CREATED between Cast(@StartDate as datetime) and cast(@EndDate as datetime)
GROUP BY (HD_PRIORITY.NAME)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.