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,

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 ...

I'm pretty sure this makes sense. :-)

Thank you for your help!

-SET &filteradd = DECODE &kounter( 1 110 2 123 3 767 ...etc) ;

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

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

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.

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.

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.

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.

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

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