Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2007
    Posts
    3

    Unanswered: Please help in this SQL (I am new to DB2)

    I am writing stored procedure for my report to get the results from the 5 tables.


    My SQL is :

    SELECT rgn.Region,biz.Rbu,usr.User,biz.BizGroup,per.Role, eff.ApproverStarDate,eff.ApproverEndDate
    FROM Region_08 rgn,
    User_07 usr,
    Person_26 per,
    Effec_27 eff,
    Biz_09 biz
    where rgn.jde_id =eff.jde_id
    and usr.per_id = per.per_id
    and eff.biz_id = biz.biz_id
    and per.per_role_id = eff.per_role_id
    and per.role IN ('Requestor' ,'BuApprover')
    Order by rgn.Region,biz.Rbu,usr.User

    Results:
    NorthAmerica AB John 239 Requestor 01/03/2007 null
    NorthAmerica MEAT Peter 239 BuApprover 06/03/2007 null
    NorthAmrica MEAT Peter 239 BuApprover 04/03/2007 null
    LatinAmerica GOAT chip 123 Requestor 03/02/2007 4/04/2007
    LatinAmerica GOAT chip 145 BuApprover 03/05/2007 null
    LatinAmerica GOAT chip 146 BuApprover 03/08/2007 null
    NorthAmerica PORK Peter 239 Requestor 04/03/2007 null
    NorthAmerica PORK Peter 146 BuApprover 05/06/2007 null
    NorthAmerica MA John 239 Requestor 01/03/2007 null
    NorthAmerica MA John 239 Requestor 01/05/2007 null
    NorthAmerica MA John 239 Requestor 01/07/2007 null

    But above results are not correct for me.Because my report requires List of Users that are both in Requestors and BuApprovers.

    Expected Results are :

    LatinAmerica GOAT chip 123 Requestor 03/02/2007 4/04/2007
    LatinAmerica GOAT chip 145 BuApprover 03/05/2007 null
    LatinAmerica GOAT chip 146 BuApprover 03/08/2007 null
    NorthAmerica PORK Peter 239 Requestor 04/03/2007 null
    NorthAmerica PORK Peter 146 BuApprover 05/06/2007 null

    Explanation:
    I don't want to see any user having only one Requestor or One BuApprover. If any user having Requestor and also he should have atleast one BuApprover then show me all the records to that perticular user.

    Please let me know if you have any questions. But your help much appreciated.

    Thanks
    Remata.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    So then you should add a predicate in the WHERE clause like:
    Code:
    WHERE user IN ( SELECT ... FROM Requestor  WHERE ... ) AND
          user IN ( SELECT ... FROM BuApprover WHERE ... )
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This should also work:
    Code:
    SELECT rgn.Region, biz.Rbu, usr.User, biz.BizGroup, per.Role, 
    	eff.ApproverStarDate, eff.ApproverEndDate
    FROM Region_08 rgn,
    	(
    	(SELECT per.per_id,
    		per.per_role_id,
    		per.role
    	FROM Person_26
    	WHERE per.role = 'Requestor')
    
    	INTERSECT 
    
    	(SELECT per.per_id,
    		per.per_role_id,
    		per.role
    	FROM Person_26
    	WHERE per.role = 'BuApprover')
    	) as per
    	User_07 usr,
    	Effec_27 eff,
    	Biz_09 biz
    where rgn.jde_id = eff.jde_id
    		and usr.per_id = per.per_id
    		and eff.biz_id = biz.biz_id
    		and per.per_role_id = eff.per_role_id
    Order by rgn.Region, biz.Rbu, usr.User
    In this solution, we first retain only those persons that are both 'Requestor' and 'BuApprover' by using the INTERSECT operator.

    I don't know which solution will be the most efficient, Stolze's or this. If you give both solutions a try, I'd be very much interested in the result.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Jul 2007
    Posts
    3
    First of all,
    Thanks for your great ideas. Apprciated.
    Stolze,
    I tried with your way but did not worked for me. The results are nothing. If I comment on Requestor and leave BUApprover gets all BuApprover related and Vice varsa.

    Wim,
    your query gives no results. The sub query gives results if I take out the other two selects. But we need those two to join with other tables.
    E.g SELECT per.per_id,
    FROM Person_26
    WHERE per.role = 'Requestor'
    INTERSECT
    (SELECT per.per_id,
    FROM Person_26
    WHERE per.role = 'BuApprover')


    Is there any modifications required for your query ? How about Exists statement?

    Please update if any

    Thanks
    Remata

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    If you can provide me with the DDL's (only the strict necessary columns) of the used tables and some INSERT statements that populates them, I'm willing to look further into this.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You seem to have a strange schema for your data. Maybe you could explain the schema and tell us how one "user" is associated with "person" and if one "person" can be Requestor and BuApprover together or not. I.e. some semantics would be helpful.

    p.s: Other than that, it is usually just "formulating exactly what you want the query to return in plan text, the apply SQL syntax." Works pretty much all the time in a very straight-forward manner.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Jul 2007
    Posts
    3
    I agree. The design was not at all good. It's strange for me.

    Sorry guys for not giving a clear picture to you both. And also Person or User both are same. Let's treat it as User.
    User can have multiple roles.
    Firstly User will create by himself or others with his username and userid and e.t.c and then Roles assigned to him.Roles for ResponsibleBusinessUnits(RBU).
    So, We want all the users having same RBU with both Requestor and BuApprover roles should display on the report.
    I have attached model and description.Thanks for spending time for me. I do appreciate your valuable time and energy.


    Thanks
    Remata.
    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
  •