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子句中的别名,但这也许最终会低落服从,详细取决于您的其他前提的选择性.

（编辑：湖南网）

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