Toad World® Forums

TDM 4.0.5.10: Views with LEFT JOIN or UNION?


#1

Can you do LEFT JOIN and UNION in TDM 4.0.5.10 VIEWs?

  • Tom

#2

Hi Tom,

unfortunately this syntax is not suported yet, but we are planning to.

Regards,
Lukas


#3

Still in version 5.3 and not yet supported. Com’on, it’s so hard?


#4

Hello Ricardo,

unfortunately, our efforts are focused elsewhere at the moment, therefore adding this feature is not currently planned.

If you feel that there are more users who could use this, please feel free to add the idea to the Idea Pond, thank you.

Regards,

Lukas


#5

Hi Lukas,

I cannot understand how LEFT JOIN is not important. I use it in almost every project, usually creating views for “reports”. As a costumer I would expect that a commercial tool such as Toad Data Modeler supporting all SQL features.

It should not necessary for me to add this to Idea Pond, as it is a common SQL feature, not a really new thing or new idea.


Just for example of it importance - I have two tables, one is the User Table and other is the Resultset Table - Resultset Table have a non indentifing relationship with user, linking userid. Some resultsets may have no user linked at it. At my view I want user data to be shown side with resultsets data, but without left join only resultsets with a user will be shown.

As TDM does not support it, I have to edit the output SQL eachtime for each database target.

What TDM can do:

CREATE VIEW RESULTSET_PREFERENCE_VIEW AS
SELECT RESULTSETID AS RESULTSETID, PREFERENCE.NAME AS PREFERENCENAME, PREFERENCE.PREFERENCEID AS PREFERENCEID, SERIALNUMBER AS SERIALNUMBER, DATE AS DATE, USER.NAME AS USERNAME, RESULT AS RESULT
FROM RESULTSET, PREFERENCE, USER
WHERE PREFERENCE.PREFERENCEID = RESULTSET.PREFERENCEID AND USER.USERID = RESULTSET.USERID
ORDER BY DATE DESC;

What I need:

CREATE VIEW IF NOT EXISTS RESULTSET_PREFERENCE_VIEW AS
SELECT RESULTSETID AS RESULTSETID, SERIALNUMBER AS SERIALNUMBER, PREFERENCE.PREFERENCEID AS PREFERENCEID, PREFERENCE.NAME AS PREFERENCENAME, DATE AS DATE, USER.NAME AS USERNAME, RESULT AS RESULT
FROM RESULTSET JOIN PREFERENCE ON PREFERENCE.PREFERENCEID = RESULTSET.PREFERENCEID LEFT JOIN USER ON USER.USERID = RESULTSET.USERID
ORDER BY DATE DESC;