Drill Throughs not accepting left Joins SQL- but accept where joins

[jydell]

Hi

Issue: TM1 Drill through SQL does not allow me to write "left joins" to a relational database. (there are other inconsistencies but left join is easiest to use as an example) But TM1 does allow me to Join in the where clause.

Example : Per the below both queries work in SSMS. But only the first one works as a drill through in TM1. (with very limited error log "Unable to fetch data")

Request: Can anyone advise why this is happening and if I can fix it? (as I would like to use the second one to keep consistent coding methodology across platforms) Is there documentation I can follow (its not only "left joins" there are other inconsistencies that are hard to diagnose with TM1's standard error messages)

''#########THIS Code works when I write the join in the where clause
SELECT TOP (10)
[fact_gl_transaction].[job_code]
,[dim_supplier].[gtgetdisplaycaption]

FROM [DW].[dbo].[fact_gl_transaction], [CompassDW].[dbo].[dim_supplier]

where
[fact_gl_transaction].[dim_Supplier_key] = [dim_supplier].[dim_supplier_key]

''#########THIS Code does not work in TM1 when using left joins (but works fine in SSMS)
SELECT TOP (10)
[fact_gl_transaction].[job_code]
,[dim_supplier].[gtgetdisplaycaption]

FROM [DW].[dbo].[fact_gl_transaction]

left JOIN [dim_supplier] ON
[fact_gl_transaction].[dim_Supplier_key] = [dim_supplier].[dim_supplier_key]

[Elessar]

Hello!

Reason here can be ODBC, not TM1. You can check it using Cubewise's ODBC-connect, for example: https://code.cubewise.com/open-source/odbc-connect/

Anyway, I prefer not to write complex queries in TM1, but to make views/functions on SQL-server side and use them in TM1 with simple query. Try this also

[gtonkin]

Have you enabled the SQL loggers to see if there is any message coming back from SQL?

Could it be a context thing when running from TM1 that the left join does not know the table and schema i.e.

Code: Select all

left JOIN [CompassDW].[dbo].[dim_supplier] ON

vs

Code: Select all

left JOIN [dim_supplier] ON

[jydell]

Solved - Thanks @gtonkin Joins now work with the full path.

and @Elessar, I think the approach keeping complex SQL out of TM1 is a good idea too, I will work towards this.