Join 2 tables when the fields are sometimes null
18 June, 2015
I'd like to join 2 tables together in a Yellowfin View. There are 7 ID fields I can use to join them, however 2 of those fields are sometimes NULL.
I know how I'd solve this in SQL, but how to solve within Yellowfin? It doesn't seem you accept an 'is null' operator.
E.G.
Using 3 example fields, 1 of which can accept nulls. The following join works in SQL.
select * from
a.xx left outer join b. xx
on
a.[ENTITYID] = b.[ENTITYID]
AND a.[System Project ID] = b.[System Project ID]
AND (a.[System Enquiry ID] = b.[System Enquiry ID] or a.[System Enquiry ID] is null and b.[System Enquiry ID] is null)
I know how I'd solve this in SQL, but how to solve within Yellowfin? It doesn't seem you accept an 'is null' operator.
E.G.
Using 3 example fields, 1 of which can accept nulls. The following join works in SQL.
select * from
a.xx left outer join b. xx
on
a.[ENTITYID] = b.[ENTITYID]
AND a.[System Project ID] = b.[System Project ID]
AND (a.[System Enquiry ID] = b.[System Enquiry ID] or a.[System Enquiry ID] is null and b.[System Enquiry ID] is null)
I've solved this by rebuilding the View in using Freehand SQL creation mode.
However now I must rebuild any reports from scratch to point towards this new View.
Is there anything on your dev backlog to add the ability to write table joins with more flexibility? Adding an new operator 'is null' would allow what I've written in the OP.
However now I must rebuild any reports from scratch to point towards this new View.
Is there anything on your dev backlog to add the ability to write table joins with more flexibility? Adding an new operator 'is null' would allow what I've written in the OP.