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.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
Looping question...
 Login/Join
 
webfocusdev
Member
posted
Hi,<br />I have a query screen
where a user can perform a document
search by entering an author's
partial name.
The first thing I have to do
is take the name they
entered on the
form and query a table
to get the
emp_id(s).
I then go to another table to get all the documents associated with these emp_id(s). Since it is a partial name
search, my first query can
return multiple records.
I'm not sure how to loop through
the first query and read each emp_
id and pass it into the second query.
The code below works if I type in one
full name (DOE,JOHN)�it doesn't work
if I type in a partial name (DOE,J). 
SQL
SELECT EMP_ID  FROM EXTCOPY.PERSON
WHERE NAME LIKE '&SNLORIGINV.EVAL%';
TABLE<br />ON TABLE HOLD AS 
HOLD_EMP_ID FORMAT ALPHA
END<br />-RUN
-READ HOLD_EMP_ID, &EMP_ID
SQL<br />SELECT * FROM PAL.SD_
SNL_ORIGINATOR
WHERE ORIGINATOR_ID = '&EMP_ID'
ORDER BY SD_NUMBER;
TABLE FILE SQLOUT<br />PRINT *
END

This message has been edited. Last edited by: <Mabel>,
 
Posts: 19 | Registered: January 04, 2005Report This Post
Francis Mariani
Expert
posted Hide Post
Two potential solutions:

2) Why not use WebFOCUS in the second request, something like:

SQL
SELECT EMP_ID FROM EXTCOPY.PERSON
WHERE NAME LIKE '&SNLORIGINV.EVAL%';
TABLE
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN

TABLE FILE SD_SNL_ORIGINATOR
PRINT SD_NUMBER NOPRINT
BY SD_NUMBER
WHERE ORIGINATOR_ID IN FILE HOLD1
END

No looping necessary.

2) Dialog Manager looping

SQL
SELECT EMP_ID FROM EXTCOPY.PERSON
WHERE NAME LIKE '&SNLORIGINV.EVAL%';
TABLE
ON TABLE HOLD AS HOLD01 FORMAT ALPHA
END
-RUN

-READ HOLD01 NOCLOSE &EMP_ID.A10.

-REPEAT END_LOOP1 WHILE &IORETURN EQ 0;
SQL
SELECT * FROM PAL.SD_SNL_ORIGINATOR
WHERE ORIGINATOR_ID = '&EMP_ID'
ORDER BY SD_NUMBER;
TABLE FILE SQLOUT
PRINT *
END
-RUN

-READ HOLD01 NOCLOSE &EMP_ID.A10.

-END_LOOP1
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
webfocusdev
Member
posted Hide Post
Thanks Francis...both of your suggestions worked. I decided to go with the first option. It was cleaner and easier to work with.
 
Posts: 19 | Registered: January 04, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders