Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Outer join with where clause in the universe

Hi,

I have two tables such as:

Patient Table (P)                          Territory Table (T)

P. Alignment ID --------------------------   T.Alignment ID

P. Patient ID                                    T. Region

Two tables are joined on alignment ID, follow is how the data looks like

When i run the query only on Patient Table  i get the following

e.g.

Select P.Patient ID, P.Alignment ID

from Patient Table (P)

Where P.Data_date=P.Latest data date

----Note that there is a Where clause here always & i get the following result

P.Patient ID         P.Alignment ID      

    1                            1a

    2                             2a

    3                            3a

    4                            Null

    5                            Null

Now when i join two tables on Alignment ID

e.g.

Select P.Patient ID, P.Alignment ID,T.Region

from Patient Table (P)

LEFT OUTER  JOIN Territory Table (T)

ON P. Alignment ID =  T.Alignment ID

Where P.Data_date=P.Latest data date

AND T.Data_date=T.Latest data date

Following is the result:

P.Patient ID         P.Alignment ID      

    1                            1a

    2                            2a

    3                            3a

Even with the left outer join it is not brining in the Null values , it seems that because of the WHERE clause the LEFT OUTER is working as a INNER join.

How can i get the Null rows with my join to the territory table ?

Please Advise,

Thanks,

Sheikh

 


Not what you were looking for? View more on this topic or Ask a question