ORA-00904 invalid identifier on decode alias
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 BYclause, 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.
ORA-00904: invalid identifier issue with alias in SELECT
The column alias isn't available in the HAVING clause. (Just as specified by ANSI/ISO SQL.)
Either put the SUM() in the HAVING clause:
SELECT idproduct, SUM(quantity) AS amnt_sold
FROM [table]
GROUP BY idproduct
HAVING SUM(quantity) >= 3
Or, wrap the query up in a derived table, and then use the amnt_sold column name.
SELECT idproduct, amnt_sold
FROM
(
SELECT idproduct, SUM(quantity) AS amnt_sold
FROM [table]
GROUP BY idproduct
) dt
WHERE amnt_sold >= 3
ORA-00904 invalid identifier error
I think you want to fetch those records having count > 1. So you can't use Indentifier C in the WHERE clause of the same query.
select NO, count(JOINT_NO) as c
from JOINT
WHERE HOLDER = 'Y'
GROUP BY NO
HAVING COUNT(JOINT_NO)>1;
Oracle Function ORA-00904 invalid indentifier
If I understand well, you need to use a decode on the 'A' string and the issue is in escaping the quotes within dynamic code.
The point could be that you do not need dynamic SQL at all, for example:
CREATE OR REPLACE FUNCTION F_BONITETA2(dav IN VARCHAR2, tip IN NUMBER)
RETURN NUMBER IS
sco NUMBER;
BEGIN
BEGIN
SELECT score
INTO sco
FROM sco_sif_score
WHERE sif_kat = 12
AND tip_pod = tip
AND vrednost IN (SELECT DECODE(a.boniteta, 'A', 1, 2)
FROM sco_boniteta a
INNER JOIN (SELECT DISTINCT a.par_davcna, a.par_reg
FROM scoring_gvin a) b
ON a.maticna = b.par_reg
WHERE b.par_davcna = dav);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
RETURN sco;
END F_BONITETA2;
Also, accordig to Boneist's comment, if you do not need the BEGIN...END block for some other reason, this could be a way to rewrite your code:
CREATE OR REPLACE FUNCTION F_BONITETA2(dav IN VARCHAR2, tip IN NUMBER)
RETURN NUMBER IS
sco NUMBER;
BEGIN
SELECT score
INTO sco
FROM sco_sif_score
WHERE sif_kat = 12
AND tip_pod = tip
AND vrednost IN (SELECT DECODE(a.boniteta, 'A', 1, 2)
FROM sco_boniteta a
INNER JOIN (SELECT DISTINCT a.par_davcna, a.par_reg
FROM scoring_gvin a) b
ON a.maticna = b.par_reg
WHERE b.par_davcna = dav);
RETURN sco;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
return NULL;
END F_BONITETA2;
In general, these could be a couple of ways to handle a string containing a quote by doubling the quotes or by the means of the alternative quoting method:
SQL> select 'This is a quote '' and these are two quotes ''''.' from dual UNION ALL
2 select q'[This is a quote ' and these are two quotes ''.]' from dual;
'THISISAQUOTE''ANDTHESEARETWOQUOTES''''.'
----------------------------------------------
This is a quote ' and these are two quotes ''.
This is a quote ' and these are two quotes ''.
PL-SQL: ORA-00904 - Invalid Identifier - Column in Select and Pivot Clause
You must use double quotes for the column in PIVOT as follows:
Select * from
(
SELECT "Team", "Tax Status", sum("Total Value") "Total Value"
FROM
(
select (A.account_roll_number) "Roll Number", ALU.DESCRIPTION "Assessor", A.account_total_property_value as "Total Value",
TaxLU.DESCRIPTION "Tax Status",
CASE
when A.assessor_id in ('ATHENDRATA', 'BRTHOMPSON', 'FTACIUNE', 'HPHILLIPS', 'JDCHU', 'JRYOUNG1', 'MHARTMANN', 'NCCHAN', 'RLEE5', 'SBERZINS') then 'Industrial'
when A.assessor_id in ('ASMTDWA','ASMTEB', 'ASMTWS', 'BBROCKLEBANK', 'CCHICHAK', 'CYMAU', 'GJONES4', 'IYPAU', 'JTGREER', 'KHOUSTON', 'LGMORRISON', 'MMCKENZIE1', 'MWALZ', 'SKUANG', 'STBAZIN', 'TKNGUYEN1', 'ASHIELDS') then 'Single Residential'
when A.assessor_id in ('BTANG', 'CMACMILLAN1', 'DGENCARELLI', 'EWU1', 'JWEHLER', 'LMDUNBAR', 'LWONG4', 'MGULOWSKA', 'RLEE1', 'SHAMMOUD', 'SLTURNER', 'YWANG') then 'Multi Residential'
when A.assessor_id in ('CMULENGA', 'EPOPOVICS', 'GFULLER', 'IMCDERMOTT', 'JERMUBE', 'JNSTEVENSON', 'JPLEPINE', 'KBUCKRY', 'KDALMHJELL', 'NPHAM1', 'PGKERSEY', 'SMSAMPLE') then 'Special Purpose and Land'
when A.assessor_id in ('ASMTHN', 'DCARSON', 'DLIDGREN', 'DMCCORD', 'EBORISENKO', 'HYAU1', 'MCTRIMBLE', 'RJTHARAKAN', 'TBJOHNSON1', 'VWONG1', 'WGIBBS', 'YYE', 'AVPETERS') then 'Office'
when A.assessor_id in ('AKEAST', 'BLTHOMPSON', 'BNELSON1', 'JCRUJI', 'JWONG1', 'KGARDINER', 'KMHAUT', 'NTNGUYEN', 'RTLUCHAK', 'SGILL3', 'THEGER1', 'TJLUDLOW', 'ZRGEIB') then 'Retail'
else 'Other'
END as "Team"
from REP_DBA.AB000_ACCOUNT A
join REP_DBA.LU_ASMT_ASSIGN_ASSESSOR ALU
on A.assessor_id = ALU.code
-- Decode the Tax Status from the LU Table
join rep_dba.LU_ASMT_ACCT_TAX_STATUS TaxLU
on TaxLU.CODE = A.account_tax_status
where A.rollyear = :rollyear
and A.account_type = 'P'
and A.account_status = 'AP'
and A.account_total_property_value is not null
-- ORDER BY account_total_property_value DESC -- not needed
)
GROUP BY "Team", "Tax Status"
)
pivot
(
min("Total Value") -- here
for ("Tax Status")
in ('Exempt from Taxation' as "Exempt", 'Taxable' as "Taxable",
'Tax Agreement - Operator of a Public Utility' as "Tax Agreement", -- duplicate
'"Assessed Person" Tax Agreement' as "Tax Agreement 1", -- changed alias, added 1
'Grant in Place of Tax' as "Grant", 'Council Tax Cancellation or Refund' as "Council Cancel/Refund")
)
Cheers!!
Using an Alias in a WHERE clause
This is not possible directly, because chronologically, WHERE happens before SELECT, which always is the last step in the execution chain.
You can do a sub-select and filter on it:
SELECT * FROM
(
SELECT A.identifier
, A.name
, TO_NUMBER(DECODE( A.month_no
, 1, 200803
, 2, 200804
, 3, 200805
, 4, 200806
, 5, 200807
, 6, 200808
, 7, 200809
, 8, 200810
, 9, 200811
, 10, 200812
, 11, 200701
, 12, 200702
, NULL)) as MONTH_NO
, TO_NUMBER(TO_CHAR(B.last_update_date, 'YYYYMM')) as UPD_DATE
FROM table_a A
, table_b B
WHERE A.identifier = B.identifier
) AS inner_table
WHERE
MONTH_NO > UPD_DATE
Interesting bit of info moved up from the comments:
There should be no performance hit.
Oracle does not need to materialize
inner queries before applying outer
conditions -- Oracle will consider
transforming this query internally and
push the predicate down into the inner
query and will do so if it is cost
effective. – Justin Cave
Oracle newbie error: ORA-00904 Invalid identifier when using case when
You can't refer an alias in the same level of the query.
You can replace the subquery...
select *
from (select a.*, rownum rnum
from (select id_edition, id_document, name, extension, creation_date,
(select inscription_date from edition_student_d0 where id_edition = 12345 and id_third = 12345) inscription_date,
case when trunc((select inscription_date from edition_student_d0 where id_edition = 12345 and id_third = 12345) + 90) <= trunc(sysdate) then null
else url
end as url
from upd_edition_doc_d0
where id_edition = 1071591
order by creation_date desc) a
where rownum <= 10 )
where rnum >= 1
OR move the case one level up.
select *
from (select a.*,
case when trunc(inscription_date + 90) <= trunc(sysdate) then null
else url
end as url,
rownum rnum
from (select id_edition, id_document, name, extension, creation_date,
(select inscription_date from edition_student_d0 where id_edition = 12345 and id_third = 12345) inscription_date
from upd_edition_doc_d0
where id_edition = 1071591
order by creation_date desc) a
where rownum <= 10 )
where rnum >= 1
Related Topics
MySQL Datetime Group by 15 Mins
Mssql Dynamic Pivot Column Values to Column Header
How to Add Number of Business Days to Given Date
MySQL - How to Order Results by Alternating (1,2,3, 1, 2, 3, 1, 2, 3,) Rows, Is It Possible
Pivot - SQL - Values from Subquery
Does the Number of Columns Returned Affect the Speed of a Query
How to Find Which Columns Don't Have Any Data (All Values Are Null)
Cast VS Ssis Data Flow Implicit Conversion Difference
Update Table with Random Record in Update Statment in SQL Server
SQL Query on Multiple Databases
Compare Strings Ignoring Accents in SQL (Oracle)