This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Mar 10, 2010 11:49 AM by mikereiche RSS

Search Functionality

718594 Newbie
Currently Being Moderated
Hi,

I need some inputs on how to fetch the data based on conditional param. Consider for example, I need to fetch the Address details of the emp. based on EmpId.
The Input to the service can be either EmpId or FirstName & LastName or PId. Based on the Input, EmpId will be fetched, and then Emp Address.

Table Emp
Id
First Name
LastName
PId

Table Address
Id
EmpId
Street
Phone

Rgds,
Is
  • 1. Re: Search Functionality
    mikereiche Pro
    Currently Being Moderated
    Read this thread. Post again if you have questions.

    Conditional Where?

    If you're going to do "query by form", unless it is very, very basic, I recommend that you use the ad hoc query feature.
  • 2. Re: Search Functionality
    718594 Newbie
    Currently Being Moderated
    Hi Mike,

    I've looked into that particular thread, but I'm facing a problem while working with double values .
    The following code works well; if the Name is not specified but fails if the Id is left empty.. Also; I tired the code by checking " Set Null" then No data is returned

    declare function tns:getDataOne($Id as xs:double?, $Name as xs:string?) as element(ns1:Table3)*{
    for $TABLE1 in tab:TABLE1()
    where
    ($TABLE1/NAME eq $Name ) or fn:empty($Name)
    or
    ($TABLE1/ID = $Id) or fn:empty($Id)


    return
    <ns1:Table3>
    <ns1:Table1Id>{fn:data($TABLE1/ID)}</ns1:Table1Id>
    <ns1:Table1Name>{fn:data($TABLE1/NAME)}</ns1:Table1Name>

    </ns1:Table3>


    };


    *"": can not cast to {http://www.w3.org/2001/XMLSchema}double: error: double: Invalid double value:*
  • 3. Re: Search Functionality
    mikereiche Pro
    Currently Being Moderated
    You have to set ID to null via the check-box (like it tells you, and empty string is not a valid double).
    You should also set Name to null via the check-box as well (you might be a little lucky Oracle db, I believe a zero-length string is treated as null).

    Can you show the audit output from when you execute the query?



    Also, I'm pretty sure you want 'and' instead of 'or' (see below). Otherwise having one of the two parameters == null will return all rows regardless of the value of the other parameter.

    ($TABLE1/NAME eq $Name ) or fn:empty($Name)
    and
    ($TABLE1/ID = $Id) or fn:empty($Id)
  • 4. Re: Search Functionality
    718594 Newbie
    Currently Being Moderated
    No data is returned







    sql:
    SELECT t3.c1, t3.c2
    FROM (
    SELECT ROWNUM AS c3, t2.c1, t2.c2
    FROM (
    SELECT t1."ID" AS c1, t1."NAME" AS c2
    FROM "EMP_DEMO"."TABLE1" t1
    WHERE ((? = t1."ID") OR ((? = 1) AND (? = t1."NAME")) OR (? = 1))
    ) t2
    WHERE (ROWNUM < 501)
    ) t3
    basesql:
    SELECT t3.c1, t3.c2
    FROM (
    SELECT ROWNUM AS c3, t2.c1, t2.c2
    FROM (
    SELECT t1."ID" AS c1, t1."NAME" AS c2
    FROM {EMP_DEMO}.{TABLE1} t1
    WHERE ((? = t1."ID") OR ((? = 1) AND (? = t1."NAME")) OR (? = 1))
    ) t2
    WHERE (ROWNUM < 501)
    ) t3
    time: 0

    query/performance
    evaltime: 1

    query/service
    result:

    query/service
    function: check
    arity: 2
    dataservice: ld:schemas/Table3.ds
    query:
    import schema namespace t1 = "http://www.example.org/Table3" at "ld:schemas/Table3.xsd";
    declare namespace ns0="ld:schemas/Table3";
    declare namespace ns1="http://www.w3.org/2001/XMLSchema";
    declare variable $__fparam0 as ns1:double? external;
    declare variable $__fparam1 as ns1:string? external;
    fn:subsequence(
    for $Table3 in ns0:check($__fparam0,$__fparam1)
         return
                   $Table3
    ,1,500)
    parameters:
    null
    Name1

    Edited by: Is916 on Mar 5, 2010 4:58 PM
  • 5. Re: Search Functionality
    mikereiche Pro
    Currently Being Moderated
    This is pretty much why I keep suggesting to use ad hoc queries for this.

    Here are your choices

    1) ad hoc query
    2) open a case with customer support
    3) rework your query so that it does not involve passing a null value to the database

    the example just shows the double, you'd need to do the same for the string.

    let $doubleValue :=
    if empty($theDoubleArg) then
    xs:double(-1.0)
    else
    $theDoubleArg

    for ...

    where ( $DB/COLUMN eq $doubleValue or $doubleValue eq xs:double(-1.0)) ...
  • 6. Re: Search Functionality
    718594 Newbie
    Currently Being Moderated
    It works if we use the where as where $Id eq $TABLE1/ID or fn:empty($Id)=true()

    but the where clause is not generated..


    will check if can proceed with ad-hoc
  • 7. Re: Search Functionality
    mikereiche Pro
    Currently Being Moderated
    For simple cases, you can use the Filter api. Basically you define a function that would retrieve everything, and then apply "filters" to it - like $TABLE1/ID = $id etc. (if the user did provide an $id, you would not include that in the filter). There should be an example in the Advanced API announcement in this forum.
  • 8. Re: Search Functionality
    661228 Newbie
    Currently Being Moderated
    I have not had trouble with this construct, and I have used it many situations. I recommend trying one more time with this code:


    declare function tns:getDataOne($Id as xs:double?, $Name as xs:string?) as element(ns1:Table3)*{
    for $TABLE1 in tab:TABLE1()
    where ($TABLE1/NAME eq $Name) or fn:empty($Name)
    where ($TABLE1/ID eq $Id) or fn:empty($Id)

    return
    <ns1:Table3>
    <ns1:Table1Id>{fn:data($TABLE1/ID)}</ns1:Table1Id>
    <ns1:Table1Name>{fn:data($TABLE1/NAME)}</ns1:Table1Name>
    </ns1:Table3>
    };


    Regards,

    Jeff
  • 9. Re: Search Functionality
    mikereiche Pro
    Currently Being Moderated
    I recommend that you don't use any constructs in ODSI 10.3 where it would be possible for a null parameter to occur on a sql call.

    Edited by: mikereiche on Jan 30, 2012 4:48 PM

    The issue is that SQL syntax for querying for a null value ( ... where COL_A is null ) is not consistent with querying for a non-null value (... where COL_A = ? ), and for ODSI to support querying for null values, it would need to delay generating the sql until the value of the arguments was known, and also there would be the possibility of having 2^number_of_args different sql statements. So it does not execute queries where any of the arguments are null.
  • 10. Re: Search Functionality
    661228 Newbie
    Currently Being Moderated
    Haha, OK, I would never recommend against Mike's recommendation. If you want to make sure to avoid the possibility of nulls, you could always use xquery to remove them before the query is sent to the db:


    declare function tns:ensureNotNull($arg as xs:string?) as xs:string {
         let $result := (if (fn:exists($arg)) then $arg else '')
         return $result
    };

    declare function tns:ensureNotNullDateTime($arg as xs:dateTime?) as xs:dateTime {
         (if (fn:exists($arg)) then $arg else fn:current-dateTime())
    };

    declare function tns:ensureNotNullInteger($arg as xs:integer?) as xs:integer {
         (if (fn:exists($arg)) then $arg else 0)
    };

    declare function tns:ensureNotNullDouble($arg as xs:double?) as xs:integer {
         (if (fn:exists($arg)) then $arg else 0.0)
    };


    declare function tns:getDataOne($Id as xs:double?, $Name as xs:string?) as element(ns1:Table3)*{
    for $TABLE1 in tab:TABLE1()
    where ($TABLE1/NAME eq tns:ensureNotNull($Name)) or fn:empty($Name)
    where ($TABLE1/ID eq tns:ensureNotNullDouble($Id)) or fn:empty($Id)

    return
    <ns1:Table3>
    <ns1:Table1Id>{fn:data($TABLE1/ID)}</ns1:Table1Id>
    <ns1:Table1Name>{fn:data($TABLE1/NAME)}</ns1:Table1Name>
    </ns1:Table3>
    };


    I have done this as well and avoids definite problems that arise when do relative comparisons such as "$TABLE1/UPDATED_ON gt $minDate".

    Good luck!
    Jeff
  • 11. Re: Search Functionality
    718594 Newbie
    Currently Being Moderated
    Hey Jeff

    Still no luck, no data is returned!!
  • 12. Re: Search Functionality
    mikereiche Pro
    Currently Being Moderated
    Still no luck, no data is returned!!
    I hate when people do that. I can't see what you've done.

    Can you show the definition of your function? and the definition of all the functions it calls? Ideally you'd export and attach your dataspace, but as there are no provisions for attachments you'll have to paste everything in a post. Also post the audit output from executing your query.

    Before you get the audit output - go to the odsiconsole for your dataspace, go to the Audit page, find query->wrappers->relational and set "parameters" -> ALWAYS. Then look at your audit. You will notice that if you use non-null parameters, that the parameters appear in the audit (and you will get correct results). If any of the parameters to the sql are null - they will not appear (and you will not get any results - this is because the sql is not executed - which is why I was telling you to avoid null parameters).

    - Mike
  • 13. Re: Search Functionality
    718594 Newbie
    Currently Being Moderated
    Sorry for the incomplete post..It wont be possible for me to post the function and plan now..bt will really apprecriate if can get help on this

    what I'm looking for is a function which will have optional input params say $id as xs:string? , $fname as xs:string?, $lname as xs:string?
    this function should return me the records from the database corresponding to the input param(id or name or both). The field may have a null value in the Database


    sample data

    Id – 1
    Name – asd
    Last name  - qwe

    Id -2
    Name – null
    Last name – null
    *
    Id – 3*
    Name – das
    Last name – null

    if the input is id =1 it shoud return name - asd, last name - qwe
    if input is name - das, it should return name - das, last name- null,
    if input is name - das, id 1 - no data
    if input is name - asd , id -2 ...then last name - qwe, name - asd
  • 14. Re: Search Functionality
    mikereiche Pro
    Currently Being Moderated
    I understand what you want. Jeff has provided you with the correct solution.

    You have implemented it incorrectly (otherwise it would work).

    If your code is top-secret, please take five minutes to write some non-top-secret code that demonstrates the same problem.

    - Mike
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points