Using input parameter as an optional input

Question

Using wso2, DSS version 3.01, I am trying to have an input parameter that could be an optional parameter. A user can say, give me all the info for this specific code, or if a user does not specify any code, I want to give all the rows of data. Can you help?

Is it that you want the user to specify some criterion for some query? — cmd, Oct 11 '13 at 20:51
yes. I have used the input as a parameter. I am not sure defaultvalue can be used as below from user @poohdedoo since I want to display all the codes and not only one. One code can be specified by a user. The generic sql is basically what I have as below by poohdedoo. — user2872396, Oct 14 '13 at 13:59

score 1 · Answer 1 · answered May 08 '14 at 12:30

Instead of creating a query for each optional parameter, you can also do the following:

<query id="selectEmployees" useConfig="default">
   <sql>select * from Employees where (:employeeNumber is null or employeeNumber = :employeeNumber)</sql>
   <result element="employees" rowName="employee">
      <element column="lastName" name="last-name" xsdType="string"/>
      <element column="firstName" name="first-name" xsdType="string"/>
      <element column="email" name="email" xsdType="string"/>
      <element column="salary" name="salary" xsdType="double"/>
   </result>
   <param defaultValue="#{NULL}" name="employeeNumber" ordinal="1" paramType="SCALAR" sqlType="INTEGER" type="IN"/>
</query>
<operation name="getEmployees">
   <call-query href="selectEmployees">
      <with-param name="employeeNumber" query-param="employeeNumber"/>
   </call-query>
</operation>

Now you can call 'getEmployees' with 'employeeNumber' and get specific employee,
or you can call 'getEmployees' without 'employeeNumber' and get all employees.
(Calling without 'employeeNumber' is by omitting the 'employeeNumber' tag, or using 'xsi:nil="true"'.)
Obviously, you cannot query for the value 'null' this way.

score 0 · Answer 2 · edited Oct 18 '15 at 01:59

0

Well you can make input parameters optional by giving default values to the input parameters. For example

<query id="MyQ" useConfig="myDS">
  <sql>select cust_id,name from customer where cust_id = ?</sql>
  <result element="Entries" rowName="Entry">
     <element column="cust_id" name="cust_id" xsdType="string"/>
     <element column="name" name="name" xsdType="string"/>
  </result>
  <param defaultValue="1" name="cust_id" sqlType="INTEGER"/>
</query>

Here if you do not mention the input parameters it will take the input parameter as one. Or else you need to create two queries and handle them programmatically

edited Oct 18 '15 at 01:59

Mogsdad

44,709
21
151
275

answered Oct 12 '13 at 12:51

poohdedoo

1,258
1
18
42

It seems like I need to create 2 queries? How do I do that? I have the following parameter and resource. ' ' – user2872396 Oct 14 '13 at 14:09
You have to create two querries one with input parameter Cd and another query without input parameter which takes all, then map each query to different opperations – poohdedoo Oct 14 '13 at 15:09
Ok. I can create 2 queries. How do you map these 2 queries to a different operation? I am new to DSS. I would appreciate your example. Thanks. – user2872396 Oct 14 '13 at 15:37
I have attached a sample – poohdedoo Oct 14 '13 at 16:08

score 0 · Answer 3 · answered Oct 14 '13 at 16:07

ok soo for example

<query id="employeesByNumberSQL" useConfig="default">
  <sql>select * from Employees where employeeNumber = ?</sql>
  <result element="employees" rowName="employee">
     <element column="lastName" name="last-name" xsdType="string"/>
     <element column="firstName" name="first-name" xsdType="string"/>
     <element column="email" name="email" xsdType="string"/>
     <element column="salary" name="salary" xsdType="double"/>
  </result>
  <param name="employeeNumber" ordinal="1" paramType="SCALAR" sqlType="INTEGER" type="IN"/>

select * from Employees

You have two queries employeesByNumberSQL, employeesByNumberSQL1 these two are mapped togetemployeesByNumber and getemployeesByNumber2

You are saying have 2 operations (one each query) and 2 resource GET methods, one for each. Is that what you are saying? Because what I would like is to have one resource GET with one input parameter CD. It would take a cd if a user provided, and if not, list all using resource ' ' — user2872396, Oct 14 '13 at 16:16

Using input parameter as an optional input

3 Answers3

Linked