1 2 Previous Next 16 Replies Latest reply: Mar 10, 2010 1:49 PM by mikereiche RSS

    Search Functionality

    718594
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              Hey Jeff

                              Still no luck, no data is returned!!
                              • 12. Re: Search Functionality
                                mikereiche
                                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
                                  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
                                    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