Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2013
    Posts
    2

    Unanswered: View to get record based on a column value

    Hi Guys,

    I have a view where the results would be like this.(userid,name,rolekey are my col names with data)
    userid name rolekey
    test1 tname rolekey1
    test1 tname rolekey2
    test1 tname rolekey3

    is this possible to retireve data from view where i need only userid with rolekey1.?

    //tried with a function but its taking more time? any options in doing it in the view itself?
    Regards
    Vin

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    //tried with a function but its taking more time?
    What exactly did you tried?
    Please copy and paste your tried statements.

    Are there other columns to be included in the result of the view?

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    --removed --
    Last edited by Wim; 07-24-13 at 13:05.
    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 2013
    Posts
    2
    Tried Retrieving it with by calling a function in my view.
    SELECT
    RD.ROL_KEY, ud.USERID,
    FROM dbo.UserProfileAssociation AS upa INNER JOIN
    dbo.PROFILE_PAX AS pax ON upa.TravelerIDProfile = pax.OID INNER JOIN
    dbo.PROFILE_ORG AS org ON upa.OrganizationIDProfile = org.OID INNER JOIN
    dbo.USERDATA AS ud ON upa.USR_OU_OID = ud.OU_OID INNER JOIN
    dbo.PROFILE_ORG_DEPARTMENT AS dep ON upa.DepartmentIDProfile = dep.OID INNER JOIN
    dbo.organizationalunitrole AS OUR ON upa.USR_OU_OID = OUR.OU_OID INNER JOIN
    dbo.ROLEDEFINITION AS RD ON OUR.ROL_OID = RD.ROL_OID
    WHERE (pax.DELETEFLAG = 0) AND
    (org.DELETEFLAG = 0) AND
    (dep.DELETEFLAG = 0) and
    (OUR.DELETEFLAG = 0) and pax.TRAVELERIDPROFILE='traveler1000'

    giving me the output as
    ROL_KEY userid

    TravelApprover traveler1000
    TravelArranger traveler1000
    Traveler traveler1000

    TravekApprover has priority 1,TravelArranger 2

    If the role key has travel approver we can consider that row,rest can be ignored.

    created a function for the scenario.
    dbo.ROLETYPE(pax.TRAVELERIDPROFILE) AS ROLETYPE ---This has the logic to iterate and send the required traveler role key as per priority.

    Question:Can this be done in the same view without functions.(In java like contains do we have any option here in DB)

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by vinnutk View Post
    Tried Retrieving it with by calling a function in my view.



    giving me the output as
    ROL_KEY userid

    TravelApprover traveler1000
    TravelArranger traveler1000
    Traveler traveler1000

    TravekApprover has priority 1,TravelArranger 2

    If the role key has travel approver we can consider that row,rest can be ignored.

    created a function for the scenario.
    dbo.ROLETYPE(pax.TRAVELERIDPROFILE) AS ROLETYPE ---This has the logic to iterate and send the required traveler role key as per priority.

    Question:Can this be done in the same view without functions.(In java like contains do we have any option here in DB)
    Looking into your post, I guessed that the answer to my following question might be "There is no other column to be included in the result".
    Quote Originally Posted by tonkuma View Post
    ...

    Are there other columns to be included in the result of the view?
    If so, please try something like ...
    Code:
    SELECT MIN(RD.ROL_KEY) AS ROL_KEY
         , MAX(name)       AS name
         , ud.USERID       AS userid
     FROM  ...
           ...
     WHERE ...
           ...
     GROUP BY
           ud.USERID
    or
    Code:
    SELECT MIN(RD.ROL_KEY) AS ROL_KEY
         , name
         , ud.USERID       AS userid
     FROM  ...
           ...
     WHERE ...
           ...
     GROUP BY
           ud.USERID
         , name

  6. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    This a mess ..

    Nothing in the DDL you failed to post is based on RDBMS, or is correct.

    1. There are absolutely no OID's in RDBMS; that is an OO concept that no RDBMS programmers would ever use. To use them is to totally invalidate the relational model.
    2. We do not use “-key” in a data element name. That is meta data and violates the ISO-11179 rule to name a thing for what it is by its nature, not for how it is used in one place.
    3. The use of INNER JOIN is how non-SQL programmers without a set-oriented mindset continue to write sequential, procedural code.
    4. We do not use flags in SQL!! That was 1950's assembly language.
    5. Please, please think silly “traveler_id_profile” is AS a data element in any RDBMS model of data! Which is it? An identifier or a profile?
    6. Why do you think that tables like “User_Data” are clear, precise names for a set? I find it to be vague and generic.
    7. In 30 years of SQL programming, I have never written a seven table query.

    Can you find the moron that did this to you and kill him? After that, you will need to replace all of his code. NOTHING HERE IS RIGHT. Hey, Google my credentials; see if I just might be right.

Posting Permissions

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