SQL中具有无效标识符错误的子查询

本文介绍了SQL中具有无效标识符错误的子查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

想要提前选择游览日期和站点名称，总费用超过230，并且该游览有7人以上.完整的代码如下，联合工作的第一部分.

want to select tour date and site names in advance of total cost is more than 230 and that tour has more than 7 persons. the complete code is below, the first part of union works.

SELECT tour_date AS "Departure Date",  site_name "Site Name"
FROM partres, reservation, tour, site
WHERE partres.res_id = reservation.res_id
  AND reservation.tour_id = tour.tour_id
  AND tour.site_id = site.site_id
GROUP BY tour_date, site_name
HAVING COUNT(part_id) > 7

  UNION

SELECT tour_date AS "Departure Date",  site_name "Site Name"
FROM (
  SELECT res_id,tour_date,site_name, (res_partcost +NVL(RES_GEARCOST,0)) as "total_cost" 
  FROM reservation,site,tour) 
WHERE  reservation.tour_id = tour.tour_id
  AND tour.site_id = site.site_id
  AND total_cost > 230
GROUP BY tour_date, site_name;

我还是有错误

ORA-00904: "TOTAL_COST": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 437 Column: 7

谢谢

推荐答案

您需要在子查询中移动联接条件

You need to move your join conditions inside the subquery

SELECT tour_date AS "Departure Date",  site_name "Site Name"
FROM (
    SELECT res_id,tour_date,site_name, (res_partcost +NVL(RES_GEARCOST,0)) as "total_cost" 
    FROM reservation,site,tour
    WHERE reservation.tour_id = tour.tour_id
    AND tour.site_id = site.site_id ) Res1
WHERE Res1.total_cost > 230 // this will not be displayed in a result
GROUP BY tour_date, site_name;

这篇关于SQL中具有无效标识符错误的子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！