> > I'm trying to substitute an empty string when a derived field returns None > > (null). > > > > Any suggestions would be appreciated. Thanks. > >You might not like my suggestion ;). If I understand what you're >doing, you're trying to assemble a list of records and the performer >from the Songs and Perfs table where the performer is either a "B"and >or a "P"erson, so you're joining those two tables in. Without trying >to get the syntax exactly right, I think what you want to do is: > >Select PersonName as Artist , SongName > + 'FROM musSongs ' ; > + 'JOIN musPerfs ON musPerfs.songid = musSongs.pkid ' ; > + 'JOIN musRecs ON musRecs.perfid = musPerfs.pkid ' ; > + 'JOIN musPeople ON musPeople.pkid = musPerfs.artid ' ; > where musSongsType = "P" > >UNION > >Select BandName as Artist , SongName > + 'FROM musSongs ' ; > + 'JOIN musPerfs ON musPerfs.songid = musSongs.pkid ' ; > + 'JOIN musRecs ON musRecs.perfid = musPerfs.pkid ' ; > + 'JOIN musBands ON musBands.pkid = musPerfs.artid ' ; > where musSongsType = "B" > >+ 'ORDER BY songname, artist'
Actually, I like it a lot. It returns actual values for artist that my query returned as null. Can you tell me why that is? (I know it's not the field truncating thing; I modified my original query with a PADL() and still got those nulls.) Thanks very much! Ken -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.446 / Virus Database: 268.18.7/711 - Release Date: 3/5/2007 9:41 AM _______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
