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     [SOLVED] Build Criteria On The Fly?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Build Criteria On The Fly?
 Login/Join
 
Platinum Member
posted
I'm working with some Web Services data that requires a primary key value in the criteria to return a record, and is set to only return one record at a time. No where clause in the query returns zero records rather than all records.

For example,in order to get a record back in the results, I need:
WHERE PRIMARY_KEY_ID EQ 109


However, if I have:
WHERE PRIMARY_KEY_ID EQ 109 OR 110

This makes two calls to the Web Services data, so it will return both records that have this PRIMARY_KEY_ID value (one for 109, one for 110).

In Visual Basic for Applications, I can create a where clause on the fly with a for next loop, checking to see if the a range of primary key values returns a record, and if it does, append that PRIMARY_KEY_ID value to the end of the where clause

Is it possible to create a for/next loop in WebFOCUS that would start a 1, and got to 100, test to see if the value of 1 would return records from a table, and if so, append it to a string variable, then move to 2, test again, append if necessary, etc?

This message has been edited. Last edited by: Kerry,


Production - 7.6.4
Sandbox - 7.6.4
 
Posts: 241 | Location: Bethesda, MD | Registered: August 14, 2007Report This Post
Expert
posted Hide Post
of course,
-SET &filter = 'IF PRIMARY_KEY EQ 109';
-SET &kounter = 1 ;
-top.loop
TABLE FILE CAR
&filter
PRINT *
ON TABLE HOLD
END
-RUN
-IF &LINES GT 0 GOTO do.report;
-SET &kounter= &kounter + 1 ;
-SET &filteradd = DECODE &kounter( 1 110 2 123 3 767 ...etc) ;
-SET &filteradd = ' OR ' | &filteradd ;
-SET &filter = &filter | &filteradd ;
-GOTO top.loop ;
..get the idea?
-do.report
TABLE FILE HOLD ...




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Platinum Member
posted Hide Post
I'm pretty sure this makes sense. :-)

Thank you for your help!


Production - 7.6.4
Sandbox - 7.6.4
 
Posts: 241 | Location: Bethesda, MD | Registered: August 14, 2007Report This Post
Platinum Member
posted Hide Post
-SET &filteradd = DECODE &kounter( 1 110 2 123 3 767 ...etc) ;


For this line, what are the values you have put inside the parenthesis?


Production - 7.6.4
Sandbox - 7.6.4
 
Posts: 241 | Location: Bethesda, MD | Registered: August 14, 2007Report This Post
Platinum Member
posted Hide Post
I think this is pretty close, but I'm still not getting more than one record, or an error message:

-SET &ECHO=ALL;

SET ASNAMES = ON

-SET &KOUNTER = 109 ;
-SET &FILTER = 'WHERE SART_GETAUDIT.GETAUDIT.AUDITID EQ ' | &KOUNTER;

-*Beginning of loop
-TOP.LOOP

TABLE FILE SART_GETAUDIT
PRINT 
SART_GETAUDIT.GETAUDIT.AUDITID AS 'AUDITID'

&FILTER

ON TABLE HOLD
END

-RUN
-IF &LINES EQ 0 GOTO DO.REPORT;

-SET &FILTERADD = ' OR ' | &KOUNTER ;
-SET &FILTER = &FILTER | &FILTERADD ;

-SET &KOUNTER= &KOUNTER+ 1 ;

-GOTO TOP.LOOP ;

-DO.REPORT
TABLE FILE HOLD
PRINT 
AUDITID

ON TABLE PCHOLD FORMAT EXL2K
END


Production - 7.6.4
Sandbox - 7.6.4
 
Posts: 241 | Location: Bethesda, MD | Registered: August 14, 2007Report This Post
Expert
posted Hide Post
i just made them up
i don't know what your expectation set is...
the 1st added value is 110
the 2nd added value is 123
the 3rd is 767
etc
If its the DECODE function you're unfamiliar with, give it a look inthe manuals.




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Platinum Member
posted Hide Post
What I'm not understanding is why the decode is needed.

My problem is that the Web Services data is set up so that it only returns one record per call to the data view, so if I want to get more than one record back, I need to "call" the view multiple times, one for each primary key value using the "OR" portion of the criteria.

I want to start with a string that says "WHERE PRIMARY_KEY EQ ", and a default variable, &PKVariable, value of 1.

Then look in the data to see if the "WHERE PRIMARY_KEY EQ 1" returns records. If it does, then move onto the next incremental value for &PKVariable, which would be 2. If "WHERE PRIMARY_KEY EQ 2" returns records, then append "OR 2" to the original "WHERE PRIMARY_KEY EQ 1" string to give me "WHERE PRIMARY_KEY EQ 1 OR 2".

Then when I call the data again, because my criteria is "WHERE PRIMARY_KEY EQ 1 OR 2", I would recieve both records back.

Since I don't know what the highest value for the primary key field is, I would need to keep looping through until the "WHERE PRIMARY_KEY EQ " test does not return any records.


Production - 7.6.4
Sandbox - 7.6.4
 
Posts: 241 | Location: Bethesda, MD | Registered: August 14, 2007Report This Post
Expert
posted Hide Post
look, i'm just giving you the looping technique that increases the filter features.
You'll have to make it work for whatever your particular criteria are.




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Platinum Member
posted Hide Post
I appreciate your help. I was just trying to think it through out loud.

I have a feeling that the code works, but the data source wont't work with it. I'll go back and talk to the DBA's.


Production - 7.6.4
Sandbox - 7.6.4
 
Posts: 241 | Location: Bethesda, MD | Registered: August 14, 2007Report This Post
Guru
posted Hide Post
ColdWhiteMilk,

The way I am reading your request tells me that you may be trying to do something like this...
1) Retrieve a list of KEY values from a table
2) Perform some action (query, update, etc.) on the same table or some other table only for those records whose KEY value is equal to the KEY values you retrieved in step 1

If that is what you are trying to do. Try this...
-******  Step 1  ******-
-******  Get a list of IDs from TRAINING Table  ******-
TABLE FILE TRAINING
PRINT
     DST.PIN
-******  Could add a WHERE clause here to get a limited set of IDs  ******-
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS TRN_IDS FORMAT ALPHA
END
-RUN											
-******  Step 2  ******-			   
TABLE FILE PERSINFO
PRINT *
-******  Only return records from PERSINFO whose ID value is found in TRN_IDS  ******-
WHERE PIN IN FILE TRN_IDS;
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
END


If I interpreted your question wrong then please disregard.

Cheers,

Dan


7.7.05M/7.7.03 HF6 on Windows Server 2003 SP2 output to whatever is required.
 
Posts: 393 | Location: St. Paul, MN | Registered: November 06, 2007Report This Post
Platinum Member
posted Hide Post
Dan, it kinda sounds to me that is exactly what ColdWhiteMilk is looking for.
Extract values in pass #1 to a hold file (or multiple hold files), and in pass #2 use an 'if fld eq (ddname)' or multiple ddnames if there's a need to split it out.

this technique is limited to the # of bytes it can go against (3200 for a single file?). In any case if the values are exceeding limitation an alternative would be to use the decode ddname (where its 32,000 bytes) per file. Hope this helps ColdWhiteMilk.

Ira


aix-533,websphere 5.1.1,apache-2.0,
wf 538(d), 537 (p),
==============
7.6.11 (t) aix 5312
websphere 6.1.19
apache 2.0
 
Posts: 195 | Registered: October 27, 2006Report 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     [SOLVED] Build Criteria On The Fly?

Copyright © 1996-2020 Information Builders