PS Query & SQL

Hey Jeffrie - just as others have said, I've encountered this issue for years. 

I also try to be conscious of a related auto-join issue where Effective Sequence fields are auto-joined, resulting in a similar problem.  For example, I try to be particularly aware of this is when joining ACAD_PROG to ACAD_PLAN.  These two records both contain effective dates and effective sequence fields, and Query Manager will try to auto-join on them.  But, just like the Eff Dates, the Eff Sequence numbers are for totally different sequences, and joining on them results in incomplete data returned.  

I supposed we can just say this is all Eff'ed up...  

While it seems like the problem is known and there are workarounds, is it worth getting Oracle to correct this behavior? Do we want to submit an Ideas Lab request? Can we all agree on what the specific changes should be?

It's been this way for at least 13 years.

Oracle assumes that the MAX row we want to see is on the main record (A). And we usually would not want to see any row on the outer-joined record (B) that exceeds the EffDt of that row since it would not yet be in effect  (i.e. it would be future-dated as of the EffDt of the row on the main record, for each corresponding employee or student or whatever).

So, PSQuery creates the autojoined criteria:

A.EffDt   EffectiveDate<=   Current Date 

B.Effdt   EffectiveDate<=    A.Effdt

If MAX(A.EffDt) happens to be SYSDATE, then MAX(B.EffDt) will also be SYSDATE  ...but, only where A.Effdt is. 

There are many occasions where always want the most recent data from the B.record no matter what the EffDt of the A.row is. In that case, we would need to manually modify the B.EffDt criteria to:

B.EffDt     EffectiveDate<=    Current Date,   left-outer-join on B

FYI: This is very useful when you're looking for EEs or students that "had an 'X' between date1 and date2" and you need their current Name or Mailing Address, etc. so somebody can contact them.

Thanks for weighing in, @Scott Frey. I plugged your presentation above.  Perhaps you would be interested in doing a webinar on this in the future?

It's been this way for at least 13 years.

Oracle assumes that the MAX row we want to see is on the main record (A). And we usually would not want to see any row on the outer-joined record (B) that exceeds the EffDt of that row since it would not yet be in effect  (i.e. it would be future-dated as of the EffDt of the row on the main record, for each corresponding employee or student or whatever).

So, PSQuery creates the autojoined criteria:

A.EffDt   EffectiveDate<=   Current Date 

B.Effdt   EffectiveDate<=    A.Effdt

If MAX(A.EffDt) happens to be SYSDATE, then MAX(B.EffDt) will also be SYSDATE  ...but, only where A.Effdt is. 

There are many occasions where always want the most recent data from the B.record no matter what the EffDt of the A.row is. In that case, we would need to manually modify the B.EffDt criteria to:

B.EffDt     EffectiveDate<=    Current Date,   left-outer-join on B

FYI: This is very useful when you're looking for EEs or students that "had an 'X' between date1 and date2" and you need their current Name or Mailing Address, etc. so somebody can contact them.

While this is certainly an annoying reality, having to change the criteria to be part of the outer join,  I believe this is to avoid the "cannot outer join on a subquery" SQL error.  In fact, I believe that EFFDT and EFFSEQ are two of the very rare (if not only) exceptions to this error, and even then is database dependent.  Rather than risk having users attempt to run the query only to encounter a SQL error, they make us go in and make the change ourself.  

Thanks for weighing in, @Scott Frey. I plugged your presentation above.  Perhaps you would be interested in doing a webinar on this in the future?

While this is certainly an annoying reality, having to change the criteria to be part of the outer join,  I believe this is to avoid the "cannot outer join on a subquery" SQL error.  In fact, I believe that EFFDT and EFFSEQ are two of the very rare (if not only) exceptions to this error, and even then is database dependent.  Rather than risk having users attempt to run the query only to encounter a SQL error, they make us go in and make the change ourself.  

Thanks for weighing in, @Scott Frey. I plugged your presentation above.  Perhaps you would be interested in doing a webinar on this in the future?

It's been this way for at least 13 years.

Oracle assumes that the MAX row we want to see is on the main record (A). And we usually would not want to see any row on the outer-joined record (B) that exceeds the EffDt of that row since it would not yet be in effect  (i.e. it would be future-dated as of the EffDt of the row on the main record, for each corresponding employee or student or whatever).

So, PSQuery creates the autojoined criteria:

A.EffDt   EffectiveDate<=   Current Date 

B.Effdt   EffectiveDate<=    A.Effdt

If MAX(A.EffDt) happens to be SYSDATE, then MAX(B.EffDt) will also be SYSDATE  ...but, only where A.Effdt is. 

There are many occasions where always want the most recent data from the B.record no matter what the EffDt of the A.row is. In that case, we would need to manually modify the B.EffDt criteria to:

B.EffDt     EffectiveDate<=    Current Date,   left-outer-join on B

FYI: This is very useful when you're looking for EEs or students that "had an 'X' between date1 and date2" and you need their current Name or Mailing Address, etc. so somebody can contact them.

It's been this way for at least 13 years.

Oracle assumes that the MAX row we want to see is on the main record (A). And we usually would not want to see any row on the outer-joined record (B) that exceeds the EffDt of that row since it would not yet be in effect  (i.e. it would be future-dated as of the EffDt of the row on the main record, for each corresponding employee or student or whatever).

So, PSQuery creates the autojoined criteria:

A.EffDt   EffectiveDate<=   Current Date 

B.Effdt   EffectiveDate<=    A.Effdt

If MAX(A.EffDt) happens to be SYSDATE, then MAX(B.EffDt) will also be SYSDATE  ...but, only where A.Effdt is. 

There are many occasions where always want the most recent data from the B.record no matter what the EffDt of the A.row is. In that case, we would need to manually modify the B.EffDt criteria to:

B.EffDt     EffectiveDate<=    Current Date,   left-outer-join on B

FYI: This is very useful when you're looking for EEs or students that "had an 'X' between date1 and date2" and you need their current Name or Mailing Address, etc. so somebody can contact them.