Clarity

Expand all | Collapse all

I need to add OBS Units and Descendants to a NSQL

  • 1.  I need to add OBS Units and Descendants to a NSQL

    Posted Jan 21, 2013 08:21 AM
    Hi

    I have some code that was developed by someone else. The code is to a custom Portlet and when the User selects a particular OBS in this Portlet, that should return data for all of the Applications (Projects) for that OBS – but in another custom Portlet is where the data is listed. However, it is only returning some Applications. I believe that I need to somehow add the OBS and Descendants. Any suggestions on if this is the case….and how to do so? Thanks for looking!

    Here's a sample of the code:
    WHERE a.id = b.id
    AND b.is_active = 1 AND a.retirement_date is null AND b.odf_object_code = 'application' AND OU.TYPE_ID = 5000009 /*FUNCTIONAL OBS*/ AND A.ID = OBS.RECORD_ID
    AND OBS.UNIT_ID = OU.ID AND OU.ID = E.UNIT_ID AND( E.BRANCH_UNIT_ID IN (SELECT ID FROM PRJ_OBS_UNITS WHERE PARENT_ID = 5000188 /*IT Units*/)
    OR E.BRANCH_UNIT_ID IN (5000841,5001610,5001882)
    AND f.id = a.id


  • 2.  RE: I need to add OBS Units and Descendants to a NSQL

    Posted Jan 21, 2013 08:49 AM
    ]If you search for the table name [font=Courier New][size=7]prj_obs_unit_flat[size][font] on the boards you should find a number of discussions around this area.


  • 3.  RE: I need to add OBS Units and Descendants to a NSQL

    Posted Jan 21, 2013 09:54 AM
    Ok. I'll take a look at what's out there. Thanks Dave!


  • 4.  RE: I need to add OBS Units and Descendants to a NSQL

    Posted Jan 21, 2013 10:04 AM
    Hi Dave,

    would you suggest or agree that the following code is where I need to add the "prj_obs_unit_flat?"

    SELECT B.ID, B.NAME, (SELECT FULL_NAME FROM SRM_RESOURCES WHERE USER_ID = B.MANAGER_ID) MANAGER, A.LIFECYCLE_STATUS, (SELECT NAME FROM PRJ_OBS_UNITS WHERE ID = F.OBS_ID) OBS, DECODE(F.ATTRIB_NOT_COMPLETE,0,'Yes','No') PAGE_STATUS, SUBPAGE


  • 5.  RE: I need to add OBS Units and Descendants to a NSQL

    Posted Jan 21, 2013 10:10 AM
    No it will just be in your "WHERE clause" somewhere.

    That prj_obs_unit_flat table gives you the association between the the OBS ID (that your user has selected) and all the units beneath that in the OBS structure.

    --

    Looking at the "where clause" you originally posted, it looks like it already uses that table (since it uses the branch_unit_id column) though; so you might not have the problem that you think you have! :unsure:


  • 6.  RE: I need to add OBS Units and Descendants to a NSQL

    Posted Jan 21, 2013 10:21 AM
    Oh...yes...ok.

    here?
    WHERE a.id = b.id
    AND b.is_active = 1 AND a.retirement_date is null AND b.odf_object_code = 'application' AND OU.TYPE_ID = 5000009 /*FUNCTIONAL OBS*/ AND A.ID = OBS.RECORD_ID
    AND OBS.UNIT_ID = OU.ID AND OU.ID = E.UNIT_ID AND( E.BRANCH_UNIT_ID IN (SELECT ID FROM PRJ_OBS_UNITS WHERE PARENT_ID = 5000188 /*IT Units*/)
    OR E.BRANCH_UNIT_ID IN (5000841,5001610,5001882 /*medplus, spec lab,insurance*/))
    AND f.id = a.id


  • 7.  RE: I need to add OBS Units and Descendants to a NSQL

    Posted Jan 21, 2013 10:27 AM
    Yes, but my last point is that you must already have that table in your FROM list (aliased as "E").

    So your NSQL logic appears to already be trying to read "down" the OBS structure. :unsure:


  • 8.  RE: I need to add OBS Units and Descendants to a NSQL

    Posted Jan 21, 2013 10:31 AM
    Ok....I didn't read the rest of your post and responded to fast. :-)

    ...so it appears that there is a different issue as to why the code is not returning all of the information the Users expect. As you stated, that information is already in the Where clause. So now...I'm back to the drawing board on what the culprit can be...... Thanks!


  • 9.  RE: I need to add OBS Units and Descendants to a NSQL

    Posted Jan 21, 2013 10:36 AM
    ^ yes it looks that way.

    The usual "tricks" when a query is not returning the records you expect are "bad joins" (where one table is joined to another, but the second table does not have any data) or joins made on columns that contain NULL data. Both these scenarios we can "code for" though - with OUTER JOINS or NULL tests (nvl() / isnull() ).


  • 10.  RE: I need to add OBS Units and Descendants to a NSQL

    Posted Jan 21, 2013 11:37 AM
      |   view attached
    Ok....so...I'm trying to get this resolved by COB today. I'm hoping that I can just "plug in" what is missing and not have to re-write the code.

    Ok....I agree and actually see evidence of what you've stated.

    The functionality, from a Users perspective is as follows:
    A User selects 100% for a category and for a particular OBS in the first Portlet - let's say the category is "Risk." All of the Applications for that OBS that have Risks that have been 100% completed will display - down below in the other Portlet. The way it should work, is that all Applications should display - even the ones that are not 100% complete in the Risk category. Now...this works fine for the ones that are not 100% complete- lets say if I select on 75% - All of the Applications show up in the below Portlet. I've attached a sample file....what's your thoughts? Thanks again for your input!!!

    I'm especially looking at this particular sample of the Union of the Code.

    "NVL2(partition_code,0,1)+NVL2(qd_it_pw_reset,0,1) ATTRIB_NOT_COMPLETE /*0 means false*/"

    Attachment(s)

    txt
    Sample Code_1-20-13.txt   8 KB 1 version


  • 11.  RE: I need to add OBS Units and Descendants to a NSQL

    Posted Jan 21, 2013 12:05 PM
    That code sample is a bit more complicated than I can just "look at" and spot any errors and I can not just "debug" it for you.

    What does looks odd to me is all the hard-coded OBS ids - that logic does not look parameter-driven at all? Are you choosing (new) parameter OBSs outside of that hardcoded list - the code only appears to pick up OBSs that are sub-levels of id 5000188 or one of the 3 explicit ids (5000841,5001610,5001882) and all that looks a bit strange to me logic-wise.

    Nor can I spot evidence of your 100% / 75% parameter either - so I don't really know what to say!. :sad

    I'd be inclined to try to work out what is different between the scenario that shows all applications compared to the one that does not and then try to work out "what it is" about those applications (for example they are in a new OBS area?) that are not shown under that second scenario rather than looking at the code? :unsure:

    :wacko:


  • 12.  RE: I need to add OBS Units and Descendants to a NSQL

    Posted Jan 21, 2013 12:29 PM
    Yeah....it is complicated code...and hard to just debug.

    Hmmm...didn't think about the OBS being hard-coded. However, your direction makes sense. Thanks for looking!!