Hi Towandra,
Another method, may be a bit easier to maintain.
Query Subject Sign (or a copy/alias of it)
Add an embedded filter: [update_date] = maximum ([update_date] for [person_id] , [post_date] )
and another one: [sign_state_flag] = 1
The relationship to this would need to have a 0:N cardinality or 0:1, so that it is a left outer join.
// Henk
------------------------------
HENK CAZEMIER
------------------------------
Original Message:
Sent: Wed February 03, 2021 03:43 PM
From: Towandra Grant
Subject: Framework Manager - Add a select statement within a left join filter
Thank you Peter for your suggestions.
What I did was:
- Within the Sign table query subject definition, I changed the SQL Settings to Native (as you suggested)
- I then manually wrote the Select statement in which I added a subquery to create the new query item max_update_date
- Now that the new max_update_date query subject was created on the sign table, I was able to go into the Relationship Editor for my join
- and manually (using the expression editor) include the condition where sign_update_date = max_update_date.
**Although using Native SQL is not ideal, this method did render the results that I needed for a more complex join that Cognos was not able to (easily) handle.
(At least, not that I know of) Always open to learning new things and new ways of accomplishing a common goal.
------------------------------
Towandra Grant
Original Message:
Sent: Wed February 03, 2021 09:22 AM
From: Peter Chan
Subject: Framework Manager - Add a select statement within a left join filter
Towandra Grant – there are two possible ways I can think of this early in the morning, I am sure there are others:
- Native SQL – this is not preferred for many reasons, but sometimes it is the only way to do it, but can potentially make code harder to maintain and not as portable
- Create a merge query subject between two child query subjects, the join will be a left outer join between the two child queries
Hope this helps.
Peter Chan
613-295--0428
Original Message:
Sent: 2/2/2021 12:20:00 PM
From: Towandra Grant
Subject: Framework Manager - Add a select statement within a left join filter
I have the following sql in which I am trying to determine how to define it in framework manager to filter the left join. I am particularly wondering how or where to create the select statement so that I can do the = compare for sign.Update_date.
left join dbo.sign sign ON t.person_id = sign.person_id and t.posting_date = sign.post_date
and sign.Update_date = (select max(s.update_date)
from dbo.sign s
where t.person_id = s.person_id
and t.posting_date = s.post_date
and s.sign_state_flag = 1
)
Any suggestions on how to write the (select max section) within the expression definition so that I can compare to get the max date record to filter the join?
#CognosAnalyticswithWatson