distinct query & a union of some kind?

Hi all,

I need assistance (obviously) with two issues. I apologize for the length.

FIRST ISSUE
I wrote a distinct query (which depending on WHERE minimizes over 15700 rows to a few hundred or less depending on SELECT). I pasted an example of it below.

select distinct pr.host ||'|'|| PR.PRO_EN ||'|'|| PR.PRO_LNAME ||'|'|| PR.PRO_FNAME ||'|'|| PR.PRO_CLASS ||'|'|| PR.PRO_SPEC_DESC ||'|'|| PR.PRO_SPEC_CD||'|'|| PR.PRO_DEA ||'|'|| PR.PRO_LIC ||'|'|| PR.PRO_TAXON ||'|'||
substr(record_image,INSTR(RECORD_IMAGE,'^',1,13)+1 ,
(INSTR(RECORD_IMAGE,'^',1,14) - INSTR(RECORD_IMAGE,'^',1,13) - 1)) ||'|'||
substr(record_image,INSTR(record_image,'^',1,15)+1 ,
(INSTR(RECORD_IMAGE,'^',1,16) - INSTR(RECORD_IMAGE,'^',1,15) - 1)) ||'|'||
LE.error_code
FROM LOAD_ERRORS LE, I_PRO_TEMP PR
WHERE LE.FILE_TYPE = 'FILE' AND
LE.FILE_NAME LIKE 'test_1234_5678_FILE_2004%' AND
LE.ERROR_CODE = '200' AND
Pr.host='1234' and
SUBSTR(record_image,INSTR(RECORD_IMAGE,'^',1,13)+1 ,
(INSTR(RECORD_IMAGE,'^',1,14) - INSTR(RECORD_IMAGE,'^',1,13) - 1)) =
Pr.PRO_EN (+);

I do need for all of the SELECT criteria below to be DISTINCT. However, I also need to include PP.PRO_ID from another table called PP.PRO_PERM. However, if I include PP.PRO_ID in the DISTINCT query, instead of getting a few hunred rows, I would only get about 20 rows (which as you know, are the rows that have PRO_ID filled in/present).

Many of the few hundred rows (in the query below) wont have a PRO_ID (if a row has one, it means the data made it to the permanent table - PRO.PERM). Is there a way to get what I want 15700 rows reduced to a few hundred rows that include and exclue PRO_ID? I tried one or two other things in the WHERE but, obviously it didnt work for me.

==================================================

SECOND ISSUE
I need to expand the query above or just run a few separate queries with a slightly different SELECT AND WHERE. In looking at the query example above, the following line corresponds to person # 1:
SUBSTR(record_image,INSTR(RECORD_IMAGE,'^',1,13)+1 ,
(INSTR(RECORD_IMAGE,'^',1,14) - INSTR(RECORD_IMAGE,'^',1,13) -
1)) =Pr.PRO_EN (+);
There could also be, if entered, a person #2 and a person #3.

What Ive been doing is running a query which includes the above (and not DISTINCT) for each person (of course for person #2 and #3 I would have to change the line above to correspond to person#2 and person#3). Also, within this particular query, Id ask for the people that person 1, 2, and 3 have serviced. I would then open up the text file for person #1, the file for person #2, and the file for person #3 in Excel. I would then copy data from person #2 andd #3 and insert it on the row next to person #1s data.

I think there has to be an easier way. Now, when I tried UNION (long before the modified query in issue one), I wouldnt get back the number of results that I shouldve gotten back. If I recall correctly, I only received info if person #1, person#2, and person #3 were available for within each row of data. However, I need to see all rows of data. Id like it to appear (all on one row) like:
person_serviced (by person 1, 2, 3) and person_serviced personal
data, person 1 and their data, person 2 and their data, and person 3
and their data.

Person 1, 2, and 3s personal data would be found in PRO_TEMP (if available) and hopefully PRO_PERM (if their data made it over to the permanent table). They all use PRO_EN for identification (other than their name).

What I need is a query that can give me what I all of this one one row. Is that possible?

and I apologize for the length of this.This looks like Oracle, right?

If so, I don't understand why you get less rows simply by including a column that may be null in the SELECT DISTINCT. Oracle considers NULL and the empty string '' to be equivalent, and concatenation of a NULL onto a string does not make the resulting string NULL:

SQL> select distinct ename||null from emp;

ENAME||NUL
----
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
MARTIN
MILLER
SCOTT
SMITH
TURNER
WARD

Of course, if you are NOT using Oracle, then things probably will be different. If so, you need to use whatever function that DBMS provides to replace a NULL by a value (something like NVL or IFNULL or COALESCE):

.. || NVL(pp.pro_id,' ') || ...

Regarding your second issue, this is caused by poor database design - kludging several values (person 1, 2 and 3) into a single column. But anyway, can you not get all the required data at once by outer joining to PRO_TEMP 3 times like this:

SUBSTR(record_image,INSTR(RECORD_IMAGE,'^',1,13)+1 ,
(INSTR(RECORD_IMAGE,'^',1,14) - INSTR(RECORD_IMAGE,'^',1,13) -
1)) =Pr1.PRO_EN (+)
AND SUBSTR(record_image,INSTR(RECORD_IMAGE,'^',1,13)+1 ,
(INSTR(RECORD_IMAGE,'^',1,14) - INSTR(RECORD_IMAGE,'^',1,13) -
1)) =Pr2.PRO_EN (+)
AND SUBSTR(record_image,INSTR(RECORD_IMAGE,'^',1,13)+1 ,
(INSTR(RECORD_IMAGE,'^',1,14) - INSTR(RECORD_IMAGE,'^',1,13) -
1)) =Pr3.PRO_EN (+);

... making the necessary changes to the conditions for Pr2 and Pr3?|||Thanks for the reply. I apologize for not including that I have Oracle 81 and am using Sql*Plus.

REPLY TO FIRST ISSUE
I think I was a little burned out Friday. I looked at what I was doing and found that within my first issue, the PRO_ID wasnt really the problem as you figured. The problem is in the WHERE clause. To be exact its WHERE Pr.host='1234'. If I dont include this line but leave PRO_ID, it returns about 150 rows instead of 30 rows (if I include PR.HOST).

I need to include Pr.host='1234' so that I can make sure that the persons info is coming directly from 1234. Unfortunately, Jane Doe and Jonathan Doe can both have the same PRO_IEN of 8765 but each would have a different host. The same number can not appear under the same host number/ID. So, if I dont include host, the database can return a name and data from some other host number that is not 1234.

Do you know if there is a way to fix this? The (+) wont work if I put it next to Pr.host='1234'.

REPLY TO SECOND ISSUE
I tried the outer join option and that didn't work. It didn't return the data that I need (such as names and personal info from those on host 1234). Perhaps whats throwing it off is the fact that each persons (#1 and #2 and #3) identification number would be found in PRO_IEN. Perhaps I have to continue running separate queries (unless someone decides to redesign various tables and applications).

SUBSTR(record_image,INSTR(RECORD_IMAGE,'^',1,13)+1 ,
(INSTR(RECORD_IMAGE,'^',1,14) - INSTR(RECORD_IMAGE,'^',1,13) - 1)) =
Pr.PRO_EN (+) AND
SUBSTR(record_image,INSTR(RECORD_IMAGE,'^',1,13)+1 ,
(INSTR(RECORD_IMAGE,'^',1,16) - INSTR(RECORD_IMAGE,'^',1,13) - 1)) =
Pr.PRO_EN (+) AND
SUBSTR(record_image,INSTR(RECORD_IMAGE,'^',1,13)+1 ,
(INSTR(RECORD_IMAGE,'^',1,18) - INSTR(RECORD_IMAGE,'^',1,13) - 1)) =
Pr.PRO_EN (+);|||1) Use: Pr.host='1234' (+)

2) You have used the same alias "Pr" in all 3 conditions. I used "Pr1", "Pr2" and "Pr3" - you need that same table 3 times in the FROM clause with 3 different aliases.
SUBSTR(record_image,INSTR(RECORD_IMAGE,'^',1,13)+1 ,
(INSTR(RECORD_IMAGE,'^',1,14) - INSTR(RECORD_IMAGE,'^',1,13) - 1)) =
Pr1.PRO_EN (+) AND
SUBSTR(record_image,INSTR(RECORD_IMAGE,'^',1,13)+1 ,
(INSTR(RECORD_IMAGE,'^',1,16) - INSTR(RECORD_IMAGE,'^',1,13) - 1)) =
Pr2.PRO_EN (+) AND
SUBSTR(record_image,INSTR(RECORD_IMAGE,'^',1,13)+1 ,
(INSTR(RECORD_IMAGE,'^',1,18) - INSTR(RECORD_IMAGE,'^',1,13) - 1)) =
Pr3.PRO_EN (+);|||When I try PR.HOST='1234' (+), I get the following error:
ERROR at line 17:
ORA-00933: SQL command not properly ended
The lines below are an example of my FROM.

FROM LOAD_ERRORS LE, I_PRO_TEMP PR
WHERE LE.FILE_TYPE = 'FILE' AND
LE.FILE_NAME LIKE 'test_1234_5678_FILE_2004%' AND
LE.ERROR_CODE = '200' AND
Pr.host='1234' (+) and
SUBSTR(record_image,INSTR(RECORD_IMAGE,'^',1,13)+1 ,
(INSTR(RECORD_IMAGE,'^',1,14) - INSTR(RECORD_IMAGE,'^',1,13) - 1)) =
Pr.PRO_EN (+);|||Actually, I made a slip there - the (+) goes on the other side:

PR.HOST (+) = '1234'

However, I don't know if that is the cause of your syntax error. It could be, I think.|||Thanks! It worked. :Dsql