当我尝试在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语句中使用了别名,所以我很困惑为什么我收到错误.在这种情况下它是否有所不同？
解决方法
From the documentation(重点补充)：
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 BYclause,but not other clauses in the query.
所以你不能在where子句中引用别名,你现在在哪里：
... AND (account_amt NOT BETWEEN ... ...
别名在该点无效,因此它在其中一个表中查找具有该名称的列,但未找到一个.尽管顺序很好.
您需要使用重复的decode语句替换别名,或者可能使用子查询,然后引用外部查询中where子句中的别名,但这可能最终会降低效率,具体取决于您的其他条件的选择性.
