Clarity

  • 1.  SQL query duplicated on project attached to program or not

    Posted Feb 19, 2019 05:13 AM

    Hello all,

     

    I am building a query to retrieve all projects attached to program or not. The query is working fine but gives duplicated results, one line with the program name and the second with 'null'.

    here is the simple query :

    SELECT II_PRJ.NAME AS PROJECT,

    II_PRG.NAMEASPROGRAM

    FROMINV_INVESTMENTS II_PRJ

    LEFTOUTERJOININV_PROJECTS IP_PRJ ONII_PRJ.ID=IP_PRJ.PRID ANDIP_PRJ.IS_PROGRAM=0

    LEFTOUTERJOININV_HIERARCHIES II_LINK ONII_PRJ.ID=II_LINK.CHILD_ID

    LEFTOUTERJOININV_INVESTMENTS II_PRG ONII_LINK.PARENT_ID=II_PRG.ID

    LEFTOUTERJOININV_PROJECTS IP_PRG ONII_PRJ.ID=IP_PRG.PRID ANDIP_PRG.IS_PROGRAM=1

    WHEREII_PRJ.CODE IN('2016-AAE','PRJ000571')

     

    Thank you for your help.

     

    Karim

     



  • 2.  Re: SQL query duplicated on project attached to program or not

    Posted Feb 19, 2019 05:49 AM

    Would guess that adding

    and II_LINK.PARENT_ID is not null

    to the join on INV_HIERARCHIES would do it.



  • 3.  Re: SQL query duplicated on project attached to program or not

    Posted Feb 19, 2019 05:51 AM

    Thank you for your response but I want to get projects which are not attached to a program that is why I did not filter on PARENT_ID IS NOT NULL.



  • 4.  Re: SQL query duplicated on project attached to program or not

    Posted Feb 19, 2019 05:57 AM

    Your SQL is showing projects attached to a program - so you need some SQL that either subtracts that result set from the set of all projects or you would need your SQL built with some "NOT EXISTS" logic.



  • 5.  Re: SQL query duplicated on project attached to program or not
    Best Answer

    Posted Feb 19, 2019 11:56 PM

    The INV_HIERARCHIES table will always include a record for all projects where PARENT_ID is null, even if they are linked to a Parent.

     

    Try:
    SELECT II_PRJ.NAME AS PROJECT
    FROM INV_INVESTMENTS II_PRJ
    JOIN INV_PROJECTS IP_PRJ ON II_PRJ.ID=IP_PRJ.PRID AND IP_PRJ.IS_PROGRAM=0
    where not exists (select 1 from INV_HIERARCHIES II_LINK
                    JOIN INV_INVESTMENTS II_PRG ON II_LINK.PARENT_ID=II_PRG.ID
                    JOIN INV_PROJECTS IP_PRG ON II_PRJ.ID=IP_PRG.PRID AND IP_PRG.IS_PROGRAM=1
                    WHERE II_PRJ.ID=II_LINK.CHILD_ID)
    if you want to find 'Projects' not linked to a 'Program'.


    You may also want to consider adding some 'IS_ACTIVE = 1' for both the 'Project' and 'Program' so that you focus on only Active Projects, and if they have been assigned to an Active Program.



  • 6.  Re: SQL query duplicated on project attached to program or not

    Posted Feb 20, 2019 03:15 AM

    Thanks Roland for your response. The query works fine and returns all projets with a program or not but I have a last question is it possible to add in my select the Program Name even if it will be null in some projects.



  • 7.  Re: SQL query duplicated on project attached to program or not

    Posted Feb 20, 2019 05:39 AM

    Thanks again Roland I have found a way to get the Program Name.