ORA

2023-05-01 07:27| 来源: 网络整理| 查看: 265

百度翻译此文有道翻译此文问题描述

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;

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?

推荐答案

From the documentation (emphasis added):

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.

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

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

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.

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.

其他推荐答案

Oracle runs the select query in the order below:

FROM clause WHERE clause GROUP BY clause HAVING clause SELECT clause ORDER BY clause

Based on the above, you can see that when you are in the WHERE part, the alias has not yet been created. If you would like to use results from the SELECT part, you can do that by modifying your query like this:

WITH q AS ( -- Your query without the extra AND ) SELECT * FROM q WHERE --put your check here

This way you will already have the alias available when you reach the WHERE part.

Hope this helps! :)

【本文地址】

ORA

ORA

今日新闻

推荐新闻