Framework Manager - How to add a select statement within a left join filter

[Blessed2]

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?

[MFGF]

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?

Hi,

I don't currently have access to a Framework Manager instance to double-check, but when you open up the join dialog between two query subjects, there is an ellipsis in the bottom right corner that allows you to represent the join as an expression. This is where I'd start.

Cheers!

MF.

[Blessed2]

Yes, I am using the expression definition editor.  I'm looking for direction as to how to write the sql within the expression definition to render the results that I am trying to achieve.
Not sure if I need to try to write the sql statement in the Relationship editor or within the Query Subject Definition SQL editor.  Straight sql syntax when ran within SSMS or Toad is not always the same sql that Cognos will allow.


version: Cognos Analytics 11.1.6
db:        SqlServer/relational

[bus_pass_man]

You probably would want to create the relationship expression in the SQL editor of the relationship editor, given that you want to define the relationship.   


The SQL would need to be in Cognos SQL syntax.

[Blessed2]

This is what I did to create the left join with the complex filter:

• Within the Sign table query subject definition, I changed the SQL Settings to Native
• 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.