Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jun 2013
    Posts
    14

    Unanswered: Oracle SQL query: Finding the number of counts of similar objects between parent proj

    I am stuck on this part.

    How to find the child project of a parent project which is passed in the 'WHERE' clause and also, to find the duplicate objects.

    I have prepared a query:

    SELECT A.projectName as PARENT,
    (select COUNT(*) from PSPROJECTITEM WHERE PROJECTNAME = A.PROJECTNAME) parentprojecount,
    B.ProjectName as CHILD,
    (select COUNT(*) from PSPROJECTITEM WHERE PROJECTNAME = B.PROJECTNAME) CHILDPROJECT,
    (select count(*) from PSPROJECTITEM A
    LEFT OUTER join PSPROJECTITEM B
    on b.objecttype = a.objecttype
    AND b.objectid1 =a.objectid1
    AND b.objectvalue1 = a.objectvalue1
    AND b.objectid2 = a.objectid2
    AND b.objectvalue2 = a.objectvalue2
    AND b.objectid3 = a.objectid3
    AND b.objectvalue3 = a.objectvalue3
    AND b.objectid4 = a.objectid4
    AND B.OBJECTVALUE4 = A.OBJECTVALUE4
    AND A.PROJECTNAME = 'AAAA_JOB_KJ'
    WHERE A.PROJECTNAME <> B.PROJECTNAME
    )
    AS SIMILAROBJECTCOUNTPARENTAND CHILD
    from psprojectitem a INNER JOIN psProjectItem B
    ON a.objecttype = b.objecttype
    AND a.objectid1 =b.objectid1
    AND a.objectvalue1 = b.objectvalue1
    AND a.objectid2 = b.objectid2
    AND a.objectvalue2 = b.objectvalue2
    AND a.objectid3 = b.objectid3
    AND a.objectvalue3 = b.objectvalue3
    AND a.objectid4 = b.objectid4
    AND a.objectvalue4 = b.objectvalue4
    WHERE A.projectname in
    (SELECT ProjectName from psProjectDefn WHERE lastupdoprid <> 'PPLSOFT')
    AND a.projectname <> B.projectName
    and A.PROJECTNAME = 'AAAA_JOB_KJ'
    group by A.PROJECTNAME,B.PROJECTNAME
    ORDER BY B.PROJECTNAME
    I am stuck on the part where i need to find the count of similar objects which are similar between the parent and child.

    For eg, here i am passing 'AAAA_JOB_KJ' in the where clause, which is my parent project. The query retrieves the parent project name, count of parent project name, child project of this project name, count of the child project. All this is find.

    My real problem is that query which I have written is not fetching the correct count for the similar objects which are between the parent and the child.

    I will post the details below:

    The column names of PSPROJECTITEM:

    PROJECTNAME OBJECTTYPE OBJECTID1 OBJECTVALUE1 OBJECTID2 OBJECTVALUE2 OBJECTID3 OBJECTVALUE3 OBJECTID4 OBJECTVALUE4
    The desired output:

    ParentProjectName ParentProjectCount ChildProject Child Count Similar Object Count
    This is the header of the file, can be ignored. This part is the output:

    AAAA_JOB_KJ 199 AZ_AUTOFILL_SP1 11 3
    The output returned by my query:

    AAAA_JOB_KJ 199 AZ_AUTOFILL_SP1 11 945
    The rest of the data is fine, except for the similar object count.

    The objectcount have to be checked between the AAAA_JOB_KJ and AZ_AUTOFILL_SP1, and the count of the similar objects amongst them have to be retrieved, I am stuck on this part.

    What I have tried here is that I have tried to map the parent object in the left outer join, so that the object present in table A and in table B gets retrieved. This is how I am trying to fetch the duplicate rows.

    Please don't hesitate in calling me wrong, I am open to all suggestions.

    Also, edit this post, as I am not aware on how to do this.

    Database in use is Oracle.

    Kindly let me know if further information is required.

    Thanks a lot for your time and help.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    realize that since we don't have your tables or data, we can not run, test, or improve posted SQL.

    >My real problem is that query which I have written is not fetching the correct count for the similar objects which are between the parent and the child.

    what criteria must be met for an object to be considered "between the parent & the child"?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2013
    Posts
    14
    Okay, I will give the table structure tomorrow.

    The table is PSPROJECTITEM.

    But I need to give the data to you.

    Thanks

  4. #4
    Join Date
    Jun 2013
    Posts
    14
    Hi,

    Please find this:

    The query result is as follows:

    SELECT * FROM PSPROJECTITEM WHERE projectname = 'AAAA_JOB_KJ';
    One such row in this query is:

    AAAA_JOB_KJ 8 1 JOB 2 EMPL_RCD 12 SavePostChange 0
    Similarly:

    SELECT * FROM PSPROJECTITEM WHERE PROJECTNAME = 'AZ_AUTOFILL_SP1';
    One such row is:

    AZ_AUTOFILL_SP1 8 1 JOB 2 EMPL_RCD 12 SavePostChange 0

    Hence, it is clear that the one row is common amongst AAAA_JOB_KJ and AZ_AUTOFILL_SP1, which both have parent and child relationship. So, the count of similar objects amongst them is 1, though, it is more than one for these two table.

    A Generic query will do.

    Thanks.
    Last edited by peterparker; 06-17-13 at 01:51. Reason: Missed information

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >A Generic query will do.

    Code:
    SELECT COUNT(*) FROM DUAL;
    Is above generic enough for you?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Jun 2013
    Posts
    14
    Hi Anacedent,

    Thanks for the response.

    By generic, i meant for this case, where the similar count will get computed.

    I have updated the thread, let me know if you need any other information, as I have access to DB right now.

  7. #7
    Join Date
    Jun 2013
    Posts
    14
    Quote Originally Posted by anacedent View Post
    >A Generic query will do.

    Code:
    SELECT COUNT(*) FROM DUAL;
    Is above generic enough for you?

    There are total of 3 rows which are similar between these two PROJECTNAME 'AAAA_JOB_KJ' and 'AZ_AUTOFILL_SP1'.

    I want the query which will fetch the similar number of counts between these two projectname, where the former one is the parent and the later one is the child.

    Thanks

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You have tables. We don't
    You have data. We don't
    You have requirements. We don't
    You have expected/desired results. We don't.
    If you can not write the SQL having all of the above, why do you expect we can produce SQL when you can not do so?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Jun 2013
    Posts
    14
    Quote Originally Posted by anacedent View Post
    You have tables. We don't
    You have data. We don't
    You have requirements. We don't
    You have expected/desired results. We don't.
    If you can not write the SQL having all of the above, why do you expect we can produce SQL when you can not do so?
    I am ready to post the table data here.

    Kindly let me know on how to do that, because one table row has 199 columns, though for the other, it is 11 only.

    Thanks

  10. #10
    Join Date
    Jun 2013
    Posts
    14
    Is it possible to attach a zip file here?

    I will post the entire table data and table here, if possible. thanks

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >because one table row has 199 columns,
    My initial reaction is that this table & likely whole application does not conform to Third Normal Form.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Jun 2013
    Posts
    14
    Quote Originally Posted by anacedent View Post
    >because one table row has 199 columns,
    My initial reaction is that this table & likely whole application does not conform to Third Normal Form.
    It is a delivered table from the peoplesoft, PSPROJECTITEM.

    The client has put there project names, which are customized.

    I am exporting the data in an xls file, and will put the zip here.

  13. #13
    Join Date
    Jun 2013
    Posts
    14
    Sorry, I made a mistake.

    For one project name, there are 199 rows, and for the other, there are 11 rows.

  14. #14
    Join Date
    Jun 2013
    Posts
    14
    Hi,

    I have the zip file ready with me, kindly let me know on how to upload it.

    It contains the data exported from the table in a csv file.

    Thanks

  15. #15
    Join Date
    Jun 2013
    Posts
    14
    Attached is the file.
    Attached Files Attached Files

Posting Permissions

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