I am translating a SQL query into Focus-eze and in my select fields I want to include a sub query. Through training it seems like this is possible through adding a virtual field in the synonym but I'm not sure if it is flexible enough to meet my needs. I'll explain:

My query selects from a table called A. There are a few where clauses, one of which says "only IDs in this list". This list is a set of GUIDs which were previously selected from table A based on a few criteria, one of which is a time frame.

Cool. So in my select fields I include a subquery. That subquery is an additional query on table A where I say, "Do a count of rows which meet this criteria", (and of course one of the where clauses creates connection between the final return row, and the rows that I'm counting). Well, this count should also include the time constraint I used in the initial query used to find out which GUIDs I'm operating on. It seems like I may not be able to do that... Thoughts?

An example query may help out:

SELECT
Field1,
Field2,
Field3,
(SELECT COUNT(*) FROM A A_ALIAS WHERE A_ALIAS.ID = A.ID AND A_ALIAS.Field1 = 'X' AND A_ALIAS.FieldDateTime >= '2000-01-01' AND A_ALIAS.FieldDateTime <= '2001-01-01') AS SubQueryField
FROM A
WHERE A.ID IN (SELECT ID FROM #Temp);

So, some questions:

1. Can I do this in a virtual field?2. If I choose not to do it in a virtual field, how should I approach it? Which would be the more performant option?3. If I add a virtual field to a synonym, even if the virtual field is not included in any way, is it calculated / executed? Where I'm going with that being if I have an expensive virtual field but barely ever use it, is it better to do it through other means so it's not executed all the time?4. Kind of an aside question - I used sub queries for other means, (so HOLD a query to a SQL file, use that in another query), and found that the method of just holding the data instead of the query itself was almost always more performant. Now, we are talking about another 200ms but usually it was quicker to HOLD the data set and perform an IN on that. Thoughts?

This message has been edited. Last edited by: FP Mod Chuck, February 19, 2019 12:54 PM

When I first started with webfocus I kept trying to write sub queries..! So missed them until I got used to how webfocus thinks. Do the sub query first into a hold file and reference that. I think you are looking for:TABLE FILE CARPRINTCOUNTRYWHERE COUNTRY EQ 'ENGLAND' OR 'FRANCE'ON TABLE HOLD AS SEL1 FORMAT ALPHAEND

TABLE FILE CARPRINT *WHERE COUNTRY IN FILE SEL1END

As for performance - sorry can't comment as tiny difference in performance isn't an issue in how we use webfocus at my place of work, but as a rule of thumb if I can safely do a query once and hold the output for reuse then I will. (do a lot of this as our webfocus data is only refreshed every 24 hours)

Virtual fields (DEFINE or COMPUTE) are evaluated only on execution, when used somewhere in the TABLE FILE...END and does not react the exact same way depending of their definition.DEFINE it's against all rows from the source dataCOMPUTE it's against rows from the extracted data set (after the WHERE clauses)

As Daryl stated, I would recommend to first extract your data from the sub-query to HOLD it and then use it for the "main" queryBut since it seems that you sub-query is performing two things (providing data and filtering the data) I would use it within a JOIN.So, that way you will be able to filter and display the data (count) in one step

something such as this

TABLE FILE CAR
SUM CNT.DST.MODEL AS 'NB_MODEL'
BY COUNTRY
BY CAR
WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY';
ON TABLE HOLD AS SUBQUERY
END
-RUN
JOIN
COUNTRY AND CAR IN CAR
TO COUNTRY AND CAR IN SUBQUERY TAG J1 AS J1
END
TABLE FILE CAR
SUM DEALER_COST
NB_MODEL
BY COUNTRY
BY CAR
-* To have all data, remove the WHERE clause
-* but only those with a match on Country/Car with SUBQUERY will have the NB_MODEL, otherwise 0
WHERE COUNTRY EQ J1.COUNTRY;
END
-RUN

Small question on the back of this - I'm used to multi-field joins as I use a DB2 database, but I didn't think you could join hold files using multiple fields, I thought you were restricted to a single indexed field? I appreciate I'm probably wrong - but what are the rules around hold files, indexes and number of fields in joins? Does it depend on the 'format' you've specified in the 'on table hold as'?

Does it depend on the 'format' you've specified in the 'on table hold as'?

I may also be wrong or don't have the exact proper expressed answer but I would say yes

If you are HOLDing as FORMAT FOCUS the child file in the JOIN must have an INDEX and you'll be able to join only on one of the INDEXed fieldsBut using txt hold file (no format specified) as I did, you can join on several fields

Does one better than the other ?To this I will answer that it depend on the situation and performance.For a small data set, a txt file without any index may be good, but larger data may need an indexed file so, a FOCUS format (or more like DB format).

Try the below and you will see that the result is not the same as my previous sample since in the below the child file (SUBQUERY) has been joined with the entire country

TABLE FILE CAR
SUM CNT.DST.MODEL AS 'NB_MODEL'
BY COUNTRY
BY CAR
WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY';
ON TABLE HOLD AS SUBQUERY
END
-RUN
JOIN
COUNTRY IN CAR
TO COUNTRY IN SUBQUERY TAG J1 AS J1
END
TABLE FILE CAR
SUM DEALER_COST
NB_MODEL
BY COUNTRY
BY CAR
END
-RUN