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,

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 CAR
PRINT
COUNTRY
WHERE COUNTRY EQ 'ENGLAND' OR 'FRANCE'
ON TABLE HOLD AS SEL1 FORMAT ALPHA
END

TABLE FILE CAR
PRINT *
WHERE COUNTRY IN FILE SEL1
END


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)

Sean,

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 data
COMPUTE 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" query
But 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

Martin - okay, so what if the join is occuring on a child of CAR?

So would it be something like:

... 
JOIN 
     COUNTRY AND CAR IN CAR.CHILD
  TO COUNTRY AND CAR IN SUBQUERY TAG J1 AS J1
END

? I've attempted what you have suggested but it says the field I'm creating is missing. I'm assuming I'm just not joining properly.

Or, is the following sufficient and FOCUS is smart enough to pick up the join between CAR and CHILD.

   
... 
JOIN 
     COUNTRY AND CAR IN CHILD
  TO COUNTRY AND CAR IN SUBQUERY TAG J1 AS J1
END

COUNTRY AND CAR tell FOCUS which fields to use for the join

quote:

Or, is the following sufficient and FOCUS is smart enough to pick up the join between CAR and CHILD.
...
JOIN
COUNTRY AND CAR IN CHILD
TO COUNTRY AND CAR IN SUBQUERY TAG J1 AS J1
END

NO, in the above the JOIN is not between CAR and CHILD, it's between CHILD and SUBQUERY where CHILD.COUNTRY is joined to SUBQUERY.COUNTRY and CHILD.CAR joined to SUBQUERY.CAR

In SQL it's translated as below

SELECT ...
FROM CHILD
INNER JOIN SUBQUERY
ON CHILD.COUNTRY = SUBQUERY.COUNTRY
AND CHILD.CAR = SUBQUERY.CAR

Right, so what would the syntax look like for supporting a join to the child?

Looks like the following does the trick, thanks for your help!

 
... 
JOIN 
     CHILD.COUNTRY IN CAR
...
 

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'?

Cheers,

quote:

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 fields
But 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