PL/SQL :: Using Regexp_instr In A Where Clause - Invalid Relational Operator
Oct 23, 2013
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 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'm not able to understand OCCURANCE parameter for REGEXP_INSTR.As per my understanding it should see the occurance of given pattern in source string.Eg.
'[o][[:alpha:]]{3}'Above pattern matches character 'o' followed by any 3 alphabetic characters: case insensitive. SELECT REGEXP_INSTR('500 Oracle Pkwy, pneumococcal Shores, CA herohonda lionab', '[o][[:alpha:]]{3}', 1, 1,0,'i') RESULT FROM DUAL ;
It is giving me 5 as result. Which is correct as search is started from first character of the source string and found first occurance "500 O".
SELECT REGEXP_INSTR('500 Oracle Pkwy, pneumococcal Shores, CA herohonda lionab', '[o][[:alpha:]]{3}', 1, 2,0,'i') RESULT FROM DUAL ;
It is giving me 23 as result. Which is correct as search is started from first character of the source string and found second occurance "500 Oracle Pkwy, pneumo"
SELECT REGEXP_INSTR('500 Oracle Pkwy, pneumococcal Shores, CA herohonda lionab', '[o][[:alpha:]]{3}', 1, 3,0,'i') RESULT FROM DUAL ;
It is giving me 33 as result although I'm expecting 25. Because the next occurance is "'500 Oracle Pkwy, pneumoco", and there are alteast 3 alphabets after it. understand "How OCCURANCE parameter works?".
I've got a task to star out account numbers in a free text field. The account numbers can be in a few different formats and there are a number of tables. The formats could increase or change as could the tables. For that purpose I want to use a couple of tables; one to store the formats and one to store the tablenames. The tablenames work fine, but I'm having problem with the formats.
INSERT INTO account_format VALUES (1, '''[0-9]{5}[A-Z]{1}-[0-9]{5}''', '''[0-9]{5}[A-Z]{1}-[0-9]{5}'',''****-****-****-****''', 'Y');
The idea is to bring back the format and use it in REGEXP_INSTR (or a similar REGEXP) to ascertain if the field contains the account number. The code I have been using looks a bit like this:-
CURSOR m_format_cur IS SELECT acc.acc_format FROM account_format acc; m_format_rec m_format_cur%ROWTYPE; IF REGEXP_INSTR (m_narrative, m_format_rec.acc_format) > 0 THEN .... END IF;
I have tried several different ways to get this to work e.g. hardcoding the format and declaring a variable with the format, which both work. Whenever I try to use the value retrieved from the database field it never works. Why should a variable from a cursor not work?
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]....
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.
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"
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 .
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?
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
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
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
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 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.
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?
if on an interactive report, you try and filter on a date column, then the "=" operator disappears.You get other (not so useful) operators like "in the next", "not in the next" ">", ">=" etc. - but no "=" to be found.
