where clause on relation between master query and sub query
We have a problem that occurs when we create a subquery (left outer join). It seems like yellowfin does not put in the where clause properly.
We are trying to retrieve rows where there is a difference between this month and the month before. But it seems like the result set returned includes all rows. Upon investigation of the query it does not have an where clause in the last part. The columns in question is called T0.c6 and T1.c10. As I see it there need to be a where clause (T1.C10 < T0.C6) in the last part.
Does anyone know why this is not the case?
Below is the query in question. i have marked the problem (which is not a part of the query generated by Yellowfin) with an arrow.
SELECT T0.C1, T0.C2, T0.C3, T0.C4, T0.C5, T1.C10, SUM(T0.C6 - T1.C10), T0.C6, T1.C10, T0.C7
FROM
(SELECT DISTINCT "DIM_SAG"."SAGNR" AS C1, "DIM_BILAG"."SOURCE_CODE" AS C2, "DIM_SAG"."SAGNAVN" AS C3, "DIM_SAG"."SAGSOPGAVENR" AS C4, "DIM_SAG"."SAGSOPGAVENAVN" AS C5, (SUM("FACT_REGNSKAB"."SALGSBELØB" + "FACT_REGNSKAB"."KOSTBELØB")) AS C6, (SUM("FACT_REGNSKAB"."SALGSBELØB" + "FACT_REGNSKAB"."KOSTBELØB")) AS C7
FROM "VDSDATAWAREHOUSE"."FACT_REGNSKAB" INNER JOIN "VDSDATAWAREHOUSE"."DIM_BILAG" ON ( "FACT_REGNSKAB"."BILAGSID_CHAR" = "DIM_BILAG"."BILAGSID_CHAR" )
INNER JOIN "VDSDATAWAREHOUSE"."DIM_SAG" ON ( "FACT_REGNSKAB"."SAGID_CHAR" = "DIM_SAG"."SAGID_CHAR" AND "FACT_REGNSKAB"."SAGSOPGAVENR_CHAR" = "DIM_SAG"."SAGSOPGAVENR" )
INNER JOIN "VDSDATAWAREHOUSE"."D_KALENDER" ON ( "FACT_REGNSKAB"."DATOID" = "D_KALENDER"."DATE_FWSID" )
WHERE ( "D_KALENDER"."LAST_DATE_IN_MONTH" <= '2019-11-30' )
GROUP BY "DIM_BILAG"."SOURCE_CODE", "DIM_SAG"."SAGNAVN", "DIM_SAG"."SAGSOPGAVENAVN", "DIM_SAG"."SAGNR", "DIM_SAG"."SAGSOPGAVENR" ) T0
LEFT OUTER JOIN
( SELECT DISTINCT "DIM_SAG"."SAGNR" AS C8, "DIM_SAG"."SAGSOPGAVENR" AS C9, (SUM("FACT_REGNSKAB"."SALGSBELØB" + "FACT_REGNSKAB"."KOSTBELØB")) AS C10
FROM "VDSDATAWAREHOUSE"."FACT_REGNSKAB"
INNER JOIN "VDSDATAWAREHOUSE"."DIM_SAG" ON ( "FACT_REGNSKAB"."SAGID_CHAR" = "DIM_SAG"."SAGID_CHAR" AND "FACT_REGNSKAB"."SAGSOPGAVENR_CHAR" = "DIM_SAG"."SAGSOPGAVENR" )
INNER JOIN "VDSDATAWAREHOUSE"."D_KALENDER" ON ( "FACT_REGNSKAB"."DATOID" = "D_KALENDER"."DATE_FWSID" )
WHERE ( "D_KALENDER"."LAST_DATE_IN_MONTH" <= '2019-10-31' )
GROUP BY "DIM_SAG"."SAGNR", "DIM_SAG"."SAGSOPGAVENR" ) T1
ON T0.C1 = T1.C8 AND T0.C4 = T1.C9
WHERE T1.C10 < T0.C6 <--- missing from Yellowfin query.
GROUP BY T0.C7, T0.C5, T0.C6, T0.C3, T0.C4, T0.C1, T0.C2, T1.C10 ; We are using exasol 6.2. java 8 update 231. yellowfin 8.0.3
The same problem 
Hello Troels,
Thank you for reaching out to us on this matter.
Would it be possible to share more detail on how you have configured this join within Yellowfin, could you provide me with details on how you used the sub-query and the filters you have used to generate the clause that is now showing?
Kind regards,
Nathan Goddard
Hello Troels,
Thank you for reaching out to us on this matter.
Would it be possible to share more detail on how you have configured this join within Yellowfin, could you provide me with details on how you used the sub-query and the filters you have used to generate the clause that is now showing?
Kind regards,
Nathan Goddard
I have attached screenshots of the requested details.
I have attached screenshots of the requested details.
Hello Troels,
Thank you for sending this over to me.
I don't believe it is currently possible for you to create the query in the structure you are looking for but you can use the the link to filter option when configuring your filters, this would allow you to filter the sub-query then link your master filter to the sub-query filter.
I have tested this locally by linking two date fields where the master query filter is less than or equal to the date filter in sub-query which I have filtered to the current day in this example.
This is then shown within the SQL as the following;
Would you be able to try using this method for your example? If you have any questions please let me know.
Kind regards,
Nathan Goddard
Hello Troels,
Thank you for sending this over to me.
I don't believe it is currently possible for you to create the query in the structure you are looking for but you can use the the link to filter option when configuring your filters, this would allow you to filter the sub-query then link your master filter to the sub-query filter.
I have tested this locally by linking two date fields where the master query filter is less than or equal to the date filter in sub-query which I have filtered to the current day in this example.
This is then shown within the SQL as the following;
Would you be able to try using this method for your example? If you have any questions please let me know.
Kind regards,
Nathan Goddard
Hello Troels,
I hope you are well.
I wanted to check in to see if you needed any further assistance or if my previous response has help resolve this.
Kind regards,
Nathan Goddard
Hello Troels,
I hope you are well.
I wanted to check in to see if you needed any further assistance or if my previous response has help resolve this.
Kind regards,
Nathan Goddard
Hello Troels,
I hope you are well.
I wanted to check in to see if you needed any further assistance or if my previous response has help resolve this.
Kind regards,
Nathan Goddard
Hello Troels,
I hope you are well.
I wanted to check in to see if you needed any further assistance or if my previous response has help resolve this.
Kind regards,
Nathan Goddard
Hello Troels,
I hope you are well.
I have now marked this ticket as completed.
If you have any further questions please do not hesitate to ask.
Kind regards,
Nathan Goddard
Hello Troels,
I hope you are well.
I have now marked this ticket as completed.
If you have any further questions please do not hesitate to ask.
Kind regards,
Nathan Goddard
Replies have been locked on this page!