I'm trying to substitute an empty string when a derived field returns None
(null).
In VFP 9 I can do it like this:
artfield = '(SELECT ICASE(musPerfs.arttype == "P", ' ;
+ 'ALLTRIM(musPeople.firstname) + " "
+ ALLTRIM(musPeople.lastname), ' ;
+ 'musPerfs.arttype == "B", ' ;
+ 'musBands.bandname," ") ' ;
+ 'FROM musPerfs ' ;
+ 'JOIN musPeople ON musPeople.pkid = musPerfs.artid ' ;
+ 'JOIN musBands ON musBands.pkid = musPerfs.artid ' ;
+ 'WHERE musPerfs.songid = musSongs.pkid)'
thequery = 'SELECT musSongs.song, IIF(ISNULL(' + artfield + '),"", ' ;
+ artfield + ') AS artist ' ;
+ 'FROM musSongs ' ;
+ 'JOIN musPerfs ON musPerfs.songid = musSongs.pkid ' ;
+ 'JOIN musRecs ON musRecs.perfid = musPerfs.pkid ' ;
+ 'ORDER BY song, artist'
That query works fine in VFP.
However, when I try to do the equivalent thing in MySQL, as follows, I get
a SQL syntax error:
artfield = '(SELECT CASE musPerfs.arttype WHEN "P" ' ;
+ 'THEN CONCAT(musPeople.firstname,"
",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)'
thequery = 'SELECT musSongs.songname, CASE IFNULL(' + artField + ') THEN "
" ELSE ' ;
+ artField + ' END ' ;
+ 'AS artist, ' ;
+ 'musRecs.pkid ' ;
+ 'FROM musSongs ' ;
+ 'JOIN musPerfs ON musPerfs.songid = musSongs.pkid ' ;
+ 'JOIN musRecs ON musRecs.perfid = musPerfs.pkid ' ;
+ 'ORDER BY songname, artist'
The query works fine if I don't try to use the IFNULL() substitution, so
it's not the use of double quote delimiters in MySQL.
It doesn't matter whether I use the artfield variable or write the whole
subquery out twice. I still get:
"'You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near ') THEN " "
ELSE (SELECT CASE musPerfs.arttype WHEN "P" THEN CONCAT(musPeople.fir' at
line 1')"
Any suggestions would be appreciated. Thanks.
Ken Dibble
www.stic-cil.org
_______________________________________________
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.
