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

Question

当我尝试在我的 select 语句中使用 decode 的别名时，我遇到了标题中所述的错误。这是代码:

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,0,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),' ','')
       OR REPLACE(lcc.county,' ','') = 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 语句中使用过别名，所以我很困惑为什么我会为此收到错误。在这种情况下它的工作方式是否有所不同？

Answer 1

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 BY clause, but not other clauses in the query.

所以你不能在 where 子句中引用别名，此时你有:

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

此时别名无效，因此它正在其中一个表中查找具有该名称的列，但没有找到。不过在 order by 中没问题。

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