how to get the substring from the Prompt() function?

[Former Member]

Hi expert,

I have a such requirement. A report select condition, need display client code and client name(just use client code, user can't remember client's code,on the other hand,just display client name, maybe 重名)。 I created index on client code. I want to use client code to retrieve data, but list client code:client name to let user select.

my syntax is :

@Select(Project\Client Code) IN left(@prompt('Client','A','Project\Client Code:Client Name',Multi,Free,Persistent,,User:4),4) -- to get the fist 4 char

but always syntax error.

if change to

@Select(Project\Client Code) = left(@prompt('Client','A','Project\Client Code:Client Name',Mono,Free,Persistent,,User:4),4) -- to get the fist 4 char

then can pass syntax check.

But user need to select multi value, please tell me the solution, thank you very much!

Regards,

Tony

[Former Member]

Have u got the solution for this problem?

I am als stuck with the same issue.

[Former Member]

why? you also must use code to retrieve data?

because I create index on code, use code+name to retrive won't use index, so it is slow.

Regards,

Tony

[former_member142702]

Hi,

your initial statement is translated in sql as follows

 TableA.code":"TableB.name IN LEFT(('Code1:Cust1','Code1:Cust2','Code2:Cust3')

As you can seen this is not a valid SQL syntax, since LEFT cannot be applied to a list of values. Please keep in mind that this is an SQL not a BO restriction.

Another option that comes to mind is to write a function that gets as input a list of values and outputs a new list of values containing only the left part of the input values.

I guess that creating an index on the code+name combination is not an option for you, is this correct?.

Regards,

Stratos

PS: As far as I know WebI prompts contents must also be used for comparing. There is no way to define a (key,description) tuple for an LOV (in CR this is possible).

[Former Member]

hi Stratos,

Another option that comes to mind is to write a function that gets as input a list of values and outputs a new list of values containing only the left part of the input values.

-

---

I think it is a little complex ..

I guess that creating an index on the code+name combination is not an option for you, is this correct?.

---thanks for your reminder,I prefer this solution.

[PPaolo]

Hello,

if I understand correctly the original need:

you have a list of customer names, for each customer you have a code which is a unique identifier (id).

You want your users to select the customer name but you want the SQL to make the query on the customer code.

For this you can try using the index awareness feature (you can read some info about it in the Designer User's guide).

Here is a sample workflow to set it up, try it and adapt it to your scenario. Some other worflows might exist.

Suppose you have a Customer table with columns Customer.Name and Customer.Id.

1. Under the class Customers, create a CustomerName object on your Customer.Name column, you will use this only for a list of values, you can hide it if you don't want users to choose it

2. On the CustomerName object properties go to the Keys tab, insert a new Primary Key and set the Select to the Customer.ID column, leave the Where blank

3. Create a Customer object which will be used by your users to select the user name

4. Set the Customer object select to Customer.Name

5. Define the Customer object where to

Customer.id in @Prompt('Select customers:','A',Customers\CustomerName',multi,primary_key,,)

6. Save all and test the Customer object in WebIntelligence.

You will be prompted by Customer name but the SQL will use the customer id (try to run a query then once in the report, click 'edit query' , look at the SQL and check if the id has been taken into account.

Hope that it helps

PPaolo

[Former Member]

Hello PPaolo,

thank you very much.

I am trying with your solution.But I am stuck with step5.

@Prompt('Select customers:','A',Customers\CustomerName',multi,primary_key,,)

I looked the @Prompt function help, the syntax is:

@Prompt('message','type','value list',[mono|multi],[free|constrained]), I think this help is not detail,

my current filter is :

@Select(Project\Client Code) IN @prompt('Client','A','Project\Client Code:Client Name',Multi,Free,Persistent,,User:3)

where should I add the Primay_key into the prompt function?

Many thanks!

Regards,

Tony

[PPaolo]

Hello

in my proposal you shouldn't be using the "@Select IN @Prompt" way of doing.

I am suggesting that you create a new object which contains:

- in the Select part the definition of your data: Customer.Name

- in the Where part the filtering: Customer.id in @Prompt('Select customers:','A',Customers\CustomerName',multi,primary_key,,)

The prompting will be automatic when you drag that object in the query panel.

If you want just to add a Filter with no dimension object you will have to define the Where of this filter as the Where above, the result should be the same.

I am not proposign here a way to parse the result string of the multiple results you can get, I am proposing a workflow to allow your users to select names and have the query run on ids.

Hope that it helps

PPaolo

[Former Member]

Hello PPaolo,

Thank you very much.

I create a filter ,and in it ,I write:

@Select(Project\Client Cd) IN @prompt('Client','A','Project\Client Code:Client Name',Multi,primary_key,fee,Persistent,User:3)

and now, user can choose name, and sql is query on client cd.

but another problem arise.

I want to add a function on this filter, if user type "ALL" into the selected item,this means let's ignore this condition.So I write below in this filter:

@Select(Project\Client Cd) IN @prompt('Client','A','Project\Client Code:Client Name',Multi,primary_key,fee,Persistent,User:3) OR 'ALL' IN @prompt('Client','A','Project\Client Code:Client Name',Multi,primary_key,free,Persistent,User:3)

the problem is, now , user can not type 'ALL' into the value list box. I think the parameter "free" means user can type the value he want, but now, it doesn't work.

Appreciate your continuous Support!

Tony

[PPaolo]

Hello,

the primary_key and free keywords are mutually exclusive.

If you want to use your filter as is you should create a fake 'ALL' entry in the list of values and client code.

Best regards

PPaolo

[former_member142702]

Hi,

you cannot apply the LEFT operator on a set of values rather on a single value. You can use the following syntax:

@Select(Project\Client Code:Client Name) IN (@prompt('Client','A','Project\Client Code:Client Name',Multi,Free,Persistent,,User:4))

Regards,

Stratos

[Former Member]

hi Stratos,

thanks for you reply.

But I really need to use client code to retrieve data. Your solution is use client code+client name to retrieve data.

Could you help me, is there any other solution?

what I need is when user choose selection item, he can see code and name, but in sql, use code

to retrieve data.

Regards,

Tony

[former_member142702]

Sorry, you are right. In this case you must use a sub query in your where-clause. Assuming the client name is in TableA and the client code is in TableB:

@Select(Project\Client Code) IN (SELECT B1.clientCode from TableA A1, TableB B1 WHERE (B1.clientCode + A1.clientName) IN (@prompt('Client','A','Project\Client Code:Client Name',Multi,Free,Persistent,,User:4)) AND <...add the join condition for tables A und B here...> )

Regards,

Stratos