Reporting on fields returned in sub query

[mtn244]

I have some questions about using a sub query in CR 8.5 on an Oracle 8i database (OLE DB connection).
The report uses two tables. There's a lot of typing on this message so state my questions first so
you don't have to look for them in the text.
1. How do I get the fields returned in a sub query to appear on my report?
2. Is there a better way than what I'm doing to get the correct data for the report (all current
positions and employees assigned to filled positions)?

PAPOSITION is the main table and EMPLOYEE, which supplies the employee
assigned to a position. The PAPOSITION table holds multiple records per position. The current
record can be retrieved with selection criteria. Note: A blank date in the HR software shows as
1/1/1700 in the database.

Example PAPOSITION data:

POSITION EFFECT_DATE END_DATE CHG_REASON
1-00-12-0013 1/1/2002 1/31/2003 CA
1-00-12-0013 2/1/2003 1/1/1700 CA/FILL
1-00-12-0301 2/1/2002 1/1/1700 CONT ST
1-00-00-0415 1/1/2002 9/15/2003 CA
1-00-00-0415 9/16/2003 1/1/1700 CA/VAC

The EMPLOYEE table’s only link to the PAPOSITION table is on the POSITION field. There may be
multiple employee records with the same position numbers. If an employee terms, HR keeps the
position number in the termed employee’s record to show history. The active employee moving into
that open position will also have that position number. The active employees can be selected by
limiting the selection to employees who have not termed or retired.

Example EMPLOYEE data:

EMPLOYEE LAST_NAME FIRST_NAME MI STATUS PROC LVL POSITION
300444 SMITH ROBERT E T J001 1-00-12-0013
305552 JONES MARY M AF J001 1-00-12-0013
302381 WHITE JAMES L AF J001 1-00-12-0301
307799 BLACK FRED P T J002 1-00-00-0415

Example Hoped-For Report data (not all fields shown because of space):

POSITION EFFECT_DATE END_DATE CHG_REASON EMPLOYEE LAST_NAME EMP_STATUS
1-00-12-0013 2/1/2003 1/1/1700 CA/FILL 305552 JONES AF
1-00-12-0301 2/1/2002 1/1/1700 CONT ST 302381 WHITE AF
1-00-00-0415 9/16/2003 1/1/1700 CA/VAC



The report needs to show all current positions and the current employees assigned to the positions.
If a position does not have an employee assigned to it, then the employee field on the report should
be blank. Maybe there is a way to link the tables that I haven’t thought of, but it seems that the
non-termed employees would need to be selected prior joining the tables. Creating a stored procedure
in the DB is not an option for me, so a sub query seemed the only answer.

The first query below works in SQL*Plus and returns 2716 records. The second query below is from
CR SHOW SQL. It returns the same number of records. My problem with using this sub query is how
do I get the fields returned from the sub query to be available to put in my report? The EMPLOYEE
table is not listed in the Field Explorer because I didn’t add the table.

Is there a better way to do what I need to do -- show all the current positions (filled and empty)
and the non-termed employees who are assigned to the filled positions?

Thanks for any help you can offer.

=========================================================================
****This works in SQL*Plus returning 2716 rows.****

SELECT
PAPOSITION.POSITION,
PAPOSITION.EFFECT_DATE,
PAPOSITION.END_DATE,
PAPOSITION.CHG_REASON,
EMP2.EMPLOYEE,
EMP2.EMP_STATUS,
EMP2.PROCESS_LEVEL,
EMP2.POSITION
FROM
PAPOSITION PAPOSITION, (SELECT EMPLOYEE,LAST_NAME,FIRST_NAME,MIDDLE_INIT,
EMP_STATUS,PROCESS_LEVEL,POSITION
FROM EMPLOYEE
WHERE EMP_STATUS <> 'R' AND
EMP_STATUS <> 'RB' AND
EMP_STATUS <> 'T' AND
EMP_STATUS <> 'TP' AND
EMP_STATUS <> 'X' AND
PROCESS_LEVEL >= 'J001' AND
PROCESS_LEVEL <= 'T6UNI') EMP2
WHERE
PAPOSITION.POSITION NOT LIKE '0%' AND
PAPOSITION.EFFECT_DATE >= TO_DATE ('01-01-2002', 'DD-MM-YYYY') AND
PAPOSITION.EFFECT_DATE <= TO_DATE ('17-02-2004', 'DD-MM-YYYY') AND
(PAPOSITION.END_DATE < TO_DATE ('01-01-2002', 'DD-MM-YYYY') OR
PAPOSITION.END_DATE > TO_DATE ('17-02-2004', 'DD-MM-YYYY')) AND
PAPOSITION.PROCESS_LEVEL >= 'J001' AND
PAPOSITION.&quot;PROCESS_LEVEL&quot; <= 'T6UNI' AND
PAPOSITION.POSITION = EMP2.POSITION(+)
ORDER BY
PAPOSITION.POSITION ASC

=========================================================================
****This works in CR SHOW SQL returning 2716 rows.****

SELECT
PAPOSITION.&quot;POSITION&quot;,
PAPOSITION.&quot;EFFECT_DATE&quot;,
PAPOSITION.&quot;END_DATE&quot;,
PAPOSITION.&quot;CHG_REASON&quot;
FROM
LAWDBA.PAPOSITION PAPOSITION, (SELECT
EMPLOYEE2.&quot;EMPLOYEE&quot;,EMPLOYEE2.&quot;LAST_NAME&quot;,
EMPLOYEE2.&quot;FIRST_NAME&quot;,EMPLOYEE2.&quot;MIDDLE_INIT&quot;,
EMPLOYEE2.&quot;EMP_STATUS&quot;,EMPLOYEE2.&quot;PROCESS_LEVEL&quot; ,
EMPLOYEE2.&quot;POSITION&quot;
FROM
LAWDBA.EMPLOYEE EMPLOYEE2
WHERE
EMPLOYEE2.&quot;EMP_STATUS&quot; <> 'R' AND
EMPLOYEE2.&quot;EMP_STATUS&quot; <> 'RB' AND
EMPLOYEE2.&quot;EMP_STATUS&quot; <> 'T' AND
EMPLOYEE2.&quot;EMP_STATUS&quot; <> 'TP' AND
EMPLOYEE2.&quot;EMP_STATUS&quot; <> 'X' AND
EMPLOYEE2.&quot;PROCESS_LEVEL&quot; >= 'J001' AND
EMPLOYEE2.&quot;PROCESS_LEVEL&quot; <= 'T6UNI') EMPLOYEE3
WHERE
PAPOSITION.&quot;POSITION&quot; NOT LIKE '0%' AND
PAPOSITION.&quot;EFFECT_DATE&quot; >= TO_DATE ('01-01-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND
PAPOSITION.&quot;EFFECT_DATE&quot; <= TO_DATE ('17-02-2004 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND
(PAPOSITION.&quot;END_DATE&quot; < TO_DATE ('01-01-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS') OR
PAPOSITION.&quot;END_DATE&quot; > TO_DATE ('17-02-2004 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) AND
PAPOSITION.&quot;PROCESS_LEVEL&quot; >= 'J001' AND
PAPOSITION.&quot;PROCESS_LEVEL&quot; <= 'T6UNI' AND
PAPOSITION.&quot;POSITION&quot; = EMPLOYEE3.&quot;POSITION&quot;(+)
ORDER BY
PAPOSITION.&quot;POSITION&quot; ASC

 

[synapsevampire]

The simplest means is to create a View on the Oracle database and use that as the data source.

There are ways to cheat using real SQL within a CR 8.5 report such as creating an ADO connection and pasting in your SQL, but I generally don't suggest doing so.

At the front of your Oracle SQL place the following line:

Create or replace view VEmpPosition as

Now forward it to your dba and ask him to create it and grant you rights to it.

BTW, what you're calling a subquery here is often termed a derived table as a subquery can be used in many different ways and isn't as descriptive.

-k