我有一个查询,有3个联合和一个order by子句。
如果s1, s2, s3和s4是包含select查询的字符串变量,oc是包含order by子句的字符串变量,那么完整的查询是:
s1 & " union " & s2 & " union " & s3 & " union " & s4 & oc
每个select都是完全相同的格式,只是参数不同,并且每个select都包含多个连接。
查询失败,报错
运行时错误:'-2147467259 (80004005)':
缺少eventtable表的from-clause entry;错误而执行查询
但是,以下所有的变量都不会导致错误
s1 & " union " & s2 & " union " & s3 & " union " & s4
s1 & oc
s2 & oc
s3 & oc
s4 & oc
导致错误的查询在.mdb格式的同一数据库上正常工作。
postgresql服务器为postgres 9.0程序为vb6, ado2.6,连接字符串为
提供者= MSDASQL;司机= {PostgreSQLANSI};服务器= localhost;数据库;= dbname UID = postgres; PWD =密码;CONNSETTINGS =Datestyle来"DMY"% 3 b; BOOLSASCHAR = 0; TEXTASLONGVARCHAR = 1; TrueIsMinus1 = 1;
编辑每个select语句的结构如下:
select Charges.CHARGESCALENAMEF1, MATERIALST12.MATERIALNAMEF1
, workspecificationst11.*
, clientdetail.clientidf9
, eventTbl.*
from Charges
right join (MATERIALST12
right join (workspecificationst11
right join (clientdetail
right join eventTbl
on clientdetail.clientidf9 = eventTbl.clientidf2
) on workspecificationst11.serialnumf5 = eventTbl.workf5
) ON MATERIALST12.SERIALNUMF3 = eventTbl.MATERIALF8
) ON Charges.SERIALNUMF2 = eventTbl.CHARGESCALEF7
where eventTbl.clientidf2 = 15249
and eventTbl.workf5 = 40
and eventTbl.workstatusf14 = 2
and eventTbl.section = 1
and workspecificationst11.showinaccountsf9 = true
;
删除所有括号:
select Charges.CHARGESCALENAMEF1, MATERIALST12.MATERIALNAMEF1
, workspecificationst11.*
, clientdetail.clientidf9
, eventTbl.*
from Charges
right join eventTbl ON Charges.SERIALNUMF2 = eventTbl.CHARGESCALEF7
right join clientdetail on clientdetail.clientidf9 = eventTbl.clientidf2
right join MATERIALST12 ON MATERIALST12.SERIALNUMF3 = eventTbl.MATERIALF8
right join workspecificationst11 on workspecificationst11.serialnumf5 = eventTbl.workf5
where eventTbl.clientidf2 = 15249
and eventTbl.workf5 = 40
and eventTbl.workstatusf14 = 2
and eventTbl.section = 1
and workspecificationst11.showinaccountsf9 = true
如果我理解正确,完整的查询最终看起来像下面这样,尽管我已经将其简化为两个SELECT
语句,它们之间有一个UNION
,最后是一个ORDER BY
子句。根据你上面的评论,你发现:
在 ORDER BY
子句中的table alias
(在您的情况下eventTbl
)在查询包含UNION
时不起作用。
我怀疑这是因为每个select查询都有一个eventTbl
的本地表别名,但总的来说,在整个查询范围内,查询没有表别名。此外,ORDER BY
is适用于整个结果集,而不是单个SELECT
语句。
SELECT Charges.CHARGESCALENAMEF1, MATERIALST12.MATERIALNAMEF1
, workspecificationst11.*
, clientdetail.clientidf9
, eventTbl.*
from Charges
right join (MATERIALST12
right join (workspecificationst11
right join (clientdetail
right join eventTbl
on clientdetail.clientidf9 = eventTbl.clientidf2
) on workspecificationst11.serialnumf5 = eventTbl.workf5
) ON MATERIALST12.SERIALNUMF3 = eventTbl.MATERIALF8
) ON Charges.SERIALNUMF2 = eventTbl.CHARGESCALEF7
where eventTbl.clientidf2 = 15249
and eventTbl.workf5 = 40
and eventTbl.workstatusf14 = 2
and eventTbl.section = 1
and workspecificationst11.showinaccountsf9 = true
UNION
SELECT Charges.CHARGESCALENAMEF1, MATERIALST12.MATERIALNAMEF1
, workspecificationst11.*
, clientdetail.clientidf9
, eventTbl.*
from Charges
right join (MATERIALST12
right join (workspecificationst11
right join (clientdetail
right join eventTbl
on clientdetail.clientidf9 = eventTbl.clientidf2
) on workspecificationst11.serialnumf5 = eventTbl.workf5
) ON MATERIALST12.SERIALNUMF3 = eventTbl.MATERIALF8
) ON Charges.SERIALNUMF2 = eventTbl.CHARGESCALEF7
where eventTbl.clientidf2 = 15249
and eventTbl.workf5 = 40
and eventTbl.workstatusf14 = 2
and eventTbl.section = 1
and workspecificationst11.showinaccountsf9 = true
ORDER BY eventbl.datef1, eventtbl.eventidf15;
从ORDER BY
中删除表别名有效,因为作为单个查询,上面的查询返回datef1
列和eventiff15
列,但没有在整个结果集中定义表别名。
从
ORDER BY
子句中删除表别名:
ORDER BY datef1, eventidf15;
我想你可以使表别名工作,如果你这样做:
SELECT * FROM
( Original full query goes here without the order by) AS eventTbl
ORDER BY eventbl.datef1, eventtbl.eventidf15;
WITH parms as (
SELECT 15249 as clntid, 40 as wrkf, 2 as wkstat, sect = 1
UNION ALL
SELECT 14349 as clntid, 37 as wrkf, 23 as wkstat, sect = 5
UNION ALL
SELECT 16822 as clntid, 106 as wrkf, 55 as wkstat, sect = 2
UNION ALL
SELECT 24253 as clntid, 9 as wrkf, 43 as wkstat, sect = 9
)
from
CHARGES
right join
eventTbl
ON Charges.SERIALNUMF2 = eventTbl.CHARGESCALEF7
right join
clientdetail
on clientdetail.clientidf9 = eventTbl.clientidf2
right join
MATERIALST12
ON MATERIALST12.SERIALNUMF3 = eventTbl.MATERIALF8
right join
workspecificationst11
on workspecificationst11.serialnumf5 = eventTbl.workf5
RIGHT JOIN
PARMS
ON (
eventTbl.clientidf2 = PARMS.clntid
and eventTbl.workf5 = PARMS.wrkf
and eventTbl.workstatusf14 = PARMS.wkstat
and eventTbl.section = PARMS.sect
)
WHERE
workspecificationst11.showinaccountsf9 = true
对我来说,问题很简单,我把表名拼错了,正如其他答案指出的那样,SQL认为那是表的别名。