StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Can a sub-query be used to fill a BO?

Can a sub-query be used to fill a BO?

Greg McGuffey — Fri, 26 Oct 2007 12:50:22 GMT

Can I use a sub-query as one of the fields in a sql statement used to fill a BO? E.g. here is an example of the statement I'd like to use:
[codesnippet]Select
ID
,(Select StatusName
From StatusLookupTable
Where StatusID = t.StatusID) As StatusName
,Title
From TitleTable As t[/codesnippet]

StatusName wouldn't be a property the BO knows about.

This is perfectly valid SQL (use it all the time), but when I tried to use this with a bo, it bombed, saying that it couldn't find a field named "(select StatusName...." in the BO. Can this be done? If not any work arounds?

RE: Can a sub-query be used to fill a BO?

Greg McGuffey — Fri, 26 Oct 2007 12:50:22 GMT

I got it working. It wasn't a problem with my query and filling the BO, it was a problem loading that data into a list view. The problem was that when the column with the sub query was getting filled it was using the sub query to do the replacement, not the alias name. I changed the DisplayFieldNames on the PopulationListSettings of the ListView to use the alias and boom it worked!:w00t:

RE: Can a sub-query be used to fill a BO?

Greg McGuffey — Fri, 26 Oct 2007 10:59:33 GMT

Good to hear. I always forget about db debugging :pinch: I'm sure that will let me know what I'm doing wrong :D

Thanks Trent!

RE: Can a sub-query be used to fill a BO?

Trent L. Taylor — Fri, 26 Oct 2007 09:53:21 GMT

Hey Greg,

The BO can execute any query that you can execute on the database. The only time you would have a problem with this type of query would be on an update. You would need to add the the 'StatusName' field to ExcludeFieldsToUpdate/Insert on the BO. Otherwise, if you are having a problem, turn on the debug for the data source and try to see what is going on with your query through the BO.