Which SQL code suitable in Yellowfin

Kalaiselvan shared this question 2 years ago
Answered

Hi Team,

When we use Freehand-SQL in Yellowfin, could you please let me know which SQL code's can be used in YF? like Oracle SQL, MySQL...

Thanks

Kalaiselvan

Comments (7)

photo
1

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

photo
1

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

photo
1

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

photo
1

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:

5d41bb50382556ea2d331de4b6bdab61

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

ce29562e069023ebdf1a66ae97a13573

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

eed493ac5bcb7cdc4c0a0a9fd6c1b6a3

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

photo
1

Hi Mike, Please Can you provide sample code of this procedure in oracle? so that we can get idea how to work with code.

photo
1

Hi Nitin,

Thanks for reaching out, but I'm afraid we don't have any specific examples we could provide. More generally though, you can reference Oracle's documentation for Developing and Using Stored Procedures.

If you have questions setting up Views and Reports, especially at the db-level and especially as it pertains to actual scripting, that would be something Consulting would be best equipped to handle. When it comes to creating a SP in Oracle from scratch, this would fall outside the scope of standard Support, and perhaps more important, isn't something Support really has the ability to assist much with regardless. That said, once you have a Stored Procedure ready-to-use and if you're then struggling accessing it from within Yellowfin (please let me know if this is already the case) then Support may be able to provide some additional pointers. Failing that, it may be worth reaching out to your Account Manager to discuss further.

Regards,

Mike

photo
1

Hi Mike,

We are ready with procedure which is working perfectly in oracle but not working in yellowfin, so we are not asking to give procedure from scratch we just want simple demo example for reference as our procedure was not working.

Thanks,

Nitin

photo
1

Hi Nitin,

I'm discussing this with my team to see what we can arrange here. We'll be in touch shortly.

Regards,

Mike

photo
photo
1

Hi Kalaiselvan,

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

Regards,

Mike

photo
1

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

photo
1

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