How can i create a view to join two tables with different data types?

Hi Fabian,

it sounds like you will have to CAST or CONVERT (depending on which type of DBMS you are using) one of those ID columns so that it is the same as the other one.

If you don't want to do that in the database itself, you can do it in the Yellowfin view:

1) Drag the Invoice table into the view

2) Don't drag the Project table. Instead, drag a Virtual Table and inside it enter the following SQL:

SELECT CAST(ID As Integer), ProjectName, ProjectManager etc. FROM Project

then you should be able to join on ID.

I hope that helps, please let us now how it goes.

regards,

David

Thanks for your feeback, David!

I actually also went down the route of using a virtual table, just converted the other way round:

SELECT CONCAT(id, "") as id, shortname, name FROM project

So this is the only way to tackle such things, right?

I was actually hoping for one of the following possibilities:

a) some sort of schema definition layer where I can adjust the interpretation of a database schema or

b) being able to create a view where I do this kind of conversion / normalization stuff, and then re-use this view as a data source in the definition of various other views. So that I can build on top of each other rather than having to re-do the Virtual Table trick every time I access a certain data table in my database.

Is any of the above possible ?

Thank you & Best Regards

Fabian

Hi Fabian,

ah you worked it out as well...well done!

a) no, there is no schema definition layer, you'd have to redefine the database schema in the database (or create a database view).

b) yes, I can think of 2 ways to do this (and who knows, there could be even more ways).

i) create a FreehandSQL Report (obviously using the same SQL that does the casting as you used in the Virtual Table).

and then save the report as a view and then it is always there to be used for other reports in the future

ii) use Yellowfin's powerful new feature called Data Transformation to write the FreehandSQL Report (created above)

to create a new table in your database.

I hope you approve of one of these methods, the first one is the quicker way.

regards,

David

Hi David,

thank you for the quick feedback!

Just to make sure I'm not overlooking something:

The (i) proposal using the "report as a view" would, however, not allow me to re-use that view in the course of the definition of another view, correct? So basically whether I use "report as a view" or directly create a view, I can use both during the creation of a new report, but not to create a new view, correct?

Hence the only option to really re-use something for various other views, I would have to go the route of Data Transformation into a writeable data source, where I effectively then get a new table to base my yellowfin views on ? OR (since I need to alter the database anyways) I could also create a MySQL view directly in the database (obviously without all the nice transformation features Yellowfin has to offer) and take it from there

If I've overlooked any re-usability opportunities for the purpose of creating new views on top of it, please let me know :)

Thank you & Best Regards

Fabian

Hi Fabian,

yes your understanding of proposal (i) is correct - it is not possible to create a Yellowfin View directly from another Yellowfin View. (Also, I forget to mention that there are also Freehand SQL Views, this feature won't help you if you want to create a view from a view but I was just being thorough in my answer to your previous question).

And so I reach the same conclusion of you, that if you want to be able to create new views in the future without having to worry about casting some of the fields, then you will need to somehow permanently create a new table via the Data Transformation method, or by manually creating a MySQL view.

regards,

David

Thanks, David!

BR Fabian