I'm facing an issue "ORA-00904". Below is the test case. Both the queries are different. I'm only focussed to find out the reason.
CREATE TABLE ACCT
(
ACCTNBR NUMBER(10)
) ;
[Code].....
In first query I'm able to refer to table alias A but in second query I'm not able to refer it. The only difference is that in second query I've not used outer table at second level. Is it the desired behaviour?
I'm getting the flowing error:
Error starting at line 1 in command:
INSERT INTO driver (registration, make, model, gvw, year, body) VALUES('4585 AW','ALBION','RIEVER',20321,1963, ' ');
Error at Command Line:1 Column:53
Error report:
[code]...
Error starting at line 1 in command:
INSERT INTO driver (registration, make, model, gvw, year) VALUES('4585 AW','ALBION','RIEVER',20321,1963)
Error at Command Line:1 Column:53
Error report:
SQL Error: ORA-00904: "YEAR": invalid identifier
00904. 00000 - "%s: invalid identifier"
i have columns both called year and body, yet I'm getting errors
The following query gives me "Invalid Identifier error: ORA-00904.
SELECT TMADMIN.pkg_twatch_invdb.Max_Trk_Date(tmadmin.sites.study_id,tmadmin.sites.site_id, 1000280)) "Qualified Date"
FROM
tmadmin.sites
"tmadmin' is the Schema Name
"pkg_twatch_invdb" is the package name
"max_trk_date" is a function inside the package (returns date)
I am using the same format for other function, it doesn't give any errors.
how to play around with NDS dynamic sql and I'm trying to add a column on the fly.Basically the procedure is trying to take a table name, column name, and eventually a data type and adds it to a table.
It works fine without the bind variable for the column name, accepting the table name on the fly.As soon as it tries to use the column name I get an ORA-00904 invalid identifier exception.
Here is the procedure I'm using
CODEcreate or replace
procedure test(tbl_name varchar2, col_name varchar2) IS
qry varchar2(500);
begin
[code]....
Here is how I'm executing it.
CODEexecute test(tbl_name => 'BB_SHOPPER', col_name => 'MEMEBER');
I have error ORA-00904: "USERNAME" : identificateur non valide. What is the problem ?
My command impdp :
impdp datapump/password@%ORACLE_SID% DIRECTORY=datapump schemas=gcom INCLUDE=PROCEDURE remap_tablespace=gpao_indx:indx remap_tablespace=gpao_data:data DUMPFILE=%ORA_DUMPFILE% LOGFILE=Imp_%annee%%mois%%jour%%hh%%min%%sec%_%ORACLE_SID%.log
The result :
;;;
Import: Release 10.2.0.4.0 - Production on Jeudi, 15 Septembre, 2011 17:47:16
Copyright (c) 2003, 2007, Oracle. All rights reserved.
;;;
Connecté à : Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Table maître "DATAPUMP"."SYS_IMPORT_SCHEMA_29" chargée/déchargée avec succès
[Code]....
I am really new to oraclesql and I'm trying to run a simple update statement and I get the: ERROR single-row subquery returns more than one row:
UPDATE EMP_TYP_T A
SET A.ACTV_IND = (SELECT B.ISONBOARD FROM
TMPUSERS B
WHERE B.emp_id = A.emp_id);
What i'm trying to do is count the number of rows those sub-queries return.
As i understand it, that error happens when more than 1 row is returned.
Here is my query:
SQL> SELECT
2 a.ssg_nme,
3 b.sftw_prod_nme,
4 COUNT((SELECT db_srvr_id FROM inv.db_srvr_t WHERE node_id = (SELECT node_id FROM inv.node_t WH
ERE node_loc_cde IN (5, 7, 8)))),
5 COUNT((SELECT db_srvr_id from inv.db_srvr_t WHERE row_status_cde = 2)),
6 COUNT((SELECT db_id from inv.db_t WHERE row_status_cde = 2 AND db_srvr_id = 1207))
7 FROM
8 inv.cde_ssg_t a, inv.sftw_prod_t b, inv.db_srvr_t c, inv.appl_t d, inv.db_t e
9 WHERE d.appl_id = e.appl_id
10 AND e.db_srvr_id = c.db_srvr_id
11 AND d.appl_ssg_cde IN (6, 9)
12 GROUP BY b.sftw_prod_nme, a.ssg_nme;
COUNT((SELECT db_id from inv.db_t WHERE row_status_cde = 2 AND db_srvr_id = 1207))
*
ERROR at line 6: ORA-01427: single-row subquery returns more than one row
I am relatively new to SQL and I am getting the below error, how to resolve this issue for my query?
ORA-01427: single-row subquery returns more than one row
nvl ( ( select add_qual_code from crm_add_qual WHERE tlc=q.tlc AND valid_since=q.valid_since AND ac_type_code=q.ac_type_code) , ' ') as add_qualification from qualification q
I have this sybase code below
UPDATE PS_LM_ML_ELI_D_TAO
SET NID_SPECIAL_CHAR =
H.NATIONAL_ID ,LAST_NAME_SRCH = SUBSTRING(F.LAST_NAME, 1 , 20),
[Code]....
i have changed the query as above.. but while running i see that i get subquery fetches more than on more. changing the query accordingly
I am trying to do this:
UPDATE projectdetails SET projectdetails.projectname=(SELECT projectid FROM project WHERE projectdetails.projectname = project.projectname);
but it returns with the error: single row subquery returns more than 1 row
I tried in the same PROJECTDETAILS table with another table A3, it works fine. but not for the PROJECT table.
Below is the project table (i have 2055 rows)
projectidprojectname1NEWEST2PARK WEST3THE VISION4PEOPLE'S PARK CENTRE5REFLECTIONS AT KEPPEL BAY6THE CLIFT7CLEMENTIWOODS CONDOMINIUM8HUNDRED TREES9PASIR VIEW PARK10CARABELLE11PARC IMPERIAL
below is the projectdetails table:
ProjectnameCORALS AT KEPPEL BAY20247502294883Strata101 to 05Jun-1399 yrs lease commencing from 20071CORALS AT KEPPEL BAY278688018901475Strata101 to 05Jun-1399 yrs lease commencing from 20071CORALS AT KEPPEL BAY15250502180700Strata101 to 05Jun-1399 yrs lease commencing from 20071ECHELON216298016471313Strata121 to 25Jun-1399 yrs lease commencing from 20121WHITEHAVEN135600012851055Strata101 to 05Jun-13Freehold1
I am using Anchored datatype wheere the table_name which lies in another schema and the current scheam has select insert update delete access on the the said table of the schema.
Example in current schema LL_TAR
I have defined a varaible in a script/procedure
V_TAG LL_TMR.TAG_DETAILS.TAG_VAL%type where TAG_VALUE column lies in a table TAG_DETAILS which lies
in LL_TMR.
During compilation it gives me an error PLS-00904 saying insufficient privilege. This issue and still use anchored datatype.Or anchored datatype of tables which lie in different schema on the same database server is not allowed? Cause i had read that on some websites that it is allowed for a table which resides on a different schema on a database.
I'm having an issue creating a table which references a larger table of mine.It gives me the error:
ORA-00904: "EID": invalid identifier
when issuing the command:
CREATE TABLE Phone (P_num CHAR(7), P_type VARCHAR2(10), PRIMARY KEY(P_num), FOREIGN KEY(EID) REFERENCES Employee ON DELETE CASCADE);
I need the employee id (EID) to be the primary key for my table Employee, as described here:
DESC Employee;
Name Null? Type
----------------------------------------- -------- ----------------------------
EID NOT NULL CHAR(7)
E_NAME VARCHAR2(15)
E_ADDRESS VARCHAR2(30)
And since Phone has its own primary key of Pnum, I find it weird that it won't let me reference Phone back to Employee using EID.
I have created one user in DB using below command
SQL> create user user1 identified by user1;
User created.
SQL> grant dba to user1;
Grant succeeded.
SQL>
I am trying to run below query
SQL> select dbms_random.value(-9223372036854775808, 9223372036854775807) from dual;
select dbms_random.value(-9223372036854775808, 9223372036854775807) from dual
*
ERROR at line 1: ORA-00904: : invalid identifier
I am getting "ORA-00904: : invalid identifier" even after giving the DBA right to user.When I run the same query using the sysdba , I am able to run the query
SQL> conn / as sysdba
Connected.
SQL> select dbms_random.value(-9223372036854775808, 9223372036854775807) from dual;
DBMS_RANDOM.VALUE(-9223372036854775808,9223372036854775807)
-----------------------------------------------------------
-5.113E+18
SQL>
i have this code.
select * from openquery(serverlinkeodwmain, 'SELECT COUNT(CNTR_N) cnt FROM OPS_DLY_AGING_DTL
WHERE BATCH_ID = DateADD(day , datediff(day, 0, getdate()), 0)')
the error
OLE DB provider "MSDAORA" for linked server "serverlinkeodwmain" returned message "ORA-00904: "DATEADD": invalid identifier".
is it because dateadd function is sql and i need to change it to oracle? if so how do i do that? i dont know much about oracle.
i need to get the current date without time. my records look like this
2011-08-16 00:00:00.000
I want run a procedure from a easy sql select.I have write this code to run this procedure, but response with a oracle error:
select [procedure_name](:data1, :data2) as test from dual
the oracle error is this:
ORA-00904: "PROCEDURENAME": invalid identifier
Here is the code I am using:
CREATE OR REPLACE
PROCEDURE CUBE_VIEW (VAR_DT IN VARCHAR2 DEFAULT '')
AUTHID CURRENT_USER
AS
START_DATE NUMBER;
END_DATE NUMBER;
VAR DATE;
BEGIN
IF VAR_DT IS NULL THEN
[code]....
The code complies successfully but when I am executing the code, here is the error I get. The code works successfully when I manually insert the values (as done in the commented out statement) but when use it from variable, it gives me error.
Connecting to the database LocalEnvironment.
ORA-00904: "END_DATE": invalid identifier
ORA-06512: at "TESTING.CUBE_VIEW", line 18
ORA-06512: at line 6
START DATE IS 90301
END DATE IS 111201
Process exited.
Disconnecting from the database LocalEnvironment.
I create a table:
create table myTable (
id number primary key,
name varchar2(100),
doc_content clob,
creation_date date default sysdate)
/
And then used dbms_metadata.get_ddl to retrieve its DDL:
select dbms_metadata.get_ddl('TABLE', 'MYTABLE') myDDL
2 from dual
3 /
I got the following error.
ERROR at line 1:
ORA-00904: "DBMS_METADAGE"."GET_DDL": invalid identifier
run catproc.sql to create pl/sql package But the error still occurs. I'm trying to understand dbms_metadata.
I have trouble reading a field called "Description" from a SQL Server 2008 DB using the gateway:
SQL> select "Description"
from opportunity@mscrm
select "Description"
from opportunity@mscrm
*
Error at line 1
ORA-00904: "Description": ungültiger Bezeichner
My first thought was, that "Description" was a reserved word in Oracle but it's not:
SQL> create table delete_this ("Description" varchar2 (50 char))
Table created.
SQL> select "Description" from delete_this
no rows selected.
So everythings fine here. Then I thought, that I might have mispelled the column name, but no:
SQL> select column_name
from v_mscrm_tab_columns
where table_name = 'opportunity'
and column_name like 'Desc%'
[code]....
First I got errors when I did the export with an export tool of Release 11.1.0.7.0 and there were lot of ORA-00904 and ORA-01003 like the following:
XP-00008: ORACLE error 904 encountered
ORA-00904: "MAXSIZE": invalid identifier
. . exporting table XRATE_INFO
EXP-00008: ORACLE error 1003 encountered
ORA-01003: no statement parsed
Then I tried to do export with another version of export tool (Release 10.2.0.4.0) The errors changed to the follwing:
.about to export FM's tables via Direct Path...
EXP-00008: ORACLE error 904 encountered
ORA-00904: "POLTYP": invalid identifier
EXP-00000: Export terminated unsuccessfully
The export tool I used is of Release 10.2.0.4.0
I recently performed an upgrade on a new server from oracle 10gr2 to oracle 11gr2 (11.2.0.3).
I take the rman backup from oracle 10g server and restore it on new server where I installed oracle 11gr2.
But on my previous oracle 10gr2 server I enabled the auditing. After doing successful upgrade now when I try to login with any user except sys I receive the following error:
SQL> conn scott/tiger
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "OBJ$EDITION": invalid identifier
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "OBJ$EDITION": invalid identifier
I got the workaround by setting the parameter audit_trail=FALSE (Previous value was DB_EXTENDED) .But I want my auditing to be enabled as per y requirements.
I was reading a tutorial for analytical function and i found something like this
sum(princial) keep(dense_rank first order by d_date) over partition by (userid, alias, sec_id, flow, p_date)
How to translate this into simple queries / subquery? i am aware that analytical function are faster but i would like to know how this can translate to using query without analytical function.
I have a database containing the following after entering the following sql command
SELECT TITLES.TITLE_ID AS TITLE_ID, (PRICE * SALES),
TITLES.ROYALTY_RATE AS ROYALTLY_RATE,
AUTHOR_TITLES.ROYALTY_SHARE AS ROYALTY_SHARE,
AUTHORS.FNAME AS FNAME, AUTHORS.LNAME AS LNAME
FROM TITLES, AUTHOR_TITLES, AUTHORS
WHERE TITLES.TITLE_ID = AUTHOR_TITLES.TITLE_ID
AND AUTHORS.AU_ID = AUTHOR_TITLES.AU_ID
TIT (PRICE*SALES) ROYALTLY_RATE ROYALTY_SHARE FNAME LNAME
--- ------------- ------------- ------------- --------------- ---------------
T01 12446,34 ,05 1 Sarah Buchman
T02 190841,7 ,06 1 Sarah Buchman
T03 1025396,65 ,07 1 Christian Kells
T04 168882,99 ,08 ,6 Hallie Hull
T04 168882,99 ,08 ,4 Klee Hull
T05 1400008 ,09 1 Klee Hull
T06 225834 ,08 1 Wendy Heydemark
T07 35929790 ,11 ,5 Wendy Heydemark
T07 35929790 ,11 ,5 Klee Hull
T08 40950 ,04 1 Kellsey
T09 69750 ,05 1 Kellsey
TIT (PRICE*SALES) ROYALTLY_RATE ROYALTY_SHARE FNAME LNAME
--- ------------- ------------- ------------- --------------- ---------------
T10 1 Wendy Heydemark
T11 752042,77 ,07 ,3 Hallie Hull
T11 752042,77 ,07 ,3 Klee Hull
T11 752042,77 ,07 ,4 Kellsey
T12 1299012,99 ,09 1 Wendy Heydemark
T13 313905,33 ,06 1 Sarah Buchman
17 rows selected.
What I need to do is create a subquery and use Aggregation to list the author receiving the greatest royalties on revenue. so i used the command to get the royalties
SELECT TITLES.TITLE_ID AS TITLE_ID, (PRICE * SALES),
AUTHORS.FNAME AS FNAME, AUTHORS.LNAME AS LNAME,
((PRICE * SALES) * TITLES.ROYALTY_RATE * AUTHOR_TITLES.ROYALTY_SHARE) AS ROYALTIES
FROM TITLES, AUTHOR_TITLES, AUTHORS
WHERE TITLES.TITLE_ID = AUTHOR_TITLES.TITLE_ID
AND AUTHORS.AU_ID = AUTHOR_TITLES.AU_ID
TIT (PRICE*SALES) FNAME LNAME ROYALTIES
--- ------------- --------------- --------------- ----------
T01 12446,34 Sarah Buchman 622,317
T02 190841,7 Sarah Buchman 11450,502
T03 1025396,65 Christian Kells 71777,7655
T04 168882,99 Hallie Hull 8106,38352
T04 168882,99 Klee Hull 5404,25568
T05 1400008 Klee Hull 126000,72
T06 225834 Wendy Heydemark 18066,72
T07 35929790 Wendy Heydemark 1976138,45
T07 35929790 Klee Hull 1976138,45
T08 40950 Kellsey 1638
T09 69750 Kellsey 3487,5
TIT (PRICE*SALES) FNAME LNAME ROYALTIES
--- ------------- --------------- --------------- ----------
T10 Wendy Heydemark
T11 752042,77 Hallie Hull 15792,8982
T11 752042,77 Klee Hull 15792,8982
T11 752042,77 Kellsey 21057,1976
T12 1299012,99 Wendy Heydemark 116911,169
T13 313905,33 Sarah Buchman 18834,3198
17 rows selected.
So how do I add up the royalties values associated with each author and find the max? for example I add klee hulls's royalties from each book and get 2,123,336.32(doing it by hand on calculator) what is the sql to find the max royalties for each author? P.S the answer should be KLEE HULL with 2,123,336.32
I am returning the ORA-01427 after running the query below. why I am returning the error and how to address it.
select b.value , b.name, p.value ......
(case when p.value <> 'G2' then null else (select c.oldvalue from ad_changelog c
where c.record_id = b.c_bpartner_id and c.ad_table_id = 291
and c.ad_column_id = 4216 ) end) as oldtradeName
from c_bpartner b, zz_receipt r, zz_recp_alloc a, m_product p, ad_user us
where a.c_bpartner_id = b.c_bpartner_id
and a.zz_receipt_id = r.zz_receipt_id
and us.ad_user_id = r.createdby
and p.m_product_id = a.m_product_id
Just trying to update a table in which the sales amount is inserted when the sales amount is null.
I have
UPDATE ph2_customer_temp
SET sales_amount = (
SELECT sl.sales_amount
FROM PH2_CUSTOMER_TEMP pct
join
sales_limit sl
on substr(pct.site_code,1,2) = sl.state
where pct.credit_limit is null )
restricting a subquery's results to the first record. If I use the following:
SELECT WU.DISCREP, M.PART, M.ETA
FROM DB.MICAP M
LEFT JOIN DB.WRITEUPS WU ON M.WRITEUPID=WU.WRITEUPID
LEFT JOIN DB.WUC WUC ON WU.WUCID=WUC.WUCID
WHERE (WU.AIRCRAFTID=205) AND (WU.CORRECTED=0)
ORDER BY WU.PACER DESC, WUC.WUCCODE
I get
-------------------------------------------------------
#5 GEN KICKED OFFLINECABLE02-MAR-08
#5 IDG HARNESS REQUIRES WIREWIRE24-MAY-08
#1 GEN WENT OFFLINECABLE02-MAR-08
AERIAL REFUEL DOORS W/N OPENLINEAR DEL29-SEP-07
AERIAL REFUEL DOORS W/N OPENCHECK VLV30-SEP-07
WEATHER RADAR TILT KNOB HASRADAR COMP05-OCT-07
But I need the highlighted line eliminated. I've tried DISTINCT subqueries in the WHERE M.WRITEUPID IN (SELECT DISTINCT... various experiments with joins, etc.
I can't seem to wrap my head around this problem I'm having with a query. I need to update all rows in my ps_ntsr_gf_stufile tables with the concatenated values from the ps_classes_tbl table where a.CLASS_NBR = b.CLASS_NBR. I tried to limit it to emplid from the ps_stdnt_enrl table but no luck.
UPDATE ps_ntsr_gf_stufile a
SET a.CLASS_NAME = (SELECT CONCAT('SUBJECT', 'CATALOG_NBR')
FROM PS_CLASS_TBL b
WHERE a.CLASS_NBR = b.CLASS_NBR
AND a.STRM = '1118'
AND a.INSTITUTION = 'NT752')
WHERE a.EMPLID IN (SELECT distinct EMPLID FROM PS_STDNT_ENRL);
I'm still getting the ORA-01427 error.
/* (SELECT A.TOTAL_ALLOCATED_AMT FROM CLS_ALLOCATION_HDR@LAMS_PROD A WHERE A.APPROVAL_DATE BETWEEN BCTH.TRAN_FROM_DATE AND BCTH.TRAN_TO_DATE
AND A.CUSTOMER_ID=BCTH.CUSTOMER_ID AND ALLOCATION_ID IN (SELECT ALLOCATION_ID FROM CLS_ALLOCATION_DTL@LAMS_PROD B WHERE ENTITY_TYPE='REC' AND
B.ALLOCATION_ID=A.ALLOCATION_ID AND ENTITY_ID IN (SELECT RECEIPT_NO FROM CLS_RECEIPT_DTL@LAMS_PROD C WHERE B.ENTITY_ID=C.RECEIPT_NO AND
RECEIPT_STATUS='A'))AND ALLOCATION_STATUS='A')*/
when i am trying to run this query i am getting an error'single row subquery returns more than one row'.
My problem : I Wrote a package(a function), this function just return a varchar2 , but in this function I wrote a cursor , when I call this function in this manner :
SELECT secu.u_menu_id,
secu.menu_id,
menu.prompt,
secu.u_type,
secu.u_id,
[code]....
SQL return : Ora-01427 Single-row subquery returns more than one row
Yes , in this function it has cursor that more than one row. But I let this function return varchar2 ,
I have written the below sql select loc,(select ename from emp where emp.deptno = dept.deptno) from dept
It results in the below error.
[Error] Execution (1: 13): ORA-01427: single-row subquery returns more than one row
I have modified the SQL and got the required output
select em.ename,dep.loc
from
(select loc,deptno from dept) dep ,
(select ename,deptno from emp) em
where dep.deptno=em.deptno(+)
I have written the below sql to fetch all loc for emp which got executed
SELECT ENAME, (SELECT LOC FROM DEPT WHERE DEPT.DEPTNO=EMP.DEPTNO) LOC
FROM EMP
But as i need all locations irrespective of any emp in the locaton so i tried to put emp side (+) which resulted in error.
I read in a book that you can't use subquery in an insert statement . E.g:
1)insert into dates (date_col) values (select sysdate fom dual) but when i tried using subquery like this:
2)insert into regions values ((select max(region_id)+1 from regions), 'Oce');
This query worked but 1st query didnt.From my assumptions if we try inserting values in table with the subqueries for a particular column as in 1st query , it will throw error but not while inserting values in all columns as in 2nd query.