PL/SQL :: WITH AS Results In ORA-00928 - Missing SELECT Keyword?
Aug 19, 2013
We are running on Oracle 10g. The following script results in ORA-00928: missing SELECT keyword. what causes this error? Both 'select' statements when run by themselves, complete successfully.
[code]WITH A1 AS WITH A1 AS ( SELECT MIN (VAPS_RPT_INTV_DMSN.INTV_DT), VAPS_RPT_INTV_DMSN.RPT_ID, VAPS_RPT_INTV_DMSN.RPT_INTV_ID FROM APS.VAPS_RPT_INTV_DMSN, APS.VAPS_RPT_CL_INTV_DMSN WHERE APS.VAPS_RPT_INTV_DMSN.RPT_INTV_ID =
[code]./..
View 4 Replies
ADVERTISEMENT
May 3, 2013
I am importing data from excel to database table with column mapping.I have created package with a procedure. I am calling this procedure from oracle forms 6i. while executing the package procedure,I am getting following error ora-00928 missing select keyword ora-06512 at" abcd.pk_ excel_ to_ db" line 26 i.e befor forms_ddl.there is no problem in code and my form gets compiled but at run time i am getting this error.I also want to check, array is populating with anything or not.
PACKAGE PK_EXCEL_TO_DB IS
TYPE tKeyValue IS RECORD (
CROUTE VARCHAR2(255),
VROUTE VARCHAR2(1000),
CTRNDATE VARCHAR2(255),
VTRNDATE VARCHAR2(1000),
CTTIME VARCHAR2(255),
VTTIME VARCHAR2(1000),
CTID VARCHAR2(255),
VTID VARCHAR2(1000));
TYPE tDataList IS TABLE OF tKeyValue
index by binary_integer;
[code].....
View 4 Replies
View Related
Jan 11, 2012
I issued the command
grant select(employee_id, job_id) on employees to scott;
But it returns error as
HR:orcl > grant select(employee_id, job_id) on employees to classuser;
select(employee_id, job_id) on employees to classuser
*
ERROR at line 1:
ORA-00969: missing ON keyword
View 5 Replies
View Related
Apr 9, 2008
Oracle and I am receiving ora-00905 missing keyword error.what could possibly be wrong with this SQL statement?
Select
T1.*,
T2.*
From
Table1 T1 INNER JOIN
Table2 T2 On
[code]....
I am trying to select the maximum dates from 2 different tables and I am using Oracle 10.
View 8 Replies
View Related
Jun 1, 2010
Check out following query
SELECT LOCALTIMESTAMP,SYSTIMESTAMP,EXTRACT(hour FROM LOCALTIMESTAMP) +2,
CASE WHEN EXTRACT(HOUR FROM LOCALTIMESTAMP) +2 Between 9 and 17 OR
(EXTRACT(HOUR FROM LOCALTIMESTAMP)+2 = '5' AND EXTRACT(MINUTE FROM LOCALTIMESTAMP)+2 > '60')
THEN TO_CHAR(FROM_TZ(LOCALTIMESTAMP,'GMT') AT TIME ZONE '+05:30','DD-MON-YYYY HH24:MI:SS')
CASE WHEN EXTRACT(HOUR FROM LOCALTIMESTAMP)+2 < '9'
THEN TO_CHAR(FROM_TZ(SYSTIMESTAMP,'GMT')+2 AT TIME ZONE '+05:30','DD-MON-YYYY HH24:MI:SS')
END
FROM dual;
ORA-00905: missing keyword
View 8 Replies
View Related
Nov 14, 2013
im trying to insert these results to my fact table (fact_apartments) with the following:
INSERT INTO fact_apartments(avg_price, segmentid, projectid)
SELECT (avg(price), segmentid, projectid)
FROM projectdetails
WHERE projectid = '1';
but it return with the missing right parenthesis.
View 11 Replies
View Related
Feb 26, 2008
I need to select a count of records where a field (call it widget) is the same, so i need all records where widgets are distinct. So it would be like asking for a distinct in a where clause. Not having much SQL experience this is a difficulty for me.
lets see .. so a count of records where widget = widget or something along those lines.
View 9 Replies
View Related
May 10, 2012
I have a SQL statement that returns a set of columns...but...when I create table as <SQL statement> I get the same columns but with 2 of the columns containing each others data, e.g:
SQL Select:
COL1 COL2 COL3 COL4
___________________________________
AND10200000017805CG-4CG-3
Create Table as <SQL Select>:
COL1 COL2 COL3 COL4
___________________________________
AND10200000017805CG-3CG-4
The SQL Select is correct and the Create Table As <SQL Select> is wrong.
Here is my SQL:
Create table ALTERNATENUMBERS as
SELECT ctry,
id,
MAX(DECODE(tp,'EN', RN)) EN,
MAX(DECODE(tp,'RN', RN)) RN,
MAX(DECODE(tp,'AN', RN)) AN
[code]....
Unfortunately I cannot give you any data (too much of it) and small scale testing works, it's only when I run it on the 11million records do I get some (not all), just some of the data being mixed up between columns.
Now, I've tried:
1. Using SQLPLus - no joy
2. Creating the Table and then inserting the data into a blank table - also no joy
3. Using a VIEW - no joy, listagg doesn't work in VIEW tables
I do understand that without data it's hard to replicate the issue but why this statement works as a SELECT but when written to a table has data anomolies?
View 4 Replies
View Related
Jan 10, 2012
we have a strange symptom in a database Oracle 11.2.0.2 EE. Following question comes from our application developers.
The following SQL statement:
SELECT
v.reporting_month,
sum(v.f_s) "REV_S",
sum(v.f_f) "REV_F",
sum(v.f_c) "REV_C",
[code].......
gives different result when we exchange the index ksr_valid_until_i on table kreditkarten_sets_rs. For some reasons we changed the index from bitmap to normal and are getting different results. Switching back gives us the same results as before. When we avoid the usage of this index in the statement than we are getting the same results as when we are using the normal index.
View 4 Replies
View Related
Feb 25, 2011
SQL> desc res;
Name Null? Type
----------------------------------------- -------- ----------------------------
RESULT PUBLIC.XMLTYPE
SQL> select * from res;
RESULT
--------------------------------------------------------------------------------
<fine No="2"><stdNo>2</stdNo><value>300</value><reason>breaks keyboard</reason><
date>2011-10-03</date></fine>
how can me write results of select statement into xml file instead of show them on screen?
View 20 Replies
View Related
Jun 15, 2010
I'm working on a Oracle Database, and I'm gettin incorrect results when including a date field in the select list which is NULL in the table.
This works correctly and returns exactly one row:
SELECT firstField FROM table WHERE firstField = 'value'
while this doesn't and returns no rows:
SELECT firstField, secondField FROM table WHERE firstField = 'value'
Where secondField is of type date and its value is NULL (00-000-00). Note that the only thing that changes is the select list.
View 6 Replies
View Related
Aug 15, 2011
I need to generate a select query in runtime and store the results of it into a file.Each time the column name and table name in the query will differ.Now im able to generate the select query through for cursor but problem is to store the results to the file.I tried using plsql table,im able to get the values to that table and store the results to a file,but the results of the query is more then 10000 lines (it might increase also)where only 4000 characters where able to store in the plsql table.so rest of them are not stored in the file.
View 3 Replies
View Related
Sep 17, 2010
Table A
Id Country city
1 US
2 US Boston
3 Boston
4 US Newyork
5 London
6 Japan Tokyo
Im looking for a query which returns results based on both city and country passed.
If i pass country US and city Boston it should return row2 with US and Boston row
If i pass country null and city Boston it should return row3
If i pass country UK and city Boston it should return row3
If i pass country UK and city London it should return row5
i.e. If country/city combination exists in DB return that row Else city row should be returned.
View 5 Replies
View Related
Jul 12, 2012
SELECT CREWACTIONFACTID
,CrewKey
,EventID
[Code].....
View 3 Replies
View Related
Dec 17, 2012
i am trying to export table using datapump in oracle 10g, this expdp takes 5 hours time, so i want use use parallel keyword in expdp,
my question is how should i know number of parallels can i use...?
View 3 Replies
View Related
Oct 18, 2012
Can we use a subquery after the update keyword??
For example
UPDATE ( Select col1 from test)
set col1='x';
In short can we replace the table name after an update and use a select statement instead??
View 3 Replies
View Related
Nov 11, 2010
I am having some difficulties with this trigger. It keeps giving me the error "ERROR at line 5: PL/SQL: ORA-00923: FROM keyword not found where expected" when I am not even using a SELECT before the line it says the error is on? Here is the trigger that I am attempting to create.
CREATE OR REPLACE TRIGGER ClassRestraint
BEFORE INSERT ON Enrolled
FOR EACH ROW
DECLARE
numCourses NUMBER :=0;
myException EXCEPTION;
BEGIN
[code]...
I am getting the error on line 5.
View 5 Replies
View Related
Mar 8, 2012
can we use distinct keyword with the count and sum analytical functions?
View 5 Replies
View Related
Jun 23, 2011
I have this script which should find tablespaces and their size, joined with free bytes. Trying to run this gives me the SQL Error: ORA-00923: FROM keyword not found where expected.
I have two questions:
1. Where should the FROM be?
2. Is there something wrong with the join.
==============================================
set linesize 120
col "TOTAL (KB)" format 99999999999999999
col "FREE (KB)" format 9999999999999999
col TSNAME format a35
col "% FREE" format a10;
SELECT a.tablespace_name TSNAME, sum(a.bytes/1024) "TOTAL (KB)",
Sum(b.bytes/1024) "FREE (KB)"
To_char(round((sum(a.bytes/1024)/sum(a.bytes/1024))*100),2), 'FM99990D999999')
|| ' % ' "% FREE"
FROM dba_data_files a, dba_free_space b
Where a.tablespace_name = b.tablespacename
Group by a. tablespace_name
[/i]
=============================================
I used the script from [URL]
It worked great but I'm not sure how to use the arithmetic functions to show me MB instead of bytes.
View 1 Replies
View Related
Jun 29, 2007
The following syntax gives me the error:
ORA-00923: FROM key word not found where expected
if this is so, why use 'from' in TRIM function? Or is my syntax incorrect?
SYNTAX: select trim leading ('0' from (substr(to_char(polref_nbr),9,10))) "TRIM example" from tbl_vg_adhoc;
View 2 Replies
View Related
Feb 12, 2011
I have questions on Top N.Before post this thread, I have read these: One select in TOP Na thread very similar with my problem
Search the documentation in Oracle with key word "Top-N", but what I mostly got is about: "Top-N Frequency Categorical Binning"
SQL> select ename, sal
2 from emp
3 where sal in
4 (
5 select distinct sal
[code]...
Actually, I am not missing parenthesis, and I can not see any syntax error.what does the issue occur?
View 6 Replies
View Related
Aug 29, 2012
I am trying to compile this block for updating a record. In the P_ADD_LOV_SQL column, I have to update the following select statment, but when ever I am compiling it it shows error in the Select statement as : ORA-00923: FROM keyword not found where expected. rearrange the select statement so that it doesn't show the error.The coding is :
DECLARE
P_ADD_KEY NUMBER;
P_ADD_CODE VARCHAR2(50);
P_ADD_DESCRIPTION VARCHAR2(75);
P_ADD_MODULE_KEY NUMBER;
P_ADD_PROMPT VARCHAR2(50);
P_ADD_REQUIRED VARCHAR2(1);
P_ADD_FORMAT VARCHAR2(1);
P_ADD_SIZE NUMBER;
[code]....
The column is updated properly. But I can't update by compiling the block.
View 11 Replies
View Related
May 17, 2013
I want to get the top 10% of salaries in employees table. But I got error:
SQL> select top 10 percent salary
2 from employees
3 order by salary desc;
ORA-00923: FROM keyword not found where expected How can I get the top 10% percent?
View 6 Replies
View Related
Dec 19, 2012
I just so happen to be the one trail blazing the pivot function for the section of the company I work in. (Needless to say, a Sesame Street style answer will not be offensive.) We are literally in the process of upgrading to 11g (11.2.0.1.0). Sadly, none of our more experienced programmers now anything about the pivot function. Not really surprising to me since we've been working in 10g. Anyway, I am using SQL Developer version 3.0.04 which I know is not the newest but I don't yet have permission to upgrade. I used [URL] to get me as far as I am on this function.
The script I am having problems with is:
SELECT *
FROM
(SELECT
[Code]....
The error I'm getting is:
ORA-01738: missing IN keyword
01738. 00000 - "missing IN keyword"
*Cause:
*Action:
Error at Line: 16 Column: 2
The error indication bounces between line 15 and 16. If I put IN at the end of 15 I then have a missing right parenthesis error...
View 6 Replies
View Related
Dec 5, 2010
This code run fine in Toad BUT giving me error in forms.
SELECT
LEVEL LOC_NAME,
LOCATION_NAME LOC_ID,
LOCATION_ID HLOC_ID,
HEAD_LOC_ID MUDRA
FROM AS_LOCATION_HDR
CONNECT BY PRIOR LOCATION_ID = HEAD_LOC_ID
START WITH HEAD_LOC_ID is null
ORDER SIBLINGS BY LOC_ID
The error is: "Encountered sysbol "SIBLINGS" when expecting one of the following:
by
The symbol "by inserted before the "SIBLINGS" to continue.
Is the SIBLINGS keyword not acepted in forms? What would be the alternative?
View 2 Replies
View Related
Jun 21, 2013
We have an Oracle UCM and Oracle SES implementation.Currently there are too many tags returned for certain keywords by SES which breaks the UI,some of the tags being irrelevant too.Is there any configuration variable in SES by which these tags can be limited to say 20 most relevant tags.?
View 0 Replies
View Related
Aug 2, 2012
I have one query regarding how to retrieve the CLOB data.
The requirement is something that in the select statement there are around 20+ columns which i need to retrieve from around 5 tables after joining.
Since the result set after joining also will get duplicate values i need to use distinct keyword to filter the resultset. But in the 20+ columns there are 2 CLOB data columns which i need to retrieve.
Whenever i use DISTINCT i'm getting ORA-00932: inconsistent datatypes: expected - got CLOB error. I know that DISTINCT keyword cannot be used for CLOB datatypes.
View 15 Replies
View Related
May 30, 2013
i have a table that contains employee id, employee name , so if i gave the correct employee id in where clause of select statement it will show employee name, in case if i give the employee id that does not exist in the table it will show 'Employee name is not found'..
View 2 Replies
View Related
Jul 4, 2012
What is difference when you issue a create controlfile with "standby" keyword on the primary database
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/path/db_stby.ctl';
vs. creating a control using
ALTER DATABASE BACKUP CONTROLFILE to '/path/db_control.ctl';
what does ORACLE put into the standby control that is "extra"? ie. what is the difference between a standby control file and a normal control file?
View 7 Replies
View Related
Apr 4, 2007
i have a query where i am using the max function to find the most recent record. What i want to do is use that query as part of an insert statement into a different table, however, i don't want to insert the column that i used the max function on. Is there anyway to use the max function without having the column it is being used on showing in the results?
View 1 Replies
View Related
