Hi Igor, Thanks for your reply.
i tried your sql, but get a error: --------- SQL error: no such function: substring -------- is something wrong? thanks 2008/2/12, Igor Tandetnik <[EMAIL PROTECTED]>: > > "li yuqian" <[EMAIL PROTECTED]> wrote in > message > news:[EMAIL PROTECTED] > > i am a newbie of sqlite3, we want porting the Freepbx(freepbx.org) to > > our > > project www.astfin.org, the freepbx can support sqlite3, but not very > > well, > > now i got a problem about sqlite3 > > --------------------- > > SELECT t.variable, t.value, d.value state FROM `globals` t JOIN > > (SELECT > > x.variable, x.value FROM globals x WHERE x.variable LIKE > > 'OUTDISABLE\_%') d > > ON substring(t.variable,5) = substring(d.variable,12) WHERE > > t.variable LIKE 'OUT\_%' UNION ALL SELECT v.variable, v.value, > > concat(substring(v.value,1,0),'off') > > state FROM `globals` v WHERE v.variable LIKE 'OUT\_%' AND > > concat('OUTDISABLE_',substring(v.variable,5)) NOT IN ( SELECT > > variable from > > globals WHERE variable LIKE 'OUTDISABLE\_%' ) ORDER BY variable > > You make it a little too complicated. Try this: > > SELECT t.variable, t.value, > ifnull(d.value, concat(substring(t.value,1,0),'off')) state > FROM globals t LEFT JOIN globals d ON ( > substring(t.variable,5) = substring(d.variable,12) and > t.variable LIKE 'OUT\_%' ESCAPE '\' and > d.variable LIKE 'OUTDISABLE\_%' ESCAPE '\' > ) > ORDER BY t.variable; > > Note that in SQLite, a backslash has no special meaning in LIKE > operator, unless assigned such meaning via ESCAPE clause (any character > can be used as an escape character, not just backslash). > -- > With best wishes, > Igor Tandetnik > > With sufficient thrust, pigs fly just fine. However, this is not > necessarily a good idea. It is hard to be sure where they are going to > land, and it could be dangerous sitting under them as they fly > overhead. -- RFC 1925 > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Li YuQian Your Astfin team ___________________________________ uClinux/Asterisk distribution for Blackfin CPU http://www.ucpbx.com http://astfin.org http://sourceforge.net/projects/astfin/ ___________________________________ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
