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
