[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