Is there anything like a subquery in webfocus?

Like

resultset = query ("select * from A")
for(resultset.next())
{
resultset2 = query("select z from B where C="+resultset.getField("X"))
}

Can anything similar be done or does everything have to do done with joins and hold files?

I've been using WF a long time, but half the time I can't get it to do what I need, especially when the CARDEX has to be involved. Because that file is so big it doesn't really make sense (to me) to create a hold file. A subquery is much quicker, but it doesn't seem to be possible in WF.

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

Josh, you can either use SQL Passthru to do this at RDBMS or anyone of these "JOIN" "MATCH" "IN FILE" will do at WebFOCUS level.

Using REPEAT and counter its possible to get the same functionality.

The conditional JOIN may be the closest WebFOCUS comes to providing subquery-type functionality.

JOIN FILE from_file AT from_field [TAG from_tag] [WITH fieldname]
     TO [ALL|ONE]
     FILE to_file AT to_field [TAG to_tag]
     [AS as_name]
     [WHERE expression1 ;
      WHERE expression2 ;
      ...              ; ]
END

Here's more: Conditional Joins

WebFOCUS also has the WHERE syntax: WHERE [NOT] field IN FILE fileid, but this syntax does require the use a HOLD file, which does have a size limit. Joins with hold files and MATCH FILE logic are the other methods for performing subquery-type of extracts in WebFOCUS. Unfortunately, these methods tend to be inefficient or even impossible with large amounts of data.

Finally, you can use the WebFOCUS SQL pass-thru facility to create SQL statements, including sub-selects, pass the SQL code directly to the DB, and return the answer set to a WebFOCUS hold file for further processing and/or reporting.