I am trying to use the REGEXP_LIKE and I am getting ORA-00920: invalid relational operator.
I have run very simplified code to try to sort it out but no success:
SELECT id, address
FROM test_lob_tab
WHERE REGEXP_LIKE(address,'^A');
select 1 from dual where regexp_like('123', '^[0-9]$');
same result from both.
I am using Oracle 11 and freetoad 11.
The following runs no problem
SELECT
ACCO.SEQUENCE,
ACCO.DESCRIPTION,
MAX (ACCO.AUDIT_DTE)
FROM
PAS.AUDIT_CLINICAL_CARE_OPTIONS ACCO
WHERE
ACCO.AUDIT_DTE < :AuditDate AND
[code]....
However, when I try to add extra conditions to the status' in the sub select i get the error. This is one way I tried:
SELECT
ACCO.SEQUENCE,
ACCO.DESCRIPTION,
MAX (ACCO.AUDIT_DTE)
FROM
PAS.AUDIT_CLINICAL_CARE_OPTIONS ACCO
WHERE
[code]....
I've tried repeating the sub select for each of the extra status parts but everytime i hit the same problem.
Whey I try to run this query in TOAD I get an ORA-00920: invalid relational operator error. It's part of a 10g stored procedure. When I highlight it and run it it prompts me for the missing values and then the error pops up. The AND in line 4 is highlighted.
select CRIME_CLASSIFICATION_ID, crime_type, nvl(count(CRIME_CLASSIFICATION_ID),0) as CRIMECNTFrom vaps.vw_offenses where regexp_instr(valoc,to_char(location_id)) AND ( fromdate is null or offense_date between to_date(fromdate, 'mm/dd/yyyy') AND to_date(todate,'mm/dd/yyyy'))group by crime_classification_id, crime_type
I am using REGEXP_LIKE function, but it is giving me error like this.
SQL>select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
5 rows selected.
SQL>select regexp_like(testcol,'^ab[cd]ef$') from test;
select regexp_like(testcol,'^ab[cd]ef$') from test
*
ERROR at line 1:
ORA-00904: "REGEXP_LIKE": invalid identifier
But in Oracle documentation it is given under heading of 10g. Does Oracle 10g supports this function?
I am writing a REGEXP_LIKE function to validate the email address.. below function works for most of the valid email address, but not working for below condition... not sure how it can be tweaked to work for below condition as well...
Character . (dot, period, full stop) provided that it is not the first or last character, and provided also that it does not appear two or more times consecutively (e.g. John..Doe@example.com).
Select 'X' from dual
WHERE NOT REGEXP_LIKE('John.Doe@example.com','^(([a-zA-Z0-9_-])([a-zA-Z0-9_.''-]*)@([((25[0-5]|2[0-4][0-9]|1[0-9][0-9]|[1-9][0-9]|[0-9]).){3}|((([a-zA-Z0-9-]+).)+))([a-zA-Z]{2,}|(25[0-5]|2[0-4][0-9]|1[0-9][0-9]|[1-9][0-9]|[0-9])]))$')
I have below query which uses between clause. For some constraints I cannot use between clause so this need this to converted to use only relational operator.
select * from TABLE1
where "+ width +" between start_width " + "and end_width and "+ height +" between start_height " + "and end_height " + "and id = "+ id
i am working on a project that requires the creation of a object relational database. A number of different methods are required in order to insert different toy types into the appropriate toy related Object Table(s). Within this there will be the following fields:
Toy_name
Cost
Age_Range
rdy_allocation
and then some more specific information. The specific information for each toy is:
Toy_Type.
The toy_type can be electronic, game, or wooden.
For electronic I need to store Battery_Type and Number_of_Batteries.
For type Game, I need to store, Type, this could card_game, board_game or other_game.
For type wooden, I need to store Painted(Y/N).
The toys are requested by a child and created by elfs. This information also needs to be recorded but im more concerned with the creation of the toy class itself first. Now this was my first attempt at the solution but I cant seem to link the specific toy information into the same table. Will i have to create three separate tables then link them together?:
DROP TABLE TOY_OBJTAB;
DROP TYPE electronic_toy;
DROP TYPE wooden_toy;
DROP TYPE board_toy;
DROP TYPE toy_typ;
[code]....
I had my previous table model as below.
CREATE TABLE Rel_Module (
Id NUMBER (6) PRIMARY KEY,
Type CHAR (7) NOT NULL,
[Code]...
How I can easily load data into this new REL_MODULE table either using SQL or PL/SQL (e.g., Procedures, Functions, and Triggers). I don't need to write so many INSERT statement.
While executing the query i got an error saying "ORA-0092 - invalid sql query"
SELECT
S.user_je_source_nameSource,
B.nameBatch_Name,
B.default_effective_dateBatch_Eff_date,
B.posted_dateBatch_Posted_Date,
B.default_period_nameBatch_Period,
[code].......
I need to get create_user_id for different sale_location_id.Also create_user_id field will be having different values.This is part of my big query.I need to add this stmt in that.So taken that part and figuring it out.
create table it(sale_location_id number,create_user_id varchar2(10));
table IT created.
insert into it values(1,'ISRA')
1 rows inserted.
insert into it values(2,'USFA')
1 rows inserted.
select a.sale_location_id,decode(a.sale_location_id,1,a.create_user_id like 'IS%',a.create_user_id like 'U%') create_user_id from it a
given error as:
ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis"
How to write this.
I have a table in SQL , I am creating a column Of name in it , i want to restrict user to enter name in Capital only ,and i want to create this at table level . I tried Check Operator but failed .
create table my_tab
(U_name varchar2(30) ,
constraint ck_check (U_name = upper(m_name))
/
Any alternate to the following query.
select * from emp where ename like upper(NVL('%mi%',ename));
Basically I want to search based on string or null.
I have an query i.e.
I want 3 lines input in 1 line using 2 union operator like
Input:-
'i love playing
football and
volleyball'
i want the output like:-
"i love playing football and volleyball"
solve query using 2 union operator?
I have a two tables with same column name , I wanted to find different record in table1 when compared with table2
create table table1(col1 number,col2 number,col3 number,col4 number,col5 number);
create table table2(col1 number,col2 number,col3 number,col4 number,col5 number);
insert into table1 values(1,2,NULL,NULL,NULL);
insert into table2 values(1,2,NULL,NULL,NULL);
commit;
select col1 from (select col1,col2,col3,col4,col5 from table1 minus select col1,col2,col3,col4,col5 from table2);
no rows selected
how come i get no rows selected when col3,col4,col5 is having null values but NULL could be anything so
NULL-NULL cannot be equal to zero how is it possible
i want to know that how can i use like operator with if condition. i m using oracle10g form builder and it's for search purpose .
for example ...
if search string=string2 then
message('Record found');
end if ;
i want to use like '%search string%'.
1) Can we set a different symbol other than '' for escape operator.
2) If yes, how to see the current escape operator symbol.
3) How to find out the below name with escape operator?
Employee name ----> rama_krishna_raj
what the difference between IN and EXISTS operator. Why should we use EXISTS operator?
View 1 Replies View RelatedI have a issue on running the query with quote operator . When I am executing the SQL query I am getting error "Quoted String not properly Ended".
select q'[Oracle's world ]'
from dual
But The following query works.
select q'[It's Oracle's world ]'
from dual
I have a requirement in SQL that I have to number each row. Hence I thought of using ROWNUM. But the sql query I'm using uses UNION operator. Hence I used like this
select a,b,rownum as 'field1' from table1
union
select c,d,1 as 'field1' from table2
Will the above query solve my purpose?
Query -
SELECT *
FROM sysadm.ps_tmtl_post_vw a
WHERE a.month_prepared_for = 'JUNE,2012'
AND a.ca_status = 'P5 CUST GO AHEAD'
[code]...
When I try for the SQL-Tuning sets its throws error that
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- The optimizer could not merge the view at line ID 2 of the execution plan.
The optimizer cannot merge a view that contains a set operator.
I read earlier forum where it says that optimizer unable to interpret the conditions like order by etc etc.Now there is one view which is getting used in the query when I did select * from vw it took more than 16 hrs to complete. (bad view).
Attached File(s)
exec_plan.txt ( 2.06MB )
Number of downloads: 1
view_def.txt ( 14.12K )
Number of downloads: 2
i got the data like
select * from Table1
SNO Name B_MONTH
--------------------
101 A Mar
102 B Jan
103 C Feb
104 D Apr
105 f May
106 G Jun
Select * from Table2
107 H Dec
108 I Aug
109 J Oct
110 L Jul
111 M Sep
112 N Nov
select * from table1 union select * from table2 order by 3
The B_MONTH column is in Varchar2. Expected output should be
Output:
Jan
Feb
Mar
Apr
.
.
.
.
Nov
Dec
How to replace the like operator for increase the performance. Because it is taking more time and not using the index.
SELECT *
FROM emp
WHERE ename like '%AL';
Want to understand difference between Concat function and "||" operator. I am getting the same result for both. Below is the test case for your reference.
Select 'H '||' S' From Dual;
--Output H S
Select Concat('H ',' S') A From Dual;
--Output H S
Select Length('H '||' S') A From Dual;
--Output 6
Select Length(Concat('H ',' S')) A From Dual;
--Output 6
How we can compare IN operator between two text boxes in form
I have two text boxes on form
control.txt1 - 003
control.txt2 - 001,002,003
On button pressed
if :control.txt1 in (:CONTROL.TXT2) THEN
MESSAGE('1');
MESSAGE('1');
else
MESSAGE('0');
MESSAGE('0');
END IF;
All the time Message is 0 .
I have a package function which is wrapped and I cannot see the code.The package function raises an user-defined exception when :
SELECT ABC.*
FROM ABC
WHERE ABC.A = PACK.FUNC(ABC.B,ABC.C)
But it does not raise any exception and the query works absolutely fine generating desired results when :
SELECT ABC.*
FROM ABC
WHERE ABC.A = (SELECT PACK.FUNC(ABC.B,ABC.C) FROM DUAL)
I want to link to blocks using description as there is no relation , for example i have two tables with one field in common called description, and i want to link this field in two tables using like operator.
create table item ( item_code varchar2(12),item_name varchar2(30));
insert into item VALUES('A','HEA160');
insert into item VALUES('B','HEA180');
create table stk (sl_item varchar2(12),sl_desc varchar2(30),sl_qty number);
insert into stk VALUES ('X','HEA160X1000',12);
insert into stk VALUES ('X','HEA160X2000',4);
insert into stk VALUES ('Y','HEA180X3000',10);
Suppose i click on item block item_desc with value on HEA160 all the items similar to that should appear in stk block like 'HEA160X1000' ,'HEA160X2000' , if i click on 'HEA180' on item then 'HEA180X3000' it should come.
I am using the following query with like 'T_%', i am getting 80 rows out of which the first table_name doesn't even have a beginning part 'T_%'.
the first table name has not started with 'T_', why is it appearing.
*********************************************************************
SELECT 'Truncate table epic500.'||table_name
FROM user_tables where table_name like 'T_%' order by table_name;
*********************************************************************
output:
Truncate table epic500.TEMP_ENC_DEL
Truncate table epic500.T_ACCOMMODATION_CODE
I have a table called Student and a column as name now if i write a Query
select * from student where name < 'BRIAN D'
How does the comparison will be done.
I am using the below query to show the difference of data between two tables using minus operator.
teh result is correct, but is there a way can it show with a flag with new rows and updated rows something like "N" for new row and "U" for updated row.
select CURRENT_STG_GLACCTS.TABLE_NAME,
CURRENT_STG_GLACCTS.ACTIVE,
CURRENT_STG_GLACCTS.BUSINESSUNITID,
CURRENT_STG_GLACCTS.COST_CENTER,
CURRENT_STG_GLACCTS.GLACCT,
[code].......
I'm creating Tool Tip definitions for the Operators in the Filter on the Interactive Reports. I was looking for a definition for the 'Contains' operator and from what I've found this operator is used to do a text search and it returns a relevance score for every row selected.
I've also read that in order for that score to be determined that the column(s) need to be indexed with a CONTEXT index. Non of the columns in the tables are indexed with a CONTEXT index, however, when I put a value in the Expression box for a column I get a record returned.
If I run the same query in PL/SQL Developer like:
SELECT <column>
FROM <table>
WHERE contains(<column>,<search text>,1) > 0;
I get an error that the column is not indexed, so how does it work in APEX?