Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED]How to search based on multiple sets of values in IN Parameter

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED]How to search based on multiple sets of values in IN Parameter
 Login/Join
 
Member
posted
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
 
Posts: 26 | Location: Hefei,China | Registered: March 27, 2016Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Member
posted Hide Post
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
 
Posts: 13 | Registered: May 31, 2016Report This Post
Master
posted Hide Post
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
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
Member
posted Hide Post
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
 
Posts: 26 | Location: Hefei,China | Registered: March 27, 2016Report This Post
Member
posted Hide Post
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
 
Posts: 26 | Location: Hefei,China | Registered: March 27, 2016Report This Post
Member
posted Hide Post
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
 
Posts: 26 | Location: Hefei,China | Registered: March 27, 2016Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Expert
posted Hide Post
Dave's idea sounds the most practical.
quote:
make a new field that combines CUSTOMER_ID and CLIENT_ID and use that
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Master
posted Hide Post
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
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED]How to search based on multiple sets of values in IN Parameter

Copyright © 1996-2020 Information Builders