ORA-00904解码别名上的无效标识符 [英] ORA-00904 invalid identifier on decode alias

问题描述

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

I am running into the error stated in the Title when I attempt to use the alias of a decode in my select statement. Here is the code:

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语句中使用过别名，所以我很困惑为什么会为此出错.在这种情况下，它的工作方式是否有所不同?

I've used aliases before in select statements so I'm confused on why I am getting an error for this. Does it work differently in this situation?

推荐答案

您可以使用列别名c_alias来标记立即 选择列表中的前一个表达式，以便该列为 显示新的标题.别名有效地重命名了选择 查询期间的列表项. 别名可以在 ORDER BY子句，而不是查询中的其他子句.

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子句中引用别名，该子句现在位于:

So you can't refer to the alias in the where clause, where at the moment you have:

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

该别名当时无效，因此它正在其中一个表中查找具有该名称的列，但未找到该列.在order by中就可以了.

The alias isn't valid at that point, so it's looking for a column with that name in one of the tables, and doesn't find one. It's fine in the order by though.

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

You either need to replace the alias with the repeated decode statement, or possibly use a subquery and then refer to the alias in a where clause in an outer query, but that might end up being less efficient depending on how selective your other conditions are.

这篇关于ORA-00904解码别名上的无效标识符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！