borimir

IQueryDef, versioned data and aliases for table names

Discussion created by borimir on Nov 22, 2011
Hello,

I use versioned data in MS SQL Server. I use IQueryDef to search for data by criteria in multiple tables. It works fine for the selected version until I put alias name for a table. Then the result set contains data only from original tables, i.e. before these tables were registered as versioned. Is there any way to solve this issue?

I have to use aliases because I want to make a join twice with the same table. For example, I would like to search for POIs which are in relation with two Categories, e.g. "Hotel" and "Restaurant". The POIs have to be related to both categories, so the join would be something like:
SELECT POI.*
FROM POI
JOIN POICategory c1 ON POI.POI_ID = c1.POI_ID AND c1.CategoryID = <Hotel category ID>
JOIN POICategory c2 ON POI.POI_ID = c2.POI_ID AND c2.CategoryID = <Restaurant category ID>

If I add new category for a POI and I search for POIs in that category, the result set would not contain my POI. It would contain only original data existing in POICategory table before the table was registered as versioned. If I use query without aliases for tables (and only one JOIN), the result set contains the newly added category. What I mean is that the following query works fine:
queryDef.Tables = "POI, POICategory";
queryDef.WhereClause = "POI.POI_ID = POICategory.POI_ID AND POICategory.CategoryID = <My new category ID>";

But the following query does not return correct result set - it does not use the delta tables:
queryDef.Tables = "POI, POICategory c1";
queryDef.WhereClause = "POI.POI_ID = c1.POI_ID AND c1.CategoryID = <My new category ID>";

So... is there a way to use alias names for tables in IQueryDef when data is versioned?

Outcomes