PS Query & SQL

TL:DR

• Is it possible / How do I create:
• A "placeholder/dummy Expression" on the Union level of a PS Query, for a Drilling URL Expression on the Top Level of the query?

Details:

• Existing PS Query has a Drilling URL Expression used as a field (acts as a hotlink to a component page in HCM)
• I need to add a Union level that returns future-dated data 
• The Drilling URL doesn't work for future-dated employees since the component page doesn't exist for them yet
• Therefore, the Drilling URL is acting as a de facto filtering criteria
• If I "just don't add the Drilling URL to the Union level", I get "ORA-01789: query block has incorrect number of result columns"
• Can I create a placeholder/dummy Expression on the Union level so the total number of columns is equal to the Top Level?
• If so, how?

Please advise.

------------------------------
Scott Frey
Senior Data Analyst - Human Resource Information Systems
University of Colorado System
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------

TL:DR

• Is it possible / How do I create:
• A "placeholder/dummy Expression" on the Union level of a PS Query, for a Drilling URL Expression on the Top Level of the query?

Details:

• Existing PS Query has a Drilling URL Expression used as a field (acts as a hotlink to a component page in HCM)
• I need to add a Union level that returns future-dated data 
• The Drilling URL doesn't work for future-dated employees since the component page doesn't exist for them yet
• Therefore, the Drilling URL is acting as a de facto filtering criteria
• If I "just don't add the Drilling URL to the Union level", I get "ORA-01789: query block has incorrect number of result columns"
• Can I create a placeholder/dummy Expression on the Union level so the total number of columns is equal to the Top Level?
• If so, how?

Please advise.

------------------------------
Scott Frey
Senior Data Analyst - Human Resource Information Systems
University of Colorado System
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------

Thanks Daniel.

Recall that when creating dummy expressions to act as placeholders on the Union level, both the Field Type and Field Width must match the corresponding column on the Top level.   

What do I use for Field Type and Field Width when trying to match a Drilling URL Expression?

I've already tried:

CHAR1  ' '

NUMBER1.0 NULL

Both options return the ORA-01789 error, which is different from the usual Oracle 37,1 error where the number of columns don't match between levels. So, it looks like Oracle is reading the number of columns correctly. But mismatching on either Field Type or Field Width, or expression syntax, or some combination of the three. 

TL:DR

• Is it possible / How do I create:
• A "placeholder/dummy Expression" on the Union level of a PS Query, for a Drilling URL Expression on the Top Level of the query?

Details:

• Existing PS Query has a Drilling URL Expression used as a field (acts as a hotlink to a component page in HCM)
• I need to add a Union level that returns future-dated data 
• The Drilling URL doesn't work for future-dated employees since the component page doesn't exist for them yet
• Therefore, the Drilling URL is acting as a de facto filtering criteria
• If I "just don't add the Drilling URL to the Union level", I get "ORA-01789: query block has incorrect number of result columns"
• Can I create a placeholder/dummy Expression on the Union level so the total number of columns is equal to the Top Level?
• If so, how?

Please advise.

------------------------------
Scott Frey
Senior Data Analyst - Human Resource Information Systems
University of Colorado System
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------

Yeah that is probably because although the logic exists in Query Manager, the fields themselves in the SQL are not clearly stated.

Try either CAST(NULL AS VARCHAR(##)) for characters where ## is the length or CAST(NULL AS NUMBER) for numerical fields. Let me know if that works.

Thanks Daniel.

Recall that when creating dummy expressions to act as placeholders on the Union level, both the Field Type and Field Width must match the corresponding column on the Top level.   

What do I use for Field Type and Field Width when trying to match a Drilling URL Expression?

I've already tried:

CHAR1  ' '

NUMBER1.0 NULL

Both options return the ORA-01789 error, which is different from the usual Oracle 37,1 error where the number of columns don't match between levels. So, it looks like Oracle is reading the number of columns correctly. But mismatching on either Field Type or Field Width, or expression syntax, or some combination of the three. 

TL:DR

• Is it possible / How do I create:
• A "placeholder/dummy Expression" on the Union level of a PS Query, for a Drilling URL Expression on the Top Level of the query?

Details:

• Existing PS Query has a Drilling URL Expression used as a field (acts as a hotlink to a component page in HCM)
• I need to add a Union level that returns future-dated data 
• The Drilling URL doesn't work for future-dated employees since the component page doesn't exist for them yet
• Therefore, the Drilling URL is acting as a de facto filtering criteria
• If I "just don't add the Drilling URL to the Union level", I get "ORA-01789: query block has incorrect number of result columns"
• Can I create a placeholder/dummy Expression on the Union level so the total number of columns is equal to the Top Level?
• If so, how?

Please advise.

------------------------------
Scott Frey
Senior Data Analyst - Human Resource Information Systems
University of Colorado System
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------

Hey Scott, the dummy field should work as initially described if you use the same field type in the expression. Part of what the error does NOT tell you, is that you ALSO have to have the dummy field be a drilling url.  You can add any drilling url you want, but if you don't have it, you will continue to get this error. I came across this myself a few months back, and it was a major pain to figure this out.

Hope this helps!

Yeah that is probably because although the logic exists in Query Manager, the fields themselves in the SQL are not clearly stated.

Try either CAST(NULL AS VARCHAR(##)) for characters where ## is the length or CAST(NULL AS NUMBER) for numerical fields. Let me know if that works.

Thanks Daniel.

Recall that when creating dummy expressions to act as placeholders on the Union level, both the Field Type and Field Width must match the corresponding column on the Top level.   

What do I use for Field Type and Field Width when trying to match a Drilling URL Expression?

I've already tried:

CHAR1  ' '

NUMBER1.0 NULL

Both options return the ORA-01789 error, which is different from the usual Oracle 37,1 error where the number of columns don't match between levels. So, it looks like Oracle is reading the number of columns correctly. But mismatching on either Field Type or Field Width, or expression syntax, or some combination of the three. 

TL:DR

• Is it possible / How do I create:
• A "placeholder/dummy Expression" on the Union level of a PS Query, for a Drilling URL Expression on the Top Level of the query?

Details:

• Existing PS Query has a Drilling URL Expression used as a field (acts as a hotlink to a component page in HCM)
• I need to add a Union level that returns future-dated data 
• The Drilling URL doesn't work for future-dated employees since the component page doesn't exist for them yet
• Therefore, the Drilling URL is acting as a de facto filtering criteria
• If I "just don't add the Drilling URL to the Union level", I get "ORA-01789: query block has incorrect number of result columns"
• Can I create a placeholder/dummy Expression on the Union level so the total number of columns is equal to the Top Level?
• If so, how?

Please advise.

------------------------------
Scott Frey
Senior Data Analyst - Human Resource Information Systems
University of Colorado System
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------

TL:DR

• Is it possible / How do I create:
• A "placeholder/dummy Expression" on the Union level of a PS Query, for a Drilling URL Expression on the Top Level of the query?

Details:

• Existing PS Query has a Drilling URL Expression used as a field (acts as a hotlink to a component page in HCM)
• I need to add a Union level that returns future-dated data 
• The Drilling URL doesn't work for future-dated employees since the component page doesn't exist for them yet
• Therefore, the Drilling URL is acting as a de facto filtering criteria
• If I "just don't add the Drilling URL to the Union level", I get "ORA-01789: query block has incorrect number of result columns"
• Can I create a placeholder/dummy Expression on the Union level so the total number of columns is equal to the Top Level?
• If so, how?

Please advise.

------------------------------
Scott Frey
Senior Data Analyst - Human Resource Information Systems
University of Colorado System
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------

Following up on my initial post:

I was able to successfully implement a Drilling URL\Component URL in a Unioned query with Dummy/Placeholder Expressions.

1. I created the Drilling URL on the Top Level as normal and mapped it to a column with a valid field.
2. I created the same Drilling URL on the Union Level, and mapped it to the same column. However, the field in the column was now a "placeholder" Expression (in this case, CHAR4 with an expression of ' ')
3. When I ran the query, I got valid hotlinks for the values that were returned by the Top Level of the query, but none for the Union Level, since those values were blanks.
4. No SQL errors were encountered. 

Please note: I found that each Drilling URL (no matter which Level it's intended for) must be added to EVERY Level of the query, and mapped to a column. Otherwise, you'll get a SQL error and the query will not run.   YMMV. 

Thanks everyone for your input and help.

TL:DR

• Is it possible / How do I create:
• A "placeholder/dummy Expression" on the Union level of a PS Query, for a Drilling URL Expression on the Top Level of the query?

Details:

• Existing PS Query has a Drilling URL Expression used as a field (acts as a hotlink to a component page in HCM)
• I need to add a Union level that returns future-dated data 
• The Drilling URL doesn't work for future-dated employees since the component page doesn't exist for them yet
• Therefore, the Drilling URL is acting as a de facto filtering criteria
• If I "just don't add the Drilling URL to the Union level", I get "ORA-01789: query block has incorrect number of result columns"
• Can I create a placeholder/dummy Expression on the Union level so the total number of columns is equal to the Top Level?
• If so, how?

Please advise.

------------------------------
Scott Frey
Senior Data Analyst - Human Resource Information Systems
University of Colorado System
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------