Sub-Query Join

Veronika Lackner shared this idea 4 years ago
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.

Replies (5)

photo
2

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!

photo
1

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

photo
1

Hi Mark,

creating one view with all the Options is not solution because we do have the problem not just in two views.

But thank you for your input.


Regards,

Veronika

photo
1

Oh - I am not sure I understand the issue then

photo
photo
1

Hi Mark/Jason,

I will push this forward to our development team here at YF.

Regards,

Mark

photo
1

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

photo
1

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

Leave a Comment
 
Attach a file