Focal Point
[CLOSED]How to search based on multiple sets of values in IN Parameter

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/9957029286

June 29, 2016, 02:29 PM
S.G
[CLOSED]How to search based on multiple sets of values in IN Parameter
Hi all,

In my report there is query like below:

TABLE FILE DATA_TABLE
PRINT
      CUSTOMER_NAME
      CLIENT_ID
      CLIENT_NAME
WHERE ( '&CUST_ID' EQ CUSTOMER_ID ) AND (CLIENT_ID IN (&CLNT_ID))


Only one customer ID and multiple client IDs under the customer is allowed. Now there is new requirement saying that the report needs to support multiple customers/clients. So I would like to update the WebfOCUS report like the following way in Oracle SQL:
WHERE (CUSTOMER_ID, CLIENT_ID) IN ((1, 1), (1, 2), (2, 3))


But I have no idea how to write that in WebFOCUS syntax. Can anyone please guide that? Thanks in advance!

This message has been edited. Last edited by: <Emily McAllister>,


WebFOCUS 8105
Windows, All Outputs
June 29, 2016, 04:32 PM
Dan Satchell
If &CUST_ID contains 5 values and &CLNT_ID contains 8 values, how will you know which client IDs go with which customer IDs?


WebFOCUS 7.7.05
June 30, 2016, 05:10 AM
Ruchika Gusain
Hi SG,

To support multiple Customer/Clients in the report use 'IN' operator:

WHERE CUSTOMER_ID IN (&CUST_ID) AND CLIENT_ID IN (&CLNT_ID) ;

'AND' operator will display ONLY those rows for which both the Customer ID and Client ID match.


Thanks
WebFOCUS
8.1.04
June 30, 2016, 05:23 AM
Dave
You could make a new field that combines CUSTOMER_ID and CLIENT_ID and use that.

...but
How will the user enter those ID's anyway?


_____________________
WF: 8.0.0.9 > going 8.2.0.5
June 30, 2016, 08:39 AM
S.G
Hi Dan,

Actually there is a web application page which shows customers list and the clients will be dynamically loading in this page based on the customers selected.

For example: if end users picked customer_1 and customer_2, then client 1 and 2 for customer_1 and client 3 for customer_2 will be shown for selection.

All the selected parameters (customer/client id pair) will be passed when submitting the page to backend and then WebFOCUS report will be triggerred.

quote:
Originally posted by Dan Satchell:
If &CUST_ID contains 5 values and &CLNT_ID contains 8 values, how will you know which client IDs go with which customer IDs?



WebFOCUS 8105
Windows, All Outputs
June 30, 2016, 08:42 AM
S.G
Hi Ruchika,

Thanks for your suggestion!

However in the database, Client IDs are only unique in one Customer. So I am afraid that your solution will bring more data.

quote:
Originally posted by Ruchika Gusain:
Hi SG,

To support multiple Customer/Clients in the report use 'IN' operator:

WHERE CUSTOMER_ID IN (&CUST_ID) AND CLIENT_ID IN (&CLNT_ID) ;

'AND' operator will display ONLY those rows for which both the Customer ID and Client ID match.


Thanks
WebFOCUS
8.1.04



WebFOCUS 8105
Windows, All Outputs
June 30, 2016, 02:00 PM
S.G
Hi Dave,

My reports query data from database views. DBA team disagrees to create a new column in the related database tables. So if I make a new field in the database view as you suggested, the indexes of CUSTOMER_ID and CLIENT_ID will be useless, which might cause performance issue then.

Appreciate your suggestion, and could you please provide more possible solution?


quote:
Originally posted by Dave:
You could make a new field that combines CUSTOMER_ID and CLIENT_ID and use that.

...but
How will the user enter those ID's anyway?



WebFOCUS 8105
Windows, All Outputs
June 30, 2016, 04:31 PM
Dan Satchell
quote:
Originally posted by S.G:
Hi Dan,

Actually there is a web application page which shows customers list and the clients will be dynamically loading in this page based on the customers selected.

For example: if end users picked customer_1 and customer_2, then client 1 and 2 for customer_1 and client 3 for customer_2 will be shown for selection.

All the selected parameters (customer/client id pair) will be passed when submitting the page to backend and then WebFOCUS report will be triggerred.

You seem to be saying that the values in &CUST_ID and &CLNT_ID will look like this:

&CUST_ID: 1,1,2
&CLNT_ID: 1,2,3

I suspect the values may actually look like this:

&CUST_ID: 1,2
&CLNT_ID: 1,2,3

If my suspicion is correct, how do you know which client ID value goes with which customer ID?


WebFOCUS 7.7.05
June 30, 2016, 04:49 PM
Doug
Dave's idea sounds the most practical.
quote:
make a new field that combines CUSTOMER_ID and CLIENT_ID and use that

July 01, 2016, 03:06 AM
Dave
S.G.

you don't need the actual combined field in the database.

DEFINE FILE CAR
-*	FIRST CHARACTER OF CARNAME + NUMBER OF SEATS
	FAKE_ID/A2 = EDIT(CAR,'9') | EDIT(SEATS,'$$9');
END

TABLE FILE CAR
	SUM SALES
	BY	FAKE_ID
	BY	CAR
	BY	SEATS
WHERE FAKE_ID EQ 'B4' OR 'J5' OR 'A2'
END



_____________________
WF: 8.0.0.9 > going 8.2.0.5