I tried this, but sqlite3 show:
-------
SQL error: no such column: d.variable
-------
any idea? thanks
2008/2/12, Mohd Radzi Ibrahim <[EMAIL PROTECTED]>:
>
> Could you just use ORDER BY 1 ?
>
> best regards
> -- radzi --
> ----- Original Message -----
> From: "li yuqian" <[EMAIL PROTECTED]>
> To: <sqlite-users@sqlite.org>
> Cc: "Dimitar Penev" <[EMAIL PROTECTED]>; "Mark" <[EMAIL PROTECTED]>
> Sent: Tuesday, February 12, 2008 1:56 PM
> Subject: [sqlite] Help for sqlite syntax
>
>
> > Hi guys,
> >
> > 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
> > -------------------
> >
> > above sql working very well at mysql, but at sqlite3 will show:
> > ---------------
> > SQL error: ORDER BY term number 1 does not match any result column
> > ---------------
> >
> > the globals table is
> > ----------
> > CREATE TABLE `globals` (
> > `variable` char(20) NOT NULL default '',
> > `value` char(50) NOT NULL default '',
> > PRIMARY KEY (`variable`)
> > ) ;
> > ---------
> > and insert some contents to this table
> > ----------
> > INSERT INTO `globals` VALUES ('CALLFILENAME','\"\"');
> > INSERT INTO `globals` VALUES ('DIAL_OPTIONS','tr');
> > INSERT INTO `globals` VALUES ('TRUNK_OPTIONS','');
> > INSERT INTO `globals` VALUES ('DIAL_OUT','9');
> > INSERT INTO `globals` VALUES ('FAX','');
> > INSERT INTO `globals` VALUES ('FAX_RX','system');
> > INSERT INTO `globals` VALUES ('FAX_RX_EMAIL','[EMAIL PROTECTED]');
> > INSERT INTO `globals` VALUES ('FAX_RX_FROM','[EMAIL PROTECTED]');
> > INSERT INTO `globals` VALUES ('INCOMING','group-all');
> > INSERT INTO `globals` VALUES ('NULL','\"\"');
> > INSERT INTO `globals` VALUES ('OPERATOR','');
> > INSERT INTO `globals` VALUES ('OPERATOR_XTN','');
> > INSERT INTO `globals` VALUES ('PARKNOTIFY','SIP/200');
> > INSERT INTO `globals` VALUES ('RECORDEXTEN','\"\"');
> > INSERT INTO `globals` VALUES ('RINGTIMER','15');
> > INSERT INTO `globals` VALUES ('DIRECTORY','last');
> > INSERT INTO `globals` VALUES ('AFTER_INCOMING','');
> > INSERT INTO `globals` VALUES ('IN_OVERRIDE','forcereghours');
> > INSERT INTO `globals` VALUES ('REGTIME','7:55-17:05');
> > INSERT INTO `globals` VALUES ('REGDAYS','mon-fri');
> > INSERT INTO `globals` VALUES ('DIRECTORY_OPTS','');
> > INSERT INTO `globals` VALUES ('DIALOUTIDS','1');
> > INSERT INTO `globals` VALUES ('OUT_1','ZAP/g0');
> > INSERT INTO `globals` VALUES ('VM_PREFIX','*');
> > INSERT INTO `globals` VALUES ('VM_OPTS','');
> > INSERT INTO `globals` VALUES ('VM_GAIN','');
> > INSERT INTO `globals` VALUES ('VM_DDTYPE','u');
> > INSERT INTO `globals` VALUES ('TIMEFORMAT','kM');
> > INSERT INTO `globals` VALUES ('TONEZONE','us');
> > INSERT INTO `globals` VALUES ('ALLOW_SIP_ANON','no');
> > INSERT INTO `globals` VALUES ('VMX_CONTEXT','from-internal');
> > INSERT INTO `globals` VALUES ('VMX_PRI','1');
> > INSERT INTO `globals` VALUES ('VMX_TIMEDEST_CONTEXT','');
> > INSERT INTO `globals` VALUES ('VMX_TIMEDEST_EXT','dovm');
> > INSERT INTO `globals` VALUES ('VMX_TIMEDEST_PRI','1');
> > INSERT INTO `globals` VALUES ('VMX_LOOPDEST_CONTEXT','');
> > INSERT INTO `globals` VALUES ('VMX_LOOPDEST_EXT','dovm');
> > INSERT INTO `globals` VALUES ('VMX_LOOPDEST_PRI','1');
> > INSERT INTO `globals` VALUES ('VMX_OPTS_TIMEOUT','');
> > INSERT INTO `globals` VALUES ('VMX_OPTS_LOOP','');
> > INSERT INTO `globals` VALUES ('VMX_OPTS_DOVM','');
> > INSERT INTO `globals` VALUES ('VMX_TIMEOUT','2');
> > INSERT INTO `globals` VALUES ('VMX_REPEAT','1');
> > INSERT INTO `globals` VALUES ('VMX_LOOPS','1');
> > INSERT INTO `globals` VALUES ('TRANSFER_CONTEXT','from-internal-xfer');
> > ---------
> >
> > how i can change the sql for sqlite3, any idea, thanks
> >
> > --
> > 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
> >
>
>
> _______________________________________________
> 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
