StrataFrame Forum

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

http://forum.strataframe.net/Topic12200.aspx

By Greg McGuffey - 10/25/2007

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:

Select

ID

,(Select StatusName

From StatusLookupTable

Where StatusID = t.StatusID) As StatusName

,Title

From TitleTable As t




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?
By Trent L. Taylor - 10/26/2007

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.

By Greg McGuffey - 10/26/2007

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



Thanks Trent!
By Greg McGuffey - 10/26/2007

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