PS Query & SQL

Has any one had any issues running queries with record CLASS_TBL_SCTY?  We have several queries that have run successfully for years that are no longer yielding any results.  All of the individuals running the queries have not had any changes to their security setup including row-level security.  I have put in a help request with Oracle but wanted to check in to see if anyone in the HEUG community has experienced this.  We are currently on the following versions:

HI Jill,

Several tables that use OPRID as a key field have a built-in security "feature" where they limit results to the OPRID of the current user. Depending on the nature of the query you could be running into a piece of criteria that creates no results.

Have someone with Query Manager access click on the View SQL button and see if the SQL has an extra line limiting the OPRID to only the current user. 

--

Jeffrie Brooks | BUSINESS SYSTEM ANALYST UNIVERSITY OF MICHIGAN | INFORMATION AND TECHNOLOGY SERVICES 734-647-8763 | jedobr@umich.edu

Has any one had any issues running queries with record CLASS_TBL_SCTY?  We have several queries that have run successfully for years that are no longer yielding any results.  All of the individuals running the queries have not had any changes to their security setup including row-level security.  I have put in a help request with Oracle but wanted to check in to see if anyone in the HEUG community has experienced this.  We are currently on the following versions:

HI Jill,

Several tables that use OPRID as a key field have a built-in security "feature" where they limit results to the OPRID of the current user. Depending on the nature of the query you could be running into a piece of criteria that creates no results.

Have someone with Query Manager access click on the View SQL button and see if the SQL has an extra line limiting the OPRID to only the current user. 

--

Jeffrie Brooks | BUSINESS SYSTEM ANALYST UNIVERSITY OF MICHIGAN | INFORMATION AND TECHNOLOGY SERVICES 734-647-8763 | jedobr@umich.edu

Has any one had any issues running queries with record CLASS_TBL_SCTY?  We have several queries that have run successfully for years that are no longer yielding any results.  All of the individuals running the queries have not had any changes to their security setup including row-level security.  I have put in a help request with Oracle but wanted to check in to see if anyone in the HEUG community has experienced this.  We are currently on the following versions:

Hello Jill,

If that is the case, then go into query properties and change the Query Type to Process:

That will allow you to see anyone regardless of OPRID restrictions.

HI Jill,

Several tables that use OPRID as a key field have a built-in security "feature" where they limit results to the OPRID of the current user. Depending on the nature of the query you could be running into a piece of criteria that creates no results.

Have someone with Query Manager access click on the View SQL button and see if the SQL has an extra line limiting the OPRID to only the current user. 

--

Jeffrie Brooks | BUSINESS SYSTEM ANALYST UNIVERSITY OF MICHIGAN | INFORMATION AND TECHNOLOGY SERVICES 734-647-8763 | jedobr@umich.edu

Has any one had any issues running queries with record CLASS_TBL_SCTY?  We have several queries that have run successfully for years that are no longer yielding any results.  All of the individuals running the queries have not had any changes to their security setup including row-level security.  I have put in a help request with Oracle but wanted to check in to see if anyone in the HEUG community has experienced this.  We are currently on the following versions:

Hi Dan!

I just tried that and still no results. 

Hello Jill,

If that is the case, then go into query properties and change the Query Type to Process:

That will allow you to see anyone regardless of OPRID restrictions.

HI Jill,

Several tables that use OPRID as a key field have a built-in security "feature" where they limit results to the OPRID of the current user. Depending on the nature of the query you could be running into a piece of criteria that creates no results.

Have someone with Query Manager access click on the View SQL button and see if the SQL has an extra line limiting the OPRID to only the current user. 

--

Jeffrie Brooks | BUSINESS SYSTEM ANALYST UNIVERSITY OF MICHIGAN | INFORMATION AND TECHNOLOGY SERVICES 734-647-8763 | jedobr@umich.edu

Has any one had any issues running queries with record CLASS_TBL_SCTY?  We have several queries that have run successfully for years that are no longer yielding any results.  All of the individuals running the queries have not had any changes to their security setup including row-level security.  I have put in a help request with Oracle but wanted to check in to see if anyone in the HEUG community has experienced this.  We are currently on the following versions:

Hello Jill,

If that is the case, then go into query properties and change the Query Type to Process:

That will allow you to see anyone regardless of OPRID restrictions.

HI Jill,

Several tables that use OPRID as a key field have a built-in security "feature" where they limit results to the OPRID of the current user. Depending on the nature of the query you could be running into a piece of criteria that creates no results.

Have someone with Query Manager access click on the View SQL button and see if the SQL has an extra line limiting the OPRID to only the current user. 

--

Jeffrie Brooks | BUSINESS SYSTEM ANALYST UNIVERSITY OF MICHIGAN | INFORMATION AND TECHNOLOGY SERVICES 734-647-8763 | jedobr@umich.edu

Has any one had any issues running queries with record CLASS_TBL_SCTY?  We have several queries that have run successfully for years that are no longer yielding any results.  All of the individuals running the queries have not had any changes to their security setup including row-level security.  I have put in a help request with Oracle but wanted to check in to see if anyone in the HEUG community has experienced this.  We are currently on the following versions:

Has any one had any issues running queries with record CLASS_TBL_SCTY?  We have several queries that have run successfully for years that are no longer yielding any results.  All of the individuals running the queries have not had any changes to their security setup including row-level security.  I have put in a help request with Oracle but wanted to check in to see if anyone in the HEUG community has experienced this.  We are currently on the following versions:

Hi Jill. I'm pretty sure this is related to the catalog number changes Oracle made in pum 35.  The SQL behind the class_tbl_scty view is trimming the catalog number.  I may have been needed for other places this is used (but not sure).  When you join class_tbl to class_tbl_scty in a query, the catalog numbers won't match, because class_tbl will have the extra space(s) in the front.  You can test this by taking out the criteria for catalog number join.  The results won't be right, but you should at least get data then and be able to narrow down the issue. 

Has any one had any issues running queries with record CLASS_TBL_SCTY?  We have several queries that have run successfully for years that are no longer yielding any results.  All of the individuals running the queries have not had any changes to their security setup including row-level security.  I have put in a help request with Oracle but wanted to check in to see if anyone in the HEUG community has experienced this.  We are currently on the following versions:

Hi Frances--thanks for that note about the changes made by Oracle! If the change with respect to the trimming of CATALOG_NBR occurred since Jill's queries had been last updated, then I think that would certainly explain it. Here's the SQL for the security table, with the relevant bit highlighted:

When I modify the criteria PS Query manager defaults to in joining this table to the CLASS_TBL record (A.CATALOG_NBR equal to B.CATALOG_NBR), which produces zero results, to one replacing A.CATALOG_NBR with the expression "LTRM(A.CATALOG_NBR)" a la the SQL above, I get the expected results:

As a follow-up, what's the best way to keep abreast of changes made by Oracle? @Frances Donahoe, you mentioned "pum 35"-where would I go to see the changes you referenced here?

Thanks so much!

Cheers,

Robert

Hi Jill. I'm pretty sure this is related to the catalog number changes Oracle made in pum 35.  The SQL behind the class_tbl_scty view is trimming the catalog number.  I may have been needed for other places this is used (but not sure).  When you join class_tbl to class_tbl_scty in a query, the catalog numbers won't match, because class_tbl will have the extra space(s) in the front.  You can test this by taking out the criteria for catalog number join.  The results won't be right, but you should at least get data then and be able to narrow down the issue. 

Has any one had any issues running queries with record CLASS_TBL_SCTY?  We have several queries that have run successfully for years that are no longer yielding any results.  All of the individuals running the queries have not had any changes to their security setup including row-level security.  I have put in a help request with Oracle but wanted to check in to see if anyone in the HEUG community has experienced this.  We are currently on the following versions:

Hi Frances--thanks for that note about the changes made by Oracle! If the change with respect to the trimming of CATALOG_NBR occurred since Jill's queries had been last updated, then I think that would certainly explain it. Here's the SQL for the security table, with the relevant bit highlighted:

When I modify the criteria PS Query manager defaults to in joining this table to the CLASS_TBL record (A.CATALOG_NBR equal to B.CATALOG_NBR), which produces zero results, to one replacing A.CATALOG_NBR with the expression "LTRM(A.CATALOG_NBR)" a la the SQL above, I get the expected results:

As a follow-up, what's the best way to keep abreast of changes made by Oracle? @Frances Donahoe, you mentioned "pum 35"-where would I go to see the changes you referenced here?

Thanks so much!

Cheers,

Robert

Hi Jill. I'm pretty sure this is related to the catalog number changes Oracle made in pum 35.  The SQL behind the class_tbl_scty view is trimming the catalog number.  I may have been needed for other places this is used (but not sure).  When you join class_tbl to class_tbl_scty in a query, the catalog numbers won't match, because class_tbl will have the extra space(s) in the front.  You can test this by taking out the criteria for catalog number join.  The results won't be right, but you should at least get data then and be able to narrow down the issue. 

Has any one had any issues running queries with record CLASS_TBL_SCTY?  We have several queries that have run successfully for years that are no longer yielding any results.  All of the individuals running the queries have not had any changes to their security setup including row-level security.  I have put in a help request with Oracle but wanted to check in to see if anyone in the HEUG community has experienced this.  We are currently on the following versions:

Hi Jill. I'm pretty sure this is related to the catalog number changes Oracle made in pum 35.  The SQL behind the class_tbl_scty view is trimming the catalog number.  I may have been needed for other places this is used (but not sure).  When you join class_tbl to class_tbl_scty in a query, the catalog numbers won't match, because class_tbl will have the extra space(s) in the front.  You can test this by taking out the criteria for catalog number join.  The results won't be right, but you should at least get data then and be able to narrow down the issue. 

Has any one had any issues running queries with record CLASS_TBL_SCTY?  We have several queries that have run successfully for years that are no longer yielding any results.  All of the individuals running the queries have not had any changes to their security setup including row-level security.  I have put in a help request with Oracle but wanted to check in to see if anyone in the HEUG community has experienced this.  We are currently on the following versions: