sql – ORA-00904解码别名上的无效标识符

当我尝试在select语句中使用解码的别名时,我遇到了标题中声明的错误.这是代码：

SELECT DISTINCT rl.complaint_date,decode(rl.judgement_date,null,rl.complaint_amt,rl.judgement_amt) as account_amt,rl.date_served1,rl.date_served2,rl.judgement_date,rl.skip_locate,rl.case_no,lcc.bal_range_min,lcc.bal_range_max,lcc.cost_range_min,lcc.cost_range_max,lcc.court,lcc.county AS lcc_county,ah.ACCOUNT,ah.transaction_code,ah.transaction_date,ah.rule_id,ah.amount,ah.description,r.state,r.zip_code,z.county AS ah_county,z.county_2,z.county_3,z.county_4
  FROM legal_address_skip las,racctrel r,ziplist z,legal_court_cost lcc,racctlgl rl,legal_transaction_review ah
  WHERE ah.ACCOUNT = rl.ACCOUNT
  AND ah.ACCOUNT = las.ACCOUNT(+)
  AND ah.ACCOUNT = r.ACCOUNT
  AND nvl(lpad(substr(r.zip_code,instr(r.zip_code,'-')-1),5,0),substr(r.zip_code,1,5)) = z.zip 
  AND r.state = lcc.state
  AND (REPLACE(lcc.county,' ','') = REPLACE(upper(z.county),'') 
       OR REPLACE(lcc.county,'') = REPLACE(upper(z.county_2),'')
       OR REPLACE(lcc.county,'') = REPLACE(upper(z.county_3),'') = REPLACE(upper(z.county_4),''))
  AND lcc.transaction_code = ah.transaction_code
  AND lcc.transaction_code = 1
  AND lcc.end_date IS NULL
  AND ah.amount NOT BETWEEN lcc.cost_range_min AND lcc.cost_range_max
  AND (account_amt NOT BETWEEN lcc.bal_range_min AND lcc.bal_range_max
      OR lcc.bal_range_min - account_amt NOT BETWEEN 0 AND 500)
  ORDER BY CASE 
           WHEN ah.amount NOT BETWEEN lcc.cost_range_min AND lcc.cost_range_max THEN 1
           WHEN ah.amount BETWEEN lcc.cost_range_min AND lcc.cost_range_max THEN 2 END,ah.amount;

我之前在select语句中使用了别名,所以我很困惑为什么我收到错误.在这种情况下它是否有所不同？

解决方法

You can use a column alias,c_alias,to label the immediately
preceding expression in the select list so that the column is
displayed with a new heading. The alias effectively renames the select
list item for the duration of the query. The alias can be used in the
ORDER BY clause,but not other clauses in the query.

所以你不能在where子句中引用别名,你现在在哪里：

...
AND (account_amt NOT BETWEEN ...
...

别名在该点无效,因此它在其中一个表中查找具有该名称的列,但未找到一个.尽管顺序很好.

您需要使用重复的decode语句替换别名,或者可能使用子查询,然后引用外部查询中where子句中的别名,但这可能最终会降低效率,具体取决于您的其他条件的选择性.

（编辑：衡阳站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!