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