Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2005
    Posts
    5

    Question Unanswered: problem when addin an extra field to a select

    I have a select statement that works fine:
    SELECT DISTINCT E.EmpNo, E.FirstName, E.Surname, A.StartDate, A.EndDate, Round((A.DurDays), 2) AS Days, Round((A.DurMins/60), 2) AS Hours, (K.Name) AS LeaveType,
    decode (A.cost, '!!!!', 0, '!!#*', 1, '!!$1', 2, '!!%8', 3, '!!&?', 4, '!!(F', 5, '!!)M', 6,
    '!!*T', 7, '!!+[', 8, '!!,b', 9, '!!-i', 10, '!!.p', 11, '!!/w', 12,
    '!!0~', 13, '!!2(', 14, 25) as CostDays
    from
    clockwise.Employee E, clockwise.Absence A, clockwise.AbsKeys K,
    ADMIN.BASIC_DETAILS@cwselinkptec c, ADMIN.EMP_POST_DETAILS@cwselinkptec d, ADMIN.POST_DETAILS@cwselinkptec f
    WHERE E.EmpID = A.EmpID AND Trim(E.EmpNo) = Trim(c.EMPLOYEE_NUMBER)
    AND A.StartDate >= to_date('01-12-2004','dd-mm-yyyy') AND A.StartDate <= to_date('01-01-2005','dd-mm-yyyy')
    AND A.AbsKeyID = K.AbsKeyID AND (K.AbsKeyID = '0' OR K.AbsKeyID = '1') AND c.EMPLOYEE_NUMBER = d.EMPLOYEE_NUMBER
    AND d.LINK_EFF_LINK = f.LINK_EFF_LINK AND f.post_location = '4106'
    ORDER BY E.Surname ASC, A.StartDate

    I get 26 rows returned. I need to add an extra field to the select. The Reason field on the Absence table contains a code, the value for this code, which I need, is stored in the Choices table. The Choices table has KEYID and ITEMID as the Primary Key. Reason field links to the ITEMID field.


    When I run the following sql:
    SELECT DISTINCT E.EmpNo, E.FirstName, E.Surname, A.StartDate, A.EndDate, Round((A.DurDays), 2) AS Days, Round((A.DurMins/60), 2) AS Hours, (K.Name) AS LeaveType,
    decode (A.cost, '!!!!', 0, '!!#*', 1, '!!$1', 2, '!!%8', 3, '!!&?', 4, '!!(F', 5, '!!)M', 6,
    '!!*T', 7, '!!+[', 8, '!!,b', 9, '!!-i', 10, '!!.p', 11, '!!/w', 12,
    '!!0~', 13, '!!2(', 14, 25) as CostDays, (B.Name) as SLReason
    FROM clockwise.Employee E, clockwise.Absence A, clockwise.AbsKeys K, clockwise.choices B,
    ADMIN.BASIC_DETAILS@cwselinkptec c, ADMIN.EMP_POST_DETAILS@cwselinkptec d, ADMIN.POST_DETAILS@cwselinkptec f
    WHERE E.EmpID = A.EmpID AND Trim(E.EmpNo) = Trim(c.EMPLOYEE_NUMBER)
    AND A.StartDate >= to_date('01-12-2004','dd-mm-yyyy') AND A.StartDate <= to_date('01-01-2005','dd-mm-yyyy')
    AND A.AbsKeyID = K.AbsKeyID AND (K.AbsKeyID = '0' OR K.AbsKeyID = '1') AND c.EMPLOYEE_NUMBER = d.EMPLOYEE_NUMBER
    AND d.LINK_EFF_LINK = f.LINK_EFF_LINK AND f.post_location = '4106'
    AND B.KeyID = 'R%' AND B.ItemID = A.Reason
    ORDER BY E.Surname ASC, A.StartDate

    I get 1 row, the only person from the previous select who has an entry in the Choices table. I understand why.

    When I run the following sql:
    SELECT DISTINCT E.EmpNo, E.FirstName, E.Surname, A.StartDate, A.EndDate, Round((A.DurDays), 2) AS Days, Round((A.DurMins/60), 2) AS Hours, (K.Name) AS LeaveType,
    decode (A.cost, '!!!!', 0, '!!#*', 1, '!!$1', 2, '!!%8', 3, '!!&?', 4, '!!(F', 5, '!!)M', 6,
    '!!*T', 7, '!!+[', 8, '!!,b', 9, '!!-i', 10, '!!.p', 11, '!!/w', 12,
    '!!0~', 13, '!!2(', 14, 25) as CostDays, SLReason
    FROM (SELECT (B.Name) SLReason
    FROM clockwise.choices B,
    clockwise.Absence A
    WHERE B.KeyID = 'R%'
    AND B.ItemID = A.Reason),
    clockwise.Employee E, clockwise.Absence A, clockwise.AbsKeys K,
    ADMIN.BASIC_DETAILS@cwselinkptec c, ADMIN.EMP_POST_DETAILS@cwselinkptec d, ADMIN.POST_DETAILS@cwselinkptec f
    WHERE E.EmpID = A.EmpID AND Trim(E.EmpNo) = Trim(c.EMPLOYEE_NUMBER)
    AND A.StartDate >= to_date('01-12-2004','dd-mm-yyyy') AND A.StartDate <= to_date('01-01-2005','dd-mm-yyyy')
    AND A.AbsKeyID = K.AbsKeyID AND (K.AbsKeyID = '0' OR K.AbsKeyID = '1') AND c.EMPLOYEE_NUMBER = d.EMPLOYEE_NUMBER
    AND d.LINK_EFF_LINK = f.LINK_EFF_LINK AND f.post_location = '4106'
    ORDER BY E.Surname ASC, A.StartDate

    I get 104 rows, each person from the first select now appears 4 times with 1 of 4 different reasons.

    How do I get 26 rows with just the 1 person showing his reason and the 25 other's having blank reasons.

    Thanks for any help.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Have a look at the Join predicate. Rather than define you realtionship using a where clause use a join. The join can cater for the situation where the value is null.

    Sorry, don't have time to examine id detail your SQL but will try to later on tonight, that is if no one else has come up with a better answer in the mean time.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What you need is a LEFT JOIN.

    Based on the SQL that you've posted, you appear to be using Oracle, but I can't tell which version. If your Oracle is current enough to support the SQL-92 syntax, I'd strongly suggest that you switch to it because it makes a lot of things easier than the SQL-89 syntax that you are using in this query.

    If you need more help, please post your Oracle version and whatever question(s) you might have.

    -PatP

  4. #4
    Join Date
    Jan 2005
    Posts
    5
    I'm using Oracle8i, since I posted I've been trying to use a join (left outer) with no success so far.

    Thanks for the info, I'll keep trying.

  5. #5
    Join Date
    Jan 2005
    Posts
    5
    Got it working by totally ignoring the choices table. I was getting more comlicated than I needed, I just added a decode for the Reason field:
    SELECT DISTINCT E.EmpNo, E.FirstName, E.Surname, A.StartDate, A.EndDate, Round((A.DurDays), 2) AS Days, Round((A.DurMins/60), 2) AS Hours, (K.Name) AS LeaveType,
    decode(A.Cost, '!!!!', 0, '!!#*', 1, '!!$1', 2, '!!%8', 3, '!!&?', 4, '!!(F', 5, '!!)M', 6,
    '!!*T', 7, '!!+[', 8, '!!,b', 9, '!!-i', 10, '!!.p', 11, '!!/w', 12,
    '!!0~', 13, '!!2(', 14, 25) as CostDays,
    Decode(A.Reason, '!', 'Certified', '#', 'Hospitalised', '$', 'Uncertified', '%', 'Unwell',
    '&', 'Throat Infection', Null) as SLReason
    FROM clockwise.Employee E, clockwise.Absence A, clockwise.AbsKeys K,
    ADMIN.BASIC_DETAILS@cwselinkptec c, ADMIN.EMP_POST_DETAILS@cwselinkptec d, ADMIN.POST_DETAILS@cwselinkptec f
    WHERE E.EmpID = A.EmpID AND Trim(E.EmpNo) = Trim(c.EMPLOYEE_NUMBER)
    AND A.StartDate >= to_date('01-12-2004','dd-mm-yyyy') AND A.StartDate <= to_date('01-01-2005','dd-mm-yyyy')
    AND A.AbsKeyID = K.AbsKeyID AND (K.AbsKeyID = '0' OR K.AbsKeyID = '1') AND c.EMPLOYEE_NUMBER = d.EMPLOYEE_NUMBER
    AND d.LINK_EFF_LINK = f.LINK_EFF_LINK AND f.post_location = '4106'
    ORDER BY E.Surname ASC, A.StartDate

    I got the 26 rows with the correct value.

    Thanks for the help,
    Mark.

Posting Permissions

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