Oracle ORA-00936子查询缺少表达式错误(Oracle ORA-00936 Missing Expression Error with Subquery)
我有一个工作查询通过Oracle 11g客户端连接到远程Oracle数据库。 要合并基于别名的参数，我将此查询用作主查询中带参数的子查询。 我没有看到任何事情 - 虽然事情显然是 - 所以在我再研究这个问题之前，我想我会把代码提交给专家：
SELECT * FROM ( SELECT "UNITS"."UnitNumber", "UNITS"."ModelYear", "UNITS"."Make", "UNITS"."Model", "UNITS"."Class3", "UNITS"."Class3Description", "UNITS"."TechnicalSpecification", SUBSTR("UNITS"."TechnicalSpecification", 13, 1) AS "FSC", "UNITS"."OwnerDepartment", "UNITS"."UnitStatus", CASE WHEN "UNITS"."Class3" = '1' AND SUBSTR("UNITS"."TechnicalSpecification", 13, 1) <> 'F' THEN 'Y' WHEN ("UNITS"."Class3" = '10' OR "UNITS"."Class3" = '15') AND SUBSTR("UNITS"."TechnicalSpecification", 13, 1) <> 'U' THEN 'Y' WHEN "UNITS"."Class3" = '11' AND SUBSTR("UNITS"."TechnicalSpecification", 13, 1) <> 'D' THEN 'Y' WHEN ("UNITS"."Class3" = '2' OR "UNITS"."Class3" = '8' OR "UNITS"."Class3" = '18') AND SUBSTR("UNITS"."TechnicalSpecification", 13, 1) <> 'C' THEN 'Y' WHEN ("UNITS"."Class3" = '3' OR "UNITS"."Class3" = '9' OR "UNITS"."Class3" = '17') AND SUBSTR("UNITS"."TechnicalSpecification", 13, 1) <> 'B' THEN 'Y' WHEN "UNITS"."Class3" = '16' AND SUBSTR("UNITS"."TechnicalSpecification", 13, 1) <> 'S' THEN 'Y' WHEN ("UNITS"."Class3" = '13' OR "UNITS"."Class3" = '4') AND SUBSTR("UNITS"."TechnicalSpecification", 13, 1) <> 'L' THEN 'Y' WHEN ("UNITS"."Class3" = '12' OR "UNITS"."Class3" = '14') AND SUBSTR("UNITS"."TechnicalSpecification", 13, 1) <> 'G' THEN 'Y' WHEN ("UNITS"."Class3" = '19' OR "UNITS"."Class3" = '20') AND SUBSTR("UNITS"."TechnicalSpecification", 13, 1) <> 'R' THEN 'Y' WHEN "UNITS"."Class3" = '5' AND SUBSTR("UNITS"."TechnicalSpecification", 13, 1) <> 'E' THEN 'Y' WHEN "UNITS"."Class3" = '6' AND SUBSTR("UNITS"."TechnicalSpecification", 13, 1) <> 'H' THEN 'Y' ELSE '' END AS "MISMATCH" FROM "MFIVE"."VIEW_ALL_UNITS" "UNITS" WHERE "UNITS"."OwnerDepartment" LIKE '580' AND "UNITS"."UnitStatus"='A' ) "U" WHERE "U"."MISMATCH" = {?Mismatch} ORDER BY "U"."UnitNumber"当我尝试运行此查询时，我收到“无法从数据库中检索日期”错误，ORA-00936：缺少表达式。
对于我的生活，我看不出问题是什么。 任何帮助将不胜感激。
I have a working query that connects to a remote Oracle database through the Oracle 11g Client. To incorporate a parameter based on an alias I used this query as a subquery with the parameter in the main query. I don't see anything the matter - though something obviously is - so before I grind on this for another hour I thought I would pose the code to the experts:
SELECT * FROM ( SELECT "UNITS"."UnitNumber", "UNITS"."ModelYear", "UNITS"."Make", "UNITS"."Model", "UNITS"."Class3", "UNITS"."Class3Description", "UNITS"."TechnicalSpecification", SUBSTR("UNITS"."TechnicalSpecification", 13, 1) AS "FSC", "UNITS"."OwnerDepartment", "UNITS"."UnitStatus", CASE WHEN "UNITS"."Class3" = '1' AND SUBSTR("UNITS"."TechnicalSpecification", 13, 1) <> 'F' THEN 'Y' WHEN ("UNITS"."Class3" = '10' OR "UNITS"."Class3" = '15') AND SUBSTR("UNITS"."TechnicalSpecification", 13, 1) <> 'U' THEN 'Y' WHEN "UNITS"."Class3" = '11' AND SUBSTR("UNITS"."TechnicalSpecification", 13, 1) <> 'D' THEN 'Y' WHEN ("UNITS"."Class3" = '2' OR "UNITS"."Class3" = '8' OR "UNITS"."Class3" = '18') AND SUBSTR("UNITS"."TechnicalSpecification", 13, 1) <> 'C' THEN 'Y' WHEN ("UNITS"."Class3" = '3' OR "UNITS"."Class3" = '9' OR "UNITS"."Class3" = '17') AND SUBSTR("UNITS"."TechnicalSpecification", 13, 1) <> 'B' THEN 'Y' WHEN "UNITS"."Class3" = '16' AND SUBSTR("UNITS"."TechnicalSpecification", 13, 1) <> 'S' THEN 'Y' WHEN ("UNITS"."Class3" = '13' OR "UNITS"."Class3" = '4') AND SUBSTR("UNITS"."TechnicalSpecification", 13, 1) <> 'L' THEN 'Y' WHEN ("UNITS"."Class3" = '12' OR "UNITS"."Class3" = '14') AND SUBSTR("UNITS"."TechnicalSpecification", 13, 1) <> 'G' THEN 'Y' WHEN ("UNITS"."Class3" = '19' OR "UNITS"."Class3" = '20') AND SUBSTR("UNITS"."TechnicalSpecification", 13, 1) <> 'R' THEN 'Y' WHEN "UNITS"."Class3" = '5' AND SUBSTR("UNITS"."TechnicalSpecification", 13, 1) <> 'E' THEN 'Y' WHEN "UNITS"."Class3" = '6' AND SUBSTR("UNITS"."TechnicalSpecification", 13, 1) <> 'H' THEN 'Y' ELSE '' END AS "MISMATCH" FROM "MFIVE"."VIEW_ALL_UNITS" "UNITS" WHERE "UNITS"."OwnerDepartment" LIKE '580' AND "UNITS"."UnitStatus"='A' ) "U" WHERE "U"."MISMATCH" = {?Mismatch} ORDER BY "U"."UnitNumber"When I attempt to run this query I recieve a "Failed to retrieve date from the database" error, ORA-00936: missing expression.
For the life of me I can't see what the problem is. Any assistance will be appreciated.
原文：https://stackoverflow.com/questions/37488788
更新时间：2022-06-05 16:06
最满意答案
恕我直言，最好的方法是在子查询中获得基本结果，并在外部查询中使用它们进行计算。 请注意，由于您只对两列中的
recruit_id = 49631感兴趣，因此可以将此条件移至where子句。 它也可能会略微提高查询的性能。 作为另一项改进，您可以使用更直接的count而不是sum使用其跳过nulls的质量：SELECT total_votes, total_yes, total_yes * 100 / total_votes AS yes_percent FROM (SELECT COUNT(vote) AS total_votes, COUNT(CASE WHEN vote = 1 THEN 1 END) as total_yes, FROM votes WHERE recruit_id = 49631) tThe neatest way to do this, IMHO, would be to have the basic results in a subquery and calculate using them in the outer query. Note that since you're only interested in
recruit_id = 49631in both columns, this condition can be moved to thewhereclause. It will also, probably, slightly improve the query's performance. As another improvement, you could use the more straight forwardcountinsteadsumby using its quality of skippingnulls:SELECT total_votes, total_yes, total_yes * 100 / total_votes AS yes_percent FROM (SELECT COUNT(vote) AS total_votes, COUNT(CASE WHEN vote = 1 THEN 1 END) as total_yes, FROM votes WHERE recruit_id = 49631) t
相关问答
更多-
恕我直言，最好的方法是在子查询中获得基本结果，并在外部查询中使用它们进行计算。 请注意，由于您只对两列中的recruit_id = 49631感兴趣，因此可以将此条件移至where子句。 它也可能会略微提高查询的性能。 作为另一项改进，您可以使用更直接的count而不是sum使用其跳过null s的质量： SELECT total_votes, total_yes, total_yes * 100 / total_votes AS yes_percent FROM (SELECT COUNT(vote) ...
-
使用相关子查询： SELECT week, day, hour, branch, sales, (sales/(SELECT SUM(sales) FROM test t2 WHERE t2.hour = t.hour and t2.week = t.week and t2.day = t.day ) ) * 100 AS Percentage FROM test t GROUP BY wee ...
-
这样的事情 SELECT machine , COUNT(DISTINCT machine) AS "nb" , COUNT(DISTINCT IF(qc_gsr = "green" AND qc_hr = "green" AND qc_acz = "green" AND qc_bre = "green" ), machine, NULL) ) AS "green_nb" , COUNT(DISTINCT IF(qc_gsr = "green" AND ...
-
如何使用php在mysql中计算匹配的标签百分比(How matched tags percentage can be calculated in mysql with php)[2023-08-07]
所以这就是答案。 其余的百分比计算基于用户的总标签数量。 SELECT p1.pid, COUNT(plid) as matched, p1.authorid FROM keywordmap km LEFT JOIN playlists p1 ON km.plid = p1.pid WHERE kwid IN ( SELECT kwid FROM playlists p2 LEFT JOIN keywordmap km2 ON p2.pid = km2.plid LEFT JOIN ke ... -
您可以使用案例并在单个选择匹配中查找所有可能的场景。 它会减少你的时间。 例 select id, count(*), sum(case when col1 between 0 and 10 then 1 else 0 end) col1_yes, sum(case when (col1 not between 0 and 10) and (col2 between 0 and 10) then 1 else 0 end) col1no_col2yes from table group by id; ...
-
您可以加入未分组或分组的总数，并将其除以总和查询。 这样，您只需进行一次总体选择即可获得更快的运行时间 SELECT cat, sum_atual, sum_atual/total_atual as percent_atual FROM ( SELECT categories.cat AS cat, SUM(atual) AS sum_atual FROM `table1` JOIN categories ON table1.category_id=categories.id ...
-
计算MySql中的百分比(Calculate Percentage in MySql)[2022-07-08]
在MySQL支持SUM（）OVER（...）等窗口函数之前，需要使用子查询来收集百分比计算所需的额外数据。 不太确定你需要什么，但希望这会有所帮助： SELECT i.Item_Category , i.Total , i.Quarter , i.Year , (i.Total * 100.0) / y.Total as Pct FROM ( SELECT Item_C ... -
您应该为交叉连接添加相同的条件。 目前，在您的选择中，您将所有survey_vote_count与所有where条件（visibility和survey_id）相加。 You should add the same condition to your cross join. At the moment, in your select you sum all survey_vote_count without the same where condition (visibility and survey_id ...
-
用MySQL计算百分比(Calculate percentage with MySQL)[2022-06-02]
您可以找到每种语言的行数，并将其除以英语计数，如下所示： 尝试这个： select l.*, concat(round(100 * count(t.languages_id) / t2.cnt,0),'%') from languages l left join translations t on l.id = t.languages_id cross join ( select count(*) cnt from translations where languages_id = ... -
您可以使用CROSS JOIN代替使用子查询来计算总和。 SELECT t1.Site, sum(t1.`Item Amount`) As Amount, sum(t1.`Item Weight`) AS MT, SUM(t1.`Item Amount`) / t2.itemSum * 100 AS `Item percentage`, SUM(t1.`Item Weight`) / t3.weightSum * 100 AS `Weight percentage` FROM table1 ...