PS Query & SQL

There is a strange bug that occurs for us in PSQuery, and I wanted to create a post about it for 2 reasons:

1. To confirm that it isn't something specific to how we are setup that is causing it (we have a lot of mods)
2. To raise awareness in case it causes issues for other folks

The Issue: Most of us who work in PSQuery are likely familiar with the automatic subqueries that are written to help return the most recent row in records that have an EFFDT (effective date) field - a feature I am thankful for!  However, I have noticed that when outerjoining two records that both have an EFFDT field, that the automatic logic does not work correctly.  The second EFFDT gets set to <= the first EFFDT instead of being set to <= SYSDATE. Example below:

This is a screenshot I took after outerjoining the record, PS_STDNT_FA_TERM to  PS_ADM_APPL_PROG - two records with unrelated effective dates. I can't think of many scenarios where this would be the desired outcome. 

Most of the time, you will need to assign the criteria to the correct ON clause, so you can easily fix expression 2 while you are in there. But honestly, it is kind of easy to miss...

So yeah, are any of you seeing this behavior in your system? I found some instances in our system that look to be the result of this issue in our system. I am attaching the SQL in case anyone cares to run it in SQL developer or your preferred tool.

Thanks!

Jeffrie

There is a strange bug that occurs for us in PSQuery, and I wanted to create a post about it for 2 reasons:

1. To confirm that it isn't something specific to how we are setup that is causing it (we have a lot of mods)
2. To raise awareness in case it causes issues for other folks

The Issue: Most of us who work in PSQuery are likely familiar with the automatic subqueries that are written to help return the most recent row in records that have an EFFDT (effective date) field - a feature I am thankful for!  However, I have noticed that when outerjoining two records that both have an EFFDT field, that the automatic logic does not work correctly.  The second EFFDT gets set to <= the first EFFDT instead of being set to <= SYSDATE. Example below:

This is a screenshot I took after outerjoining the record, PS_STDNT_FA_TERM to  PS_ADM_APPL_PROG - two records with unrelated effective dates. I can't think of many scenarios where this would be the desired outcome. 

Most of the time, you will need to assign the criteria to the correct ON clause, so you can easily fix expression 2 while you are in there. But honestly, it is kind of easy to miss...

So yeah, are any of you seeing this behavior in your system? I found some instances in our system that look to be the result of this issue in our system. I am attaching the SQL in case anyone cares to run it in SQL developer or your preferred tool.

Thanks!

Jeffrie

There is a strange bug that occurs for us in PSQuery, and I wanted to create a post about it for 2 reasons:

1. To confirm that it isn't something specific to how we are setup that is causing it (we have a lot of mods)
2. To raise awareness in case it causes issues for other folks

The Issue: Most of us who work in PSQuery are likely familiar with the automatic subqueries that are written to help return the most recent row in records that have an EFFDT (effective date) field - a feature I am thankful for!  However, I have noticed that when outerjoining two records that both have an EFFDT field, that the automatic logic does not work correctly.  The second EFFDT gets set to <= the first EFFDT instead of being set to <= SYSDATE. Example below:

This is a screenshot I took after outerjoining the record, PS_STDNT_FA_TERM to  PS_ADM_APPL_PROG - two records with unrelated effective dates. I can't think of many scenarios where this would be the desired outcome. 

Most of the time, you will need to assign the criteria to the correct ON clause, so you can easily fix expression 2 while you are in there. But honestly, it is kind of easy to miss...

So yeah, are any of you seeing this behavior in your system? I found some instances in our system that look to be the result of this issue in our system. I am attaching the SQL in case anyone cares to run it in SQL developer or your preferred tool.

Thanks!

Jeffrie

There is a strange bug that occurs for us in PSQuery, and I wanted to create a post about it for 2 reasons:

1. To confirm that it isn't something specific to how we are setup that is causing it (we have a lot of mods)
2. To raise awareness in case it causes issues for other folks

The Issue: Most of us who work in PSQuery are likely familiar with the automatic subqueries that are written to help return the most recent row in records that have an EFFDT (effective date) field - a feature I am thankful for!  However, I have noticed that when outerjoining two records that both have an EFFDT field, that the automatic logic does not work correctly.  The second EFFDT gets set to <= the first EFFDT instead of being set to <= SYSDATE. Example below:

This is a screenshot I took after outerjoining the record, PS_STDNT_FA_TERM to  PS_ADM_APPL_PROG - two records with unrelated effective dates. I can't think of many scenarios where this would be the desired outcome. 

Most of the time, you will need to assign the criteria to the correct ON clause, so you can easily fix expression 2 while you are in there. But honestly, it is kind of easy to miss...

So yeah, are any of you seeing this behavior in your system? I found some instances in our system that look to be the result of this issue in our system. I am attaching the SQL in case anyone cares to run it in SQL developer or your preferred tool.

Thanks!

Jeffrie

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...  

There is a strange bug that occurs for us in PSQuery, and I wanted to create a post about it for 2 reasons:

1. To confirm that it isn't something specific to how we are setup that is causing it (we have a lot of mods)
2. To raise awareness in case it causes issues for other folks

The Issue: Most of us who work in PSQuery are likely familiar with the automatic subqueries that are written to help return the most recent row in records that have an EFFDT (effective date) field - a feature I am thankful for!  However, I have noticed that when outerjoining two records that both have an EFFDT field, that the automatic logic does not work correctly.  The second EFFDT gets set to <= the first EFFDT instead of being set to <= SYSDATE. Example below:

This is a screenshot I took after outerjoining the record, PS_STDNT_FA_TERM to  PS_ADM_APPL_PROG - two records with unrelated effective dates. I can't think of many scenarios where this would be the desired outcome. 

Most of the time, you will need to assign the criteria to the correct ON clause, so you can easily fix expression 2 while you are in there. But honestly, it is kind of easy to miss...

So yeah, are any of you seeing this behavior in your system? I found some instances in our system that look to be the result of this issue in our system. I am attaching the SQL in case anyone cares to run it in SQL developer or your preferred tool.

Thanks!

Jeffrie

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...  

There is a strange bug that occurs for us in PSQuery, and I wanted to create a post about it for 2 reasons:

1. To confirm that it isn't something specific to how we are setup that is causing it (we have a lot of mods)
2. To raise awareness in case it causes issues for other folks

The Issue: Most of us who work in PSQuery are likely familiar with the automatic subqueries that are written to help return the most recent row in records that have an EFFDT (effective date) field - a feature I am thankful for!  However, I have noticed that when outerjoining two records that both have an EFFDT field, that the automatic logic does not work correctly.  The second EFFDT gets set to <= the first EFFDT instead of being set to <= SYSDATE. Example below:

This is a screenshot I took after outerjoining the record, PS_STDNT_FA_TERM to  PS_ADM_APPL_PROG - two records with unrelated effective dates. I can't think of many scenarios where this would be the desired outcome. 

Most of the time, you will need to assign the criteria to the correct ON clause, so you can easily fix expression 2 while you are in there. But honestly, it is kind of easy to miss...

So yeah, are any of you seeing this behavior in your system? I found some instances in our system that look to be the result of this issue in our system. I am attaching the SQL in case anyone cares to run it in SQL developer or your preferred tool.

Thanks!

Jeffrie

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...  

There is a strange bug that occurs for us in PSQuery, and I wanted to create a post about it for 2 reasons:

1. To confirm that it isn't something specific to how we are setup that is causing it (we have a lot of mods)
2. To raise awareness in case it causes issues for other folks

The Issue: Most of us who work in PSQuery are likely familiar with the automatic subqueries that are written to help return the most recent row in records that have an EFFDT (effective date) field - a feature I am thankful for!  However, I have noticed that when outerjoining two records that both have an EFFDT field, that the automatic logic does not work correctly.  The second EFFDT gets set to <= the first EFFDT instead of being set to <= SYSDATE. Example below:

This is a screenshot I took after outerjoining the record, PS_STDNT_FA_TERM to  PS_ADM_APPL_PROG - two records with unrelated effective dates. I can't think of many scenarios where this would be the desired outcome. 

Most of the time, you will need to assign the criteria to the correct ON clause, so you can easily fix expression 2 while you are in there. But honestly, it is kind of easy to miss...

So yeah, are any of you seeing this behavior in your system? I found some instances in our system that look to be the result of this issue in our system. I am attaching the SQL in case anyone cares to run it in SQL developer or your preferred tool.

Thanks!

Jeffrie

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...  

There is a strange bug that occurs for us in PSQuery, and I wanted to create a post about it for 2 reasons:

1. To confirm that it isn't something specific to how we are setup that is causing it (we have a lot of mods)
2. To raise awareness in case it causes issues for other folks

The Issue: Most of us who work in PSQuery are likely familiar with the automatic subqueries that are written to help return the most recent row in records that have an EFFDT (effective date) field - a feature I am thankful for!  However, I have noticed that when outerjoining two records that both have an EFFDT field, that the automatic logic does not work correctly.  The second EFFDT gets set to <= the first EFFDT instead of being set to <= SYSDATE. Example below:

This is a screenshot I took after outerjoining the record, PS_STDNT_FA_TERM to  PS_ADM_APPL_PROG - two records with unrelated effective dates. I can't think of many scenarios where this would be the desired outcome. 

Most of the time, you will need to assign the criteria to the correct ON clause, so you can easily fix expression 2 while you are in there. But honestly, it is kind of easy to miss...

So yeah, are any of you seeing this behavior in your system? I found some instances in our system that look to be the result of this issue in our system. I am attaching the SQL in case anyone cares to run it in SQL developer or your preferred tool.

Thanks!

Jeffrie

There is a strange bug that occurs for us in PSQuery, and I wanted to create a post about it for 2 reasons:

1. To confirm that it isn't something specific to how we are setup that is causing it (we have a lot of mods)
2. To raise awareness in case it causes issues for other folks

The Issue: Most of us who work in PSQuery are likely familiar with the automatic subqueries that are written to help return the most recent row in records that have an EFFDT (effective date) field - a feature I am thankful for!  However, I have noticed that when outerjoining two records that both have an EFFDT field, that the automatic logic does not work correctly.  The second EFFDT gets set to <= the first EFFDT instead of being set to <= SYSDATE. Example below:

This is a screenshot I took after outerjoining the record, PS_STDNT_FA_TERM to  PS_ADM_APPL_PROG - two records with unrelated effective dates. I can't think of many scenarios where this would be the desired outcome. 

Most of the time, you will need to assign the criteria to the correct ON clause, so you can easily fix expression 2 while you are in there. But honestly, it is kind of easy to miss...

So yeah, are any of you seeing this behavior in your system? I found some instances in our system that look to be the result of this issue in our system. I am attaching the SQL in case anyone cares to run it in SQL developer or your preferred tool.

Thanks!

Jeffrie

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?

There is a strange bug that occurs for us in PSQuery, and I wanted to create a post about it for 2 reasons:

1. To confirm that it isn't something specific to how we are setup that is causing it (we have a lot of mods)
2. To raise awareness in case it causes issues for other folks

The Issue: Most of us who work in PSQuery are likely familiar with the automatic subqueries that are written to help return the most recent row in records that have an EFFDT (effective date) field - a feature I am thankful for!  However, I have noticed that when outerjoining two records that both have an EFFDT field, that the automatic logic does not work correctly.  The second EFFDT gets set to <= the first EFFDT instead of being set to <= SYSDATE. Example below:

This is a screenshot I took after outerjoining the record, PS_STDNT_FA_TERM to  PS_ADM_APPL_PROG - two records with unrelated effective dates. I can't think of many scenarios where this would be the desired outcome. 

Most of the time, you will need to assign the criteria to the correct ON clause, so you can easily fix expression 2 while you are in there. But honestly, it is kind of easy to miss...

So yeah, are any of you seeing this behavior in your system? I found some instances in our system that look to be the result of this issue in our system. I am attaching the SQL in case anyone cares to run it in SQL developer or your preferred tool.

Thanks!

Jeffrie

There is a strange bug that occurs for us in PSQuery, and I wanted to create a post about it for 2 reasons:

1. To confirm that it isn't something specific to how we are setup that is causing it (we have a lot of mods)
2. To raise awareness in case it causes issues for other folks

The Issue: Most of us who work in PSQuery are likely familiar with the automatic subqueries that are written to help return the most recent row in records that have an EFFDT (effective date) field - a feature I am thankful for!  However, I have noticed that when outerjoining two records that both have an EFFDT field, that the automatic logic does not work correctly.  The second EFFDT gets set to <= the first EFFDT instead of being set to <= SYSDATE. Example below:

This is a screenshot I took after outerjoining the record, PS_STDNT_FA_TERM to  PS_ADM_APPL_PROG - two records with unrelated effective dates. I can't think of many scenarios where this would be the desired outcome. 

Most of the time, you will need to assign the criteria to the correct ON clause, so you can easily fix expression 2 while you are in there. But honestly, it is kind of easy to miss...

So yeah, are any of you seeing this behavior in your system? I found some instances in our system that look to be the result of this issue in our system. I am attaching the SQL in case anyone cares to run it in SQL developer or your preferred tool.

Thanks!

Jeffrie

There is a strange bug that occurs for us in PSQuery, and I wanted to create a post about it for 2 reasons:

1. To confirm that it isn't something specific to how we are setup that is causing it (we have a lot of mods)
2. To raise awareness in case it causes issues for other folks

The Issue: Most of us who work in PSQuery are likely familiar with the automatic subqueries that are written to help return the most recent row in records that have an EFFDT (effective date) field - a feature I am thankful for!  However, I have noticed that when outerjoining two records that both have an EFFDT field, that the automatic logic does not work correctly.  The second EFFDT gets set to <= the first EFFDT instead of being set to <= SYSDATE. Example below:

This is a screenshot I took after outerjoining the record, PS_STDNT_FA_TERM to  PS_ADM_APPL_PROG - two records with unrelated effective dates. I can't think of many scenarios where this would be the desired outcome. 

Most of the time, you will need to assign the criteria to the correct ON clause, so you can easily fix expression 2 while you are in there. But honestly, it is kind of easy to miss...

So yeah, are any of you seeing this behavior in your system? I found some instances in our system that look to be the result of this issue in our system. I am attaching the SQL in case anyone cares to run it in SQL developer or your preferred tool.

Thanks!

Jeffrie

There is a strange bug that occurs for us in PSQuery, and I wanted to create a post about it for 2 reasons:

1. To confirm that it isn't something specific to how we are setup that is causing it (we have a lot of mods)
2. To raise awareness in case it causes issues for other folks

The Issue: Most of us who work in PSQuery are likely familiar with the automatic subqueries that are written to help return the most recent row in records that have an EFFDT (effective date) field - a feature I am thankful for!  However, I have noticed that when outerjoining two records that both have an EFFDT field, that the automatic logic does not work correctly.  The second EFFDT gets set to <= the first EFFDT instead of being set to <= SYSDATE. Example below:

This is a screenshot I took after outerjoining the record, PS_STDNT_FA_TERM to  PS_ADM_APPL_PROG - two records with unrelated effective dates. I can't think of many scenarios where this would be the desired outcome. 

Most of the time, you will need to assign the criteria to the correct ON clause, so you can easily fix expression 2 while you are in there. But honestly, it is kind of easy to miss...

So yeah, are any of you seeing this behavior in your system? I found some instances in our system that look to be the result of this issue in our system. I am attaching the SQL in case anyone cares to run it in SQL developer or your preferred tool.

Thanks!

Jeffrie

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.

There is a strange bug that occurs for us in PSQuery, and I wanted to create a post about it for 2 reasons:

1. To confirm that it isn't something specific to how we are setup that is causing it (we have a lot of mods)
2. To raise awareness in case it causes issues for other folks

The Issue: Most of us who work in PSQuery are likely familiar with the automatic subqueries that are written to help return the most recent row in records that have an EFFDT (effective date) field - a feature I am thankful for!  However, I have noticed that when outerjoining two records that both have an EFFDT field, that the automatic logic does not work correctly.  The second EFFDT gets set to <= the first EFFDT instead of being set to <= SYSDATE. Example below:

This is a screenshot I took after outerjoining the record, PS_STDNT_FA_TERM to  PS_ADM_APPL_PROG - two records with unrelated effective dates. I can't think of many scenarios where this would be the desired outcome. 

Most of the time, you will need to assign the criteria to the correct ON clause, so you can easily fix expression 2 while you are in there. But honestly, it is kind of easy to miss...

So yeah, are any of you seeing this behavior in your system? I found some instances in our system that look to be the result of this issue in our system. I am attaching the SQL in case anyone cares to run it in SQL developer or your preferred tool.

Thanks!

Jeffrie

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.

There is a strange bug that occurs for us in PSQuery, and I wanted to create a post about it for 2 reasons:

1. To confirm that it isn't something specific to how we are setup that is causing it (we have a lot of mods)
2. To raise awareness in case it causes issues for other folks

The Issue: Most of us who work in PSQuery are likely familiar with the automatic subqueries that are written to help return the most recent row in records that have an EFFDT (effective date) field - a feature I am thankful for!  However, I have noticed that when outerjoining two records that both have an EFFDT field, that the automatic logic does not work correctly.  The second EFFDT gets set to <= the first EFFDT instead of being set to <= SYSDATE. Example below:

This is a screenshot I took after outerjoining the record, PS_STDNT_FA_TERM to  PS_ADM_APPL_PROG - two records with unrelated effective dates. I can't think of many scenarios where this would be the desired outcome. 

Most of the time, you will need to assign the criteria to the correct ON clause, so you can easily fix expression 2 while you are in there. But honestly, it is kind of easy to miss...

So yeah, are any of you seeing this behavior in your system? I found some instances in our system that look to be the result of this issue in our system. I am attaching the SQL in case anyone cares to run it in SQL developer or your preferred tool.

Thanks!

Jeffrie

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.

There is a strange bug that occurs for us in PSQuery, and I wanted to create a post about it for 2 reasons:

1. To confirm that it isn't something specific to how we are setup that is causing it (we have a lot of mods)
2. To raise awareness in case it causes issues for other folks

The Issue: Most of us who work in PSQuery are likely familiar with the automatic subqueries that are written to help return the most recent row in records that have an EFFDT (effective date) field - a feature I am thankful for!  However, I have noticed that when outerjoining two records that both have an EFFDT field, that the automatic logic does not work correctly.  The second EFFDT gets set to <= the first EFFDT instead of being set to <= SYSDATE. Example below:

This is a screenshot I took after outerjoining the record, PS_STDNT_FA_TERM to  PS_ADM_APPL_PROG - two records with unrelated effective dates. I can't think of many scenarios where this would be the desired outcome. 

Most of the time, you will need to assign the criteria to the correct ON clause, so you can easily fix expression 2 while you are in there. But honestly, it is kind of easy to miss...

So yeah, are any of you seeing this behavior in your system? I found some instances in our system that look to be the result of this issue in our system. I am attaching the SQL in case anyone cares to run it in SQL developer or your preferred tool.

Thanks!

Jeffrie

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.

There is a strange bug that occurs for us in PSQuery, and I wanted to create a post about it for 2 reasons:

1. To confirm that it isn't something specific to how we are setup that is causing it (we have a lot of mods)
2. To raise awareness in case it causes issues for other folks

The Issue: Most of us who work in PSQuery are likely familiar with the automatic subqueries that are written to help return the most recent row in records that have an EFFDT (effective date) field - a feature I am thankful for!  However, I have noticed that when outerjoining two records that both have an EFFDT field, that the automatic logic does not work correctly.  The second EFFDT gets set to <= the first EFFDT instead of being set to <= SYSDATE. Example below:

This is a screenshot I took after outerjoining the record, PS_STDNT_FA_TERM to  PS_ADM_APPL_PROG - two records with unrelated effective dates. I can't think of many scenarios where this would be the desired outcome. 

Most of the time, you will need to assign the criteria to the correct ON clause, so you can easily fix expression 2 while you are in there. But honestly, it is kind of easy to miss...

So yeah, are any of you seeing this behavior in your system? I found some instances in our system that look to be the result of this issue in our system. I am attaching the SQL in case anyone cares to run it in SQL developer or your preferred tool.

Thanks!

Jeffrie

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.

There is a strange bug that occurs for us in PSQuery, and I wanted to create a post about it for 2 reasons:

1. To confirm that it isn't something specific to how we are setup that is causing it (we have a lot of mods)
2. To raise awareness in case it causes issues for other folks

The Issue: Most of us who work in PSQuery are likely familiar with the automatic subqueries that are written to help return the most recent row in records that have an EFFDT (effective date) field - a feature I am thankful for!  However, I have noticed that when outerjoining two records that both have an EFFDT field, that the automatic logic does not work correctly.  The second EFFDT gets set to <= the first EFFDT instead of being set to <= SYSDATE. Example below:

This is a screenshot I took after outerjoining the record, PS_STDNT_FA_TERM to  PS_ADM_APPL_PROG - two records with unrelated effective dates. I can't think of many scenarios where this would be the desired outcome. 

Most of the time, you will need to assign the criteria to the correct ON clause, so you can easily fix expression 2 while you are in there. But honestly, it is kind of easy to miss...

So yeah, are any of you seeing this behavior in your system? I found some instances in our system that look to be the result of this issue in our system. I am attaching the SQL in case anyone cares to run it in SQL developer or your preferred tool.

Thanks!

Jeffrie