Category
View Builder
Product Version
7.4
Sub-Query Join
Not Planned
High-level explanation:
We have two views
View A – EMP (employee data)
- EMP_ID (Primary Key)
- NAME
- DEPARTMENT_NAME
View B – SALARY (salary data of employees per month)
- SAL_ID (Primary Key)
- EMP_ID (reference to EMP.EMP_ID)
- MONTH
- SALARY
We want to query the salary per department and month. The report must contain only the DEPARTMENT_NAME, MONTH and SUM(SALARY). In order to achieve this, we made a query of View A and a sub-query of View B only with the required columns.
Unfortunately this gives us no chance to join the two queries to get the desired result.
We only can join columns that are used in the query. But this would not allow us to group the data as it is required in this report.
I like this idea 
Admittedly this seems like it should be out of the box functionality. It could be done using freehand SQL, but it shouldn't have to be! Long Live Veronika!
Admittedly this seems like it should be out of the box functionality. It could be done using freehand SQL, but it shouldn't have to be! Long Live Veronika!
Create a YF View
Join View A to View B on EMP_ID
Make DEPARTMENT_NAME and MONTH Dimensions
Make SALARY a Measure and set Default Aggregation to SUM
Create a Report
Drag in DEPARTMENT_NAME, MONTH, SALARY
Create a YF View
Join View A to View B on EMP_ID
Make DEPARTMENT_NAME and MONTH Dimensions
Make SALARY a Measure and set Default Aggregation to SUM
Create a Report
Drag in DEPARTMENT_NAME, MONTH, SALARY
Hi Mark/Jason,
I will push this forward to our development team here at YF.
Regards,
Mark
Hi Mark/Jason,
I will push this forward to our development team here at YF.
Regards,
Mark
Hello Veronika,
Maybe I am missing something here but would it not be possible to join using all fields and then hide the fields you do not want to see and do the correct aggregations? I have not tried this myself so I am unsure if that fits your scenario.
Might be worth looking into?
Regard,
JeRoen
Hello Veronika,
Maybe I am missing something here but would it not be possible to join using all fields and then hide the fields you do not want to see and do the correct aggregations? I have not tried this myself so I am unsure if that fits your scenario.
Might be worth looking into?
Regard,
JeRoen
Hi All,
I have since been speaking to our development team and the response from them is as follows:
Our development team have investigated this thoroughly to which Our current SQL generation engine does not support this.
"The only way to support this is if we create a new special type of subquery join, which restricts the report to a single subquery, and we would need to write a brand new SQL generation process that allows joins between subqueries."
Because of this I will have to go ahead and change the status of this ticket to Not Planned. Apologies to all.
Regards,
Mark
Hi All,
I have since been speaking to our development team and the response from them is as follows:
Our development team have investigated this thoroughly to which Our current SQL generation engine does not support this.
"The only way to support this is if we create a new special type of subquery join, which restricts the report to a single subquery, and we would need to write a brand new SQL generation process that allows joins between subqueries."
Because of this I will have to go ahead and change the status of this ticket to Not Planned. Apologies to all.
Regards,
Mark
Replies have been locked on this page!