Hi Igor,
Thanks for your kind of help :-)
now my sql working, but result different mysql result.
1. mysql:
-----------
mysql> 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.variableLIKE 'OUT\_%' AND concat('OUTDISABLE_',substring(
v.variable,5)) NOT IN ( SELECT variable from globals WHERE variable LIKE
'OUTDISABLE\_%' ) ORDER BY variable;
+----------+--------+-------+
| variable | value | state |
+----------+--------+-------+
| OUT_1 | ZAP/g0 | off |
+----------+--------+-------+
------------------
2. sqlite :
------------
sqlite> SELECT t.variable, t.value, ifnull(d.value, ''||'off') state FROM
globals t LEFT JOIN globals d ON (substr(t.variable,5,length(t.variable)-4)
= substr(d.variable,12,length(t.variable) - 11) and t.variable LIKE 'OUT\_%'
ESCAPE '\' and d.variable LIKE 'OUTDISABLE\_%' ESCAPE '\') ORDER BY
t.variable;
AFTER_INCOMING||off
ALLOW_SIP_ANON|yes|off
CALLFILENAME|\"\"|off
DIALOUTIDS|1|off
DIAL_OPTIONS|tr|off
DIAL_OUT|9|off
DIRECTORY|last|off
DIRECTORY_OPTS||off
FAX||off
FAX_RX|system|off
FAX_RX_EMAIL|[EMAIL PROTECTED]|off
FAX_RX_FROM|[EMAIL PROTECTED]|off
INCOMING|group-all|off
IN_OVERRIDE|forcereghours|off
NULL|\"\"|off
OPERATOR||off
OPERATOR_XTN||off
OUT_1|ZAP/g0|off
PARKNOTIFY|SIP/200|off
RECORDEXTEN|\"\"|off
REGDAYS|mon-fri|off
REGTIME|7:55-17:05|off
RINGTIMER|15|off
TIMEFORMAT|kM|off
TONEZONE|us|off
TRANSFER_CONTEXT|from-internal-xfer|off
TRUNK_OPTIONS||off
VMX_CONTEXT|from-internal|off
VMX_LOOPDEST_CONTEXT||off
VMX_LOOPDEST_EXT|dovm|off
VMX_LOOPDEST_PRI|1|off
VMX_LOOPS|1|off
VMX_OPTS_DOVM||off
VMX_OPTS_LOOP||off
VMX_OPTS_TIMEOUT||off
VMX_PRI|1|off
VMX_REPEAT|1|off
VMX_TIMEDEST_CONTEXT||off
VMX_TIMEDEST_EXT|dovm|off
VMX_TIMEDEST_PRI|1|off
VMX_TIMEOUT|2|off
VM_DDTYPE|b|off
VM_GAIN||off
VM_OPTS||off
VM_PREFIX|*|off
-------------
thanks,
2008/2/13, Igor Tandetnik <[EMAIL PROTECTED]>:
>
> li yuqian <[EMAIL PROTECTED]> wrote:
> > now i try follow sql
> > SELECT t.variable, t.value, ifnull(d.value, ''||'off') state FROM
> > globals t LEFT JOIN globals d ON (substr(t.variable,5) =
> > substr(d.variable,12) and t.variable LIKE 'OUT\_%' ESCAPE '\' and
> > d.variable LIKE 'OUTDISABLE\_%' ESCAPE '\') ORDER BY t.variable;
> >
> > and get a SQL error: wrong number of arguments to function substr(),
> > still not get working :-(
>
> This statement works for me without any errors. Try upgrading to a more
> recent version of SQLite. If for some reason you can't, try replacing
>
> substr(t.variable,5)
>
> with
>
> substr(t.variable,5, length(t.variable) - 4)
>
> and similarly for the other occurence of substr.
>
> ''||'off' is equivalent to simply 'off' (concatenating anything to an
> empty string doesn't change that anything).
> --
> 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
