"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
