Results 1 to 3 of 3

Thread: query help

  1. #1
    Join Date
    Jul 2003
    Posts
    70

    Unanswered: query help

    Dear friends,
    I am new to Oracle SQL, there is a query :

    SELECT E.Attribute6 as EmpNo, E.Name,
    Wk.WEEK,
    SUM(PEIA.QUANTITY) as Hours
    FROM PER_ALL_PEOPLE_F E,
    PA_EXPENDITURE_ITEMS_ALL PEIA,
    PA_EXPENDITURES_ALL PEA,
    PA_PROJECTS_ALL PPA,
    DISC_Week Wk
    WHERE E.PERSON_ID = PEA.INCURRED_BY_PERSON_ID
    AND PEIA.EXPENDITURE_ID = PEA.EXPENDITURE_ID
    AND PPA.PROJECT_ID = PEIA.PROJECT_ID
    AND Wk.WEEK_DATE = PEIA.EXPENDITURE_ITEM_DATE
    AND E.PERSON_ID=193
    GROUP BY E.Attribute6, E.FULL_NAME,
    Wk.WEEK

    The output is:

    EmpNo Name Week Hours
    QI_0453 Anand W-1 85
    QI_0453 Anand W-2 45.5
    QI_0453 Anand W-3 62
    QI_0453 Anand W-4 47
    QI_0453 Anand W-5 48
    QI_0453 Anand W-6 52


    To this output, I want to add one more column vacation hours. It may happen that there may not be any data for vacation hours for a particular week, so the output should appear like this:

    EmpNo Name Week Hours vacation
    QI_0453 Anand W-1 85 0
    QI_0453 Anand W-2 45.5 0
    QI_0453 Anand W-3 62 0
    QI_0453 Anand W-4 47 10
    QI_0453 Anand W-5 48 0
    QI_0453 Anand W-6 52 9


    If i rewrite the query like this:
    SELECT E.Attribute6 as EmpNo, E.Name,
    Wk.WEEK,
    SUM(PEIA.QUANTITY) as Hours,
    SUM(PEIA_v.QUANTITY) as Vacation
    FROM PER_ALL_PEOPLE_F E,
    PA_EXPENDITURE_ITEMS_ALL PEIA,
    PA_EXPENDITURES_ALL PEA,
    PA_PROJECTS_ALL PPA,
    DISC_Week Wk,
    PA_EXPENDITURE_ITEMS_ALL PEIA_v,
    PA_EXPENDITURES_ALL PEA_v,
    (SELECT * FROM PA_PROJECTS_ALL WHERE PROJECT_ID IN(496,497,499,502,507)) PPA_v
    WHERE E.PERSON_ID = PEA.INCURRED_BY_PERSON_ID
    AND PEIA.EXPENDITURE_ID = PEA.EXPENDITURE_ID
    AND PPA.PROJECT_ID = PEIA.PROJECT_ID
    AND Wk.WEEK_DATE = PEIA.EXPENDITURE_ITEM_DATE
    AND E.PERSON_ID = PEA_v.INCURRED_BY_PERSON_ID
    AND PEIA_v.EXPENDITURE_ID = PEA_v.EXPENDITURE_ID
    AND PPA_v.PROJECT_ID = PEIA_v.PROJECT_ID
    AND Wk.WEEK_DATE = PEIA_v.EXPENDITURE_ITEM_DATE
    AND E.PERSON_ID=193
    GROUP BY E.Attribute6, E.FULL_NAME,
    Wk.WEEK

    I just get one row (probably i need to do outer join).
    Can someone help me with the query??

    Thanks in advance
    qA

  2. #2
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    SELECT E.Attribute6 as EmpNo, E.Name,
    Wk.WEEK,
    SUM(PEIA.QUANTITY) as Hours.
    sum(case when PEIA.PROJECT_ID in (496,497,499,502,507) then PEIA.QUANTITY else 0 end) Vacation
    FROM PER_ALL_PEOPLE_F E,
    PA_EXPENDITURE_ITEMS_ALL PEIA,
    PA_EXPENDITURES_ALL PEA,
    PA_PROJECTS_ALL PPA,
    DISC_Week Wk
    WHERE E.PERSON_ID = PEA.INCURRED_BY_PERSON_ID
    AND PEIA.EXPENDITURE_ID = PEA.EXPENDITURE_ID
    AND PPA.PROJECT_ID = PEIA.PROJECT_ID
    AND Wk.WEEK_DATE = PEIA.EXPENDITURE_ITEM_DATE
    AND E.PERSON_ID=193
    GROUP BY E.Attribute6, E.FULL_NAME,
    Wk.WEEK


    something like this will work I think in 9.0 and later

    I would suggest using the join syntax just a preference.
    If you use the from where method of joining and outer join is represented but putting (+) on the deficient column

  3. #3
    Join Date
    Jul 2003
    Posts
    70
    Hi,
    That was really a wonderful query solving my purpose.


    Thank you very much.

    Regards,
    qA

Posting Permissions

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