Encounter a problem of subquery alias in Oracle

Feb 21, 2013 at 8:05 AM
When I construct a query which involves subquery alias, I find that an AS keyword is appended between the subquery and the alias name.
However it is correct when the AS function is used on normal table join
For instance, I draft a sample query to illustrate the problem I found.
Do I use the API correctly? Or is there any workaround for this problem?
Thank you for your advice.
DBSelectQuery subQuery = DBQuery
.Select(DBField.Field("OBJECTID"))
.Max("SDE_STATE_ID").As("SDE_STATE_ID")
.From(FeatureTableSchemaName, "A" + regId)
.GroupBy("OBJECTID");

DBSelectQuery query = DBQuery
.Select(DBField.Field("atable", "OBJECTID"))
.Select(DBField.Field("atable", "LST_UPD_USR_NAME"))
.From("SDE", LineageTableName).As("sls")
.InnerJoin(FeatureTableSchemaName, "A" + regId).As("atable").On("LINEAGE_ID", Compare.Equals, "SDE_STATE_ID")
.InnerJoin(subQuery).As("filterTable").On(DBField.Field("atable", "OBJECTID"), Compare.Equals, DBField.Field("filterTable", "OBJECTID"))
.Where(DBComparison.Between(DBField.Field("sls", "LINEAGE_ID"), DBParam.ParamWithValue(startState + 1), DBParam.ParamWithValue(endState)))
.AndWhere(DBField.Field("sls", "LINEAGE_NAME"), Compare.Equals, DBParam.ParamWithValue(lineageName))
.AndWhere(DBField.Field("atable", "SDE_STATE_ID"), Compare.Equals, DBField.Field("filterTable", "SDE_STATE_ID"))
.AndWhere(DBField.Field("atable", "OBJECTID"), Compare.Equals, DBField.Field("filterTable", "OBJECTID"))
.OrderBy("OBJECTID");
which turns out to be :
SELECT "atable"."OBJECTID", "atable"."LST_UPD_USR_NAME"
    FROM "SDE"."STATE_LINEAGES" "sls" INNER JOIN "RDMSADMIN"."A109" "atable"
             ON  ("LINEAGE_ID" = "SDE_STATE_ID")  INNER JOIN 
             (SELECT "OBJECTID", MAX("SDE_STATE_ID")  AS "SDE_STATE_ID"
                FROM "RDMSADMIN"."A109"
                GROUP BY "OBJECTID") 
                 AS "filterTable"
             ON  ("atable"."OBJECTID" = "filterTable"."OBJECTID") 
    WHERE  ( ( ( ("sls"."LINEAGE_ID" BETWEEN :orc_param1 AND :orc_param2)  AND  ("sls"."LINEAGE_NAME" = :orc_param3) )  AND  ("atable"."SDE_STATE_ID" = "filterTable"."SDE_STATE_ID") )  AND  ("atable"."OBJECTID" = "filterTable"."OBJECTID") ) 
    ORDER BY "OBJECTID"
Coordinator
Feb 22, 2013 at 9:48 AM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.