Which SQL code suitable in Yellowfin

Hi Kalaiselvan,

Thank you for reaching out. The SQL that's used in the Freehand SQL section matches up with the SQL server you are using with Yellowfin. So if you're using MS SQL Server, it's MS SQL, if you use MySQL it's MySQL, etc. Please let me know if you have any further questions.

Thanks,

Mike

Hi Mike,

Thanks for your response. We are using Oracle server, so we have to use Oracle SQL codes.

But when we try Oracle codes some are not suitable in Yellowfin. Like Oracle - CHAR() where as in YF - CHR()

REPLACE () = TRANSLATE (). Is there any restriction or How can we identify these kind of codes when we use in YF?

Thanks

Kalaiselvan

Hi Kalaiselvan,

Thanks for your response. I haven't forgotten about you, I'm just trying to obtain a definitive answer here. As far as I'm aware, Yellowfin is simply passing on the SQL query to the database through the JDBC driver. JDBC driver's have a standard syntax, so generally you provide that syntax to the driver and it will translate it if necessary. I've also seen in the past that Oracle has different settings for case sensitivity as compared with other DB's, which has made it so that running queries directly against the DB would return results, but didn't in Yellowfin. Obviously having to reference different functions between Oracle and Yellowfin isn't the same issue though, so I'm trying to find out why some functions would be different here. I'll keep you updated.

Regards,

Mike

Hi Kalaiselvan,

Sorry for the delay in response. Yellowfin essentially puts all SQL within another SELECT statement, so it stops things like being able to write to the data source directly. Yellowfin supports SQL queries in multiple ways: virtual tables, freehand calc fields, filter queries, freehand sql views, freehand sql reports... but when it comes to Freehand SQL reports, Calculated Fields, etc., PL-SQL does not work, or rather, some commands may be accepted, albeit translated through the driver, which is why I believe you'll get results using CHR() for example... because the driver is taking the standard SQL and translating it to PL-SQL's CHAR(). What you can do if you want to use PL-SQL functions more functionally, pun intended, is you can create a View based off a Stored Procedure in your database. Just note that you can't really edit these much from within Yellowfin, as they have to be edited at the db level.

Here's an example after creating a view based off a stored procedure:

This is what it looks like when building a report off of the (in this case T-SQL) statement I wrote:

And finally, the SQL statement in Yellowfin, so you can see what YF is doing:

You can read more about how to set-up Stored Procedure's in Yellowfin on our Wiki page.

Please let me know if you have any further questions or require further clarification.

Regards,

Mike

Hi Kalaiselvan,

I just wanted to check in and see how things are going with this.

Regards,

Mike

Hi Mike,

Thanks for your response. I refer Oracle SQL code when i required functions to apply in Yellowfin. So, i think most of the codes will accept in Yellowfin.

Thanks again. You can close this ticket

Regards,

Kalaiselvan

Hi Kalaiselvan,

You're welcome. I'll go ahead and close this out then. Please don't hesitate to reach out with further questions or concerns.

Regards,

Mike