> > The KeyField *is* in the query. > > Yes, but not with the table.column format. You want to specify the >column that pertains to the key field.
I don't understand what you're getting at. Here's the query, broken down into clauses: Fields clause, includes a derived field; pkid is the last (third) field in the list: SELECT musSongs.song, (SELECT CASE musPerfs.arttype WHEN "P" THEN musPeople.lastname WHEN "B" THEN musBands.bandname ELSE " " END FROM musPerfs JOIN musPeople ON musPeople.pkid = musPerfs.artid JOIN musBands ON musBands.pkid = musPerfs.artid WHERE musPerfs.songid = musSongs.pkid) AS artist, musRecs.pkid keyfield is here ^ in table.column format: "musRecs.pkid" From clause: FROM musSongs JOIN musPerfs ON musPerfs.songid = musSongs.pkid JOIN musRecs ON musRecs.perfid = musPerfs.pkid > >> I've never tried running a bizobj without a DataSource. What > >> happensif you set this to the name of the table? > > > > There are four tables involved in the query; which one would I use? > > Is there a 'main' table in the query? The one that would be > updated, >if you were writing this for updates? No. This is a lookup query, not an updatable query. I'm trying to get a list of recordings with artists. Artists can be People or Bands. However, it's not that simple; a recording is defined as a performance on a certain date (date left out of my examples for simplicity), a performance is defined as a song performed by an artist who may be either a Person or a Band. All of this stuff is mostly stored in the database as link tables containing only key values. To make sense of it for a user, complex queries with joins and derived fields are going to be commonplace. The only table mentioned in the FROM clause is musSongs; is that the one I should use? It seems arbitrary given the purpose of the query--not that there's anything wrong with that. But then the grid isn't going to be able to display my derived field, artist, is it? Since there is no field by that name in musSongs? > >> This should always be just a column name, not a > >> table.column name. If you're saying it should not be in table.column format in the *query*--well, if I did that in VFP I'd get an error saying something like "field pkid is ambiguous", since there are several references to "pkid" in the query. Are you saying I can put just "pkid" in the fields clause of a MySQL query and MySQL will know which one I want? > There can only be one 'pkid' in the data set. Right, and there is only one. > > In any case, I tried it that way; same error. > > Here was the error you reported: > > File "C:\Program Files\Dabo Runtime\dabo\db\dCursorMixin.py", >line 1362, >in _getRecordByPk >KeyError: 'musRecs.pkid' > > This means that there was no column in the dataset with the key >value of 'musRecs.pkid'. But there was. You can see it in the query I posted above. >You're saying that if you change the >KeyField to just 'pkid', you still get an error saying that you have >no key named 'pkid'? If so, then you don't have that name in your >result set. I don't know why, but that's what a KeyError means. Yes, if I just use KeyField = "pkid" I get KeyError: 'pkid'. If I don't have a KeyField statement at all, I get KeyError: ' ' -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.446 / Virus Database: 268.18.6/708 - Release Date: 3/2/2007 4:19 PM _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users
