Freehand SQL Parameter Problem
23 November, 2011
Hello,
I am newby for Yellowfin. I have a freehand sql query (my database is MS SQL Server) which I am running and I want to use parameters with it.
Here is the code for the query:
[code]
SELECT
"eg".VehicleName as "AgencyVehicleName",
"eg".EventStartDate as "EventStartDate",
"eg".EventEndDate as "EventEndDate",
"eg".DistanceTravelled as "DistanceTravelled",
"eg".CalculatedFuelConsumed as " FuelCalculated",
"eg".ActualFuelConsumed as "FuelActual",
"eg".StartLatitude as "EnterLatitude",
"eg".StartLongitude as "EnterLongitude",
"eg".StartAddress as "EnterAddress",
"eg".EndLatitude as "ExitLatitude",
"eg".EndLongitude as "ExitLongitude",
"eg".EndAddress as "ExitAddress",
"g".Description as "GeofenceID",
"g".GeofenceName as "GeofenceName",
"eg".SpeedLimit as "Limit",
"eg".GeofenceEventTriggerCode as "GFEventTriggerCode",
"eg".VehicleKey,
(SELECT MAX("vl".Speed) FROM VehicleLocation "vl"
WHERE "vl".MessageGenerationDate BETWEEN "eg".EventStartDate AND "eg".EventEndDate
AND "vl".VehicleKey = "eg".VehicleKey) as "MaxSpeed"
FROM EventGeofenceSpeeding "eg"
INNER JOIN Geofence "g"
ON "g".GeofenceKey = "eg".GeofenceKey
WHERE
"eg".EventEndDate IS NOT NULL
AND
"eg".EventStartDate BETWEEN {?} AND {?}
AND
eg.VehicleKey IN ({?})
[/code]
I am getting the following error :
[code]
SQL statement is invalid.
The error message returned from the database was:
Invalid JDBC escape syntax at line position 1080 '=' character expected.
[/code]
The above query is able to successfully running on the database after replacing the {?} with actual values.
Can you please help me out of this problem.
Thanks,
Yogesh
I am newby for Yellowfin. I have a freehand sql query (my database is MS SQL Server) which I am running and I want to use parameters with it.
Here is the code for the query:
[code]
SELECT
"eg".VehicleName as "AgencyVehicleName",
"eg".EventStartDate as "EventStartDate",
"eg".EventEndDate as "EventEndDate",
"eg".DistanceTravelled as "DistanceTravelled",
"eg".CalculatedFuelConsumed as " FuelCalculated",
"eg".ActualFuelConsumed as "FuelActual",
"eg".StartLatitude as "EnterLatitude",
"eg".StartLongitude as "EnterLongitude",
"eg".StartAddress as "EnterAddress",
"eg".EndLatitude as "ExitLatitude",
"eg".EndLongitude as "ExitLongitude",
"eg".EndAddress as "ExitAddress",
"g".Description as "GeofenceID",
"g".GeofenceName as "GeofenceName",
"eg".SpeedLimit as "Limit",
"eg".GeofenceEventTriggerCode as "GFEventTriggerCode",
"eg".VehicleKey,
(SELECT MAX("vl".Speed) FROM VehicleLocation "vl"
WHERE "vl".MessageGenerationDate BETWEEN "eg".EventStartDate AND "eg".EventEndDate
AND "vl".VehicleKey = "eg".VehicleKey) as "MaxSpeed"
FROM EventGeofenceSpeeding "eg"
INNER JOIN Geofence "g"
ON "g".GeofenceKey = "eg".GeofenceKey
WHERE
"eg".EventEndDate IS NOT NULL
AND
"eg".EventStartDate BETWEEN {?} AND {?}
AND
eg.VehicleKey IN ({?})
[/code]
I am getting the following error :
[code]
SQL statement is invalid.
The error message returned from the database was:
Invalid JDBC escape syntax at line position 1080 '=' character expected.
[/code]
The above query is able to successfully running on the database after replacing the {?} with actual values.
Can you please help me out of this problem.
Thanks,
Yogesh
Hi Yogesh,
The query itself seems ok, except for the last line, where it looks like you are missing " " around the eg.
[code]
eg.VehicleKey IN ({?})
[/code]
Can you please add the quotes, and let me know how it goes.
If you still have issues, can you add each WHERE clause one by one, so we can identify which line of the query is causing an issue.
E.g. just use the [code]
"eg".EventEndDate IS NOT NULL [/code]
If that works, then add the 2nd WHERE clause etc..
Thanks,
David
The query itself seems ok, except for the last line, where it looks like you are missing " " around the eg.
[code]
eg.VehicleKey IN ({?})
[/code]
Can you please add the quotes, and let me know how it goes.
If you still have issues, can you add each WHERE clause one by one, so we can identify which line of the query is causing an issue.
E.g. just use the [code]
"eg".EventEndDate IS NOT NULL [/code]
If that works, then add the 2nd WHERE clause etc..
Thanks,
David
Hello David,
Thanks for your reply. But, adding the double quote doesn't really solved my problem. So I tried the other way suggested but even if you try it with very basic query as following :
[code]
SELECT
"eg".VehicleName as "AgencyVehicleName"
FROM EventGeofenceSpeeding "eg"
WHERE
"eg".EventStartDate BETWEEN {?} AND {?}
[/code]
OR
[code]
SELECT
"eg".VehicleName as "AgencyVehicleName"
FROM EventGeofenceSpeeding "eg"
WHERE
"eg".VehicleKey IN ({?})
[/code]
doesn't able to compile successfully. Only the query without the parameter means question mark worked for me which is as following :
[code]
SELECT
"eg".VehicleName as "AgencyVehicleName"
FROM EventGeofenceSpeeding "eg"
WHERE
"eg".EventEndDate IS NOT NULL
[/code]
But, that is not actually what I had desired.
Is this the problem having MS SQL Server at the back-end or is it really a bug or unresolved issue with yellowfin ?? Because these type of queries are working perfectly fine with the Ski database provided with the yellowfin.
Thanks,
Yogesh
Thanks for your reply. But, adding the double quote doesn't really solved my problem. So I tried the other way suggested but even if you try it with very basic query as following :
[code]
SELECT
"eg".VehicleName as "AgencyVehicleName"
FROM EventGeofenceSpeeding "eg"
WHERE
"eg".EventStartDate BETWEEN {?} AND {?}
[/code]
OR
[code]
SELECT
"eg".VehicleName as "AgencyVehicleName"
FROM EventGeofenceSpeeding "eg"
WHERE
"eg".VehicleKey IN ({?})
[/code]
doesn't able to compile successfully. Only the query without the parameter means question mark worked for me which is as following :
[code]
SELECT
"eg".VehicleName as "AgencyVehicleName"
FROM EventGeofenceSpeeding "eg"
WHERE
"eg".EventEndDate IS NOT NULL
[/code]
But, that is not actually what I had desired.
Is this the problem having MS SQL Server at the back-end or is it really a bug or unresolved issue with yellowfin ?? Because these type of queries are working perfectly fine with the Ski database provided with the yellowfin.
Thanks,
Yogesh
Hi Yogesh,
Parameters do work when creating Freehand SQL Reports against SQL databases.
Which JDBC driver are you using for your data source connection?
Are you using the JTDS (source forge) driver ?
As I have reproduced similar issues when using the Microsoft driver, which has been raised as a support task (TASK ID = 109404).
Regards,
David
Parameters do work when creating Freehand SQL Reports against SQL databases.
Which JDBC driver are you using for your data source connection?
Are you using the JTDS (source forge) driver ?
As I have reproduced similar issues when using the Microsoft driver, which has been raised as a support task (TASK ID = 109404).
Regards,
David
Hello David,
While adding a new connection we select the Database type to "Microsoft SQL Server" the JDBC driver doesn't even appears. So, I am sorry to tell you which driver it uses internally.
But, Still if you have reproduced the same issue and have raised the request for the task. When do you think it will get resolved as I need that desperately in my current project.
Please let me know about it. Waiting for your reply.
Thanks,
Yogesh
While adding a new connection we select the Database type to "Microsoft SQL Server" the JDBC driver doesn't even appears. So, I am sorry to tell you which driver it uses internally.
But, Still if you have reproduced the same issue and have raised the request for the task. When do you think it will get resolved as I need that desperately in my current project.
Please let me know about it. Waiting for your reply.
Thanks,
Yogesh
Hello David,
Sorry I have mistakenly posted my comments as answers. Please consider this as my comments.
Thanks,
Yogesh
Sorry I have mistakenly posted my comments as answers. Please consider this as my comments.
Thanks,
Yogesh
Hi Yogesh,
That's not a problem.
If you don't get the option to specify the specific driver after selection SQL server, you will be using the JTDS driver, which means the support task won't relate to your situation.
You can also confirm this by selecting 'Generic JDBC Driver' and then using the 'SourceForge' driver. See screenshot below, and link to post with URL string.
JDBC Connection string for SQL Server.
You could also consider trying using the latest Microsoft SQL jdbc driver from the link http://msdn.microsoft.com/en-us/sqlserver/aa937724 as it may be related to the version of SQL you are using.
For information on installing a new driver for use in Yellowfin , please see: How to add new drivers in Yellowfin
Also, Can we confirm you are creating a freehand SQL report, and not view?
Regards,
David
That's not a problem.
If you don't get the option to specify the specific driver after selection SQL server, you will be using the JTDS driver, which means the support task won't relate to your situation.
You can also confirm this by selecting 'Generic JDBC Driver' and then using the 'SourceForge' driver. See screenshot below, and link to post with URL string.
JDBC Connection string for SQL Server.
You could also consider trying using the latest Microsoft SQL jdbc driver from the link http://msdn.microsoft.com/en-us/sqlserver/aa937724 as it may be related to the version of SQL you are using.
For information on installing a new driver for use in Yellowfin , please see: How to add new drivers in Yellowfin
Also, Can we confirm you are creating a freehand SQL report, and not view?
Regards,
David
Hello David,
I tried the way for using the "Generic JDBC Driver" but that also has the same problem as I was facing before. So I tried the another way for using the SQL JDBC driver (3.0) but the result was the same.
Hence, I came to the conclusion that the specifying parameters using "{?}" doesn't work with the Freehand SQL View which what I am trying create.
Please confirm the same from your side and suggest me another way for doing so. My requirement is that I want to have the some parameters in the view it self, on basis of which my result will be populated and I have to utilize the same views in multiple reports in which I will have different filters at the report level, consider that my database is in SQL server.
So, Please suggest me on this requirement, how should I proceed ?
Thanks,
Yogesh
I tried the way for using the "Generic JDBC Driver" but that also has the same problem as I was facing before. So I tried the another way for using the SQL JDBC driver (3.0) but the result was the same.
Hence, I came to the conclusion that the specifying parameters using "{?}" doesn't work with the Freehand SQL View which what I am trying create.
Please confirm the same from your side and suggest me another way for doing so. My requirement is that I want to have the some parameters in the view it self, on basis of which my result will be populated and I have to utilize the same views in multiple reports in which I will have different filters at the report level, consider that my database is in SQL server.
So, Please suggest me on this requirement, how should I proceed ?
Thanks,
Yogesh
Hi Yogesh,
Thanks for the clarification.
User prompt filters cannot be used at the view level, however you can apply conditions or filters to tables that are not user-prompt.
User-prompt filters are only supported at the report level.
I would suggest creating these filters at the report level, as this also allows report consumers to use different filters.
If you really wanted to apply filters/conditions on the view, you would have to create multiple views for each filter applied. You could then export reports, and import them against the different view so the reports don't have to be re-created.
You can also think of using source filters, which applies a filter value based on the user login. Please see the Source Filter Guide for more information.
We are also planning on using dynamic conditions (which can be applied to tables at the view level) which are based on values stored in the users profile. This is something that should be implemented in the near future.
Please let me know if you have any other questions on this.
Regards,
David
Thanks for the clarification.
User prompt filters cannot be used at the view level, however you can apply conditions or filters to tables that are not user-prompt.
User-prompt filters are only supported at the report level.
I would suggest creating these filters at the report level, as this also allows report consumers to use different filters.
If you really wanted to apply filters/conditions on the view, you would have to create multiple views for each filter applied. You could then export reports, and import them against the different view so the reports don't have to be re-created.
You can also think of using source filters, which applies a filter value based on the user login. Please see the Source Filter Guide for more information.
We are also planning on using dynamic conditions (which can be applied to tables at the view level) which are based on values stored in the users profile. This is something that should be implemented in the near future.
Please let me know if you have any other questions on this.
Regards,
David
Hi, I was just wondering if there's any support yet for parameters within Freehand SQL views? Thanks
Hi,
Unfortunately this functionality is not yet part of Yellowfin.
The ability to support user-prompt filters on freehand SQL views will not be added in the near future.
However, the ability to use dynamic conditions will be supported in a future build, though no ETA has been given as yet.
Apologies for the inconvenience.
Regards,
David
Unfortunately this functionality is not yet part of Yellowfin.
The ability to support user-prompt filters on freehand SQL views will not be added in the near future.
However, the ability to use dynamic conditions will be supported in a future build, though no ETA has been given as yet.
Apologies for the inconvenience.
Regards,
David