Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2004
    Posts
    13

    Unanswered: 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) wonít 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 didnít 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 Iíve 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, Iíd 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 #1ís data.

    I think there has to be an easier way. Now, when I tried UNION (long before the modified query in issue one), I wouldnít get back the number of results that I shouldíve 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. Iíd 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 3ís 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.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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:

    Code:
    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:
    Code:
     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?

  3. #3
    Join Date
    Jul 2004
    Posts
    13

    Thanks for your reply.

    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 wasnít really the problem as you figured. The problem is in the WHERE clause. To be exact itís WHERE Ö Pr.host='1234'. If I donít 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 personís 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 donít 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 (+) wonít 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 whatís throwing it off is the fact that each personís (#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 (+);

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    1) Use:
    Code:
    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 (+);

  5. #5
    Join Date
    Jul 2004
    Posts
    13

    Thanks again for your help

    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 (+);

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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.

  7. #7
    Join Date
    Jul 2004
    Posts
    13

    Talking Thanks!

    Thanks! It worked.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •