Skip to Content
author's profile photo Former Member
Former Member

Incomplete results using uSelect

Hi IDM Experts,

I'm facing a strange problem when creating a new script; so, this is what's happening; I get a list of User IDs from a textbox within a UI task; but in order to do further processing for all of the IDs in the textbox, I need to get each of their MSKEYs; seems like a standard thing to do; after building my user filter, I execute the SQL but only end up getting back one result; e.g.:

If I were to enter the following users, within the UI task's text box:

ABC1234

DEF1234

GHI1234

The user filter created through the script would be the following:

var filter = 'ABC1234','DEF1234','GHI1234'

and the SQL query would end up being this:

SELECT MSKEY FROM idmv_value_ext_active WITH (NOLOCK) WHERE ATTRNAME = 'USERID' AND SEARCHVALUE IN ('ABC1234','DEF1234','GHI1234')

Now, strangely passing this query into a variable like this:

var mskeyQuery = "SELECT MSKEY FROM idmv_value_ext_active WITH (NOLOCK) WHERE ATTRNAME = 'USERID' AND SEARCHVALUE IN ("+filter+")"

and then passing that variable into uSelect should ideally return the MSKEY for all 3 users within the query:

uSelect(mskeyQuery)

Instead, what happens is, I get a result with ONLY 1 result, which is the MSKEY for one of the users in the list; like this

"111111"

Now, strangely, when I execute the query in this manner (with the SQL query hardcoded into uSelect):

uSelect("SELECT MSKEY FROM idmv_value_ext_active WITH (NOLOCK) WHERE ATTRNAME = 'USERID' AND SEARCHVALUE IN ('ABC1234','DEF1234','GHI1234')")


I get ALL the results like this:

"111111!!222222!!333333"


I don't quite understand what's happening; the query executes perfectly outside in a SQL Server tool, and even within the SQL Tester in the IDM MMC, and also within the script ONLY IF ITS hardcoded; if the query is passed in as a variable, it only returns one user's MSKEY.

It can't have anything to do with table / view permissions; I even thought about a misplaced single quote or double quote, but that isn't the case; since I write out the exact same SQL prior to execution, to the logs, and that SQL when hardcoded works, but doesn't work when in a variable.

Is there something blatantly wrong with what I'm doing that I just don't see? Is there any other reason why this might be happening?

I would greatly appreciate your insight into what could be wrong.

Thanks a ton in advance!!!

Best regards,
Sandeep

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Dec 23, 2013 at 01:58 PM

    Hi Sandeep,

    I just tried the scenario in my dev with a sample script. But in my case I am getting result as expected. My script is as below.

    // Main function: temp

    function temp(Par){

    temp=Par;

    uErrMsg(1,"Filters passed to the script" + Par);

    var mskeyQuery = "SELECT MSKEY FROM idmv_value_ext_active WITH (NOLOCK) WHERE ATTRNAME = 'MSKEYVALUE' AND SEARCHVALUE IN ("+temp+")";

    var result = uSelect(mskeyQuery);

    uErrMsg(1, "MSKEY values of filters passed" + result);

    return result;

    }

    The result is displayed as warning in logs as below

    Can you share your complete script. Sometimes small things which we may not notice can cause such issues !!

    ~ Krishna.


    Untitled.png (12.6 kB)
    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Thank you Krishna and Fadoua!

      The version info is 7.2 patch level SP8.

      Strangely though, thanks to Fadoua's suggestion, I tried splitting the input using "\r\n" instead of "\n" and this seems to have fixed the entire thing. 🤯 😊 Thanks a ton Fadoua!

      I will keep this in mind from now onwards to always check the usage of "\r\n" and "\n".

      Best regards,

      Sandeep

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.