Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2005
    Location
    Valladolid, Spain
    Posts
    110

    Unanswered: left join hangs the pc

    hello fellows.

    I got a query that hangs the computer so i want to improve it. The sql is in oracle.

    The thing is that I got a table named mig_adm_historias that hold all the persons and a few tables that hold other information such a telephone number of the person and other things.

    I want to retrieve all the information of the persons in one result set even they do not have phone numbers.

    The sql i got is:

    SELECT P.STD_ID_PERSON, P.STD_N_FAM_NAME_1, P.STD_N_MAIDEN_NAME,
    P.STD_N_FIRST_NAME, P.STD_ID_GENDER, P.STD_DT_BIRTH,
    S.SSP_PROV_NUM_SS, S.SSP_NUM_SS, S.SSP_DIG_NUM_SS,
    XX.STD_NAT_REG_CODE, XX.STD_PHONE, A.STD_ID_SUB_GEO_DIV
    FROM STD_PERSON P, STD_HR S, MIG_ADM_HISTORIAS H, STD_ADDRESS A,
    (SELECT F.ID_ORGANIZATION, F.STD_ID_PERSON,
    F.STD_NAT_REG_CODE, F.STD_PHONE
    FROM STD_PHONE_FAX F INNER JOIN
    (SELECT STD_ID_PERSON, MAX(STD_OR_PHONE) AS MAX
    FROM STD_PHONE_FAX GROUP BY STD_ID_PERSON) X
    ON F.STD_ID_PERSON = X.STD_ID_PERSON AND
    F.STD_OR_PHONE = X.MAX) XX
    WHERE FC_QUITAR_LETRA_DNI(P.STD_ID_PERSON) =
    RG_DNI_N AND S.STD_ID_HR = P.STD_ID_PERSON
    AND
    XX.STD_ID_PERSON = P.STD_ID_PERSON AND
    A.STD_ID_PERSON = P.STD_ID_PERSON;


    p.s: fc_quitar_letra_dni() is a function that takes out a letter because sdd_id_person is an 8 caracter containing numbers and rg_dni_n has 8 numbers and a letter at the end.


    Hope some one can help me!!

    Thanks
    J
    Juan Norton Alea
    DB2 v8.2 for win2k3 (fixpack 9)

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Code:
    SELECT 
            P.STD_ID_PERSON
    ,       P.STD_N_FAM_NAME_1
    ,       P.STD_N_MAIDEN_NAME
    ,       P.STD_N_FIRST_NAME
    ,       P.STD_ID_GENDER
    ,       P.STD_DT_BIRTH 
    ,       S.SSP_PROV_NUM_SS
    ,       S.SSP_NUM_SS
    ,       S.SSP_DIG_NUM_SS
    ,       XX.STD_NAT_REG_CODE
    ,       XX.STD_PHONE
    ,       A.STD_ID_SUB_GEO_DIV
    FROM    STD_PERSON P
    ,       STD_HR S
    ,       MIG_ADM_HISTORIAS H
    ,       STD_ADDRESS A
    ,       (SELECT  F.ID_ORGANIZATION
             ,       F.STD_ID_PERSON
             ,       F.STD_NAT_REG_CODE
             ,       F.STD_PHONE 
             FROM    STD_PHONE_FAX F 
             ,      (SELECT   STD_ID_PERSON, MAX(STD_OR_PHONE) AS MAX 
                     FROM     STD_PHONE_FAX 
                     GROUP BY STD_ID_PERSON) X
             WHERE  F.STD_ID_PERSON = X.STD_ID_PERSON AND 
                    F.STD_OR_PHONE  = X.MAX) XX 
    WHERE SUBSTR(P.STD_ID_PERSON,1,8) = H.RG_DNI_N 
    AND   P.STD_ID_PERSON = S.STD_ID_HR 
    AND   P.STD_ID_PERSON = XX.STD_ID_PERSON 
    AND   P.STD_ID_PERSON = A.STD_ID_PERSON;
    All I did was,
    Re-write your query to something readable!!!

    You might be able to take out the MIG_ADM_HISTORIAS table, as you are not using it for anything but joining it.
    Losing that function or substr all together.

    And you are not outerjoining at all! Other than that I guess I wasnt much help

  3. #3
    Join Date
    Mar 2005
    Location
    Valladolid, Spain
    Posts
    110

    Angry

    Hi, thanks for the readable thing I guess when I copy paste it got like that.

    I had tryed your code and works fine but what I want is to retrieve all the information of mig_adm_historias and the related data; I guess a outer join will help, but when I do it the machine takes like 3 minutes to do it and is not giving me all the rows from mig_adm_historias

    What I did is:

    SELECT
    P.STD_ID_PERSON
    , P.STD_N_FAM_NAME_1
    , P.STD_N_MAIDEN_NAME
    , P.STD_N_FIRST_NAME
    , P.STD_ID_GENDER
    , P.STD_DT_BIRTH
    , S.SSP_PROV_NUM_SS
    , S.SSP_NUM_SS
    , S.SSP_DIG_NUM_SS
    , XX.STD_NAT_REG_CODE
    , XX.STD_PHONE
    , A.STD_ID_SUB_GEO_DIV
    FROM STD_PERSON P right outer join MIG_ADM_HISTORIAS H
    on fc_quitar_letra_dni(P.STD_ID_PERSON) = H.RG_DNI_N
    , STD_HR S
    , STD_ADDRESS A
    , (SELECT F.ID_ORGANIZATION
    , F.STD_ID_PERSON
    , F.STD_NAT_REG_CODE
    , F.STD_PHONE
    FROM STD_PHONE_FAX F
    , (SELECT STD_ID_PERSON, MAX(STD_OR_PHONE) AS MAX
    FROM STD_PHONE_FAX
    GROUP BY STD_ID_PERSON) X
    WHERE F.STD_ID_PERSON = X.STD_ID_PERSON AND
    F.STD_OR_PHONE = X.MAX) XX
    WHERE P.STD_ID_PERSON = S.STD_ID_HR
    AND P.STD_ID_PERSON = XX.STD_ID_PERSON
    AND P.STD_ID_PERSON = A.STD_ID_PERSON

    p.s.: I am trying to put black spaces so the query will be readable like the one up but the page erase the blank spaces :-s
    Juan Norton Alea
    DB2 v8.2 for win2k3 (fixpack 9)

  4. #4
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Juan, if you enclose your text within the code tags it will appear as above. It is the "#" icon above the message box on the right hand side. Simply highlight your query/text and then click on the icon.

    Without the CODE tags, a query appears like this no matter how the original is formatted:

    SELECT *
    FROM CUSTOMERS
    WHERE emp_id=101;

    But with the CODE tags, you can retain format and use a box to offset the code:

    Code:
    SELECT *
      FROM CUSTOMERS
     WHERE emp_id=101;
    JoeB
    save disk space, use smaller fonts

  5. #5
    Join Date
    Mar 2005
    Location
    Valladolid, Spain
    Posts
    110

    Thumbs up

    Quote Originally Posted by joebednarz
    Juan, if you enclose your text within the code tags it will appear as above. It is the "#" icon above the message box on the right hand side. Simply highlight your query/text and then click on the icon.

    Without the CODE tags, a query appears like this no matter how the original is formatted:

    Code:
    SELECT *
    FROM CUSTOMERS
    WHERE emp_id=101;
    But with the CODE tags, you can retain format and use a box to offset the code:

    Code:
    SELECT *
      FROM CUSTOMERS
     WHERE emp_id=101;
    THANKS !!
    Juan Norton Alea
    DB2 v8.2 for win2k3 (fixpack 9)

Posting Permissions

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