SQL & PL/SQL :: Alternate To Like Operator

Mar 15, 2013

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.

View 11 Replies


ADVERTISEMENT

How To Get Alternate Records

Oct 17, 2006

how to retrive alternate records from the table in the answer table the first row wil be in upper case and the second row wil be in lower case ...like wise wat is the querry

View 1 Replies View Related

Non Unique Alternate Key Of A Table

Feb 11, 2011

Can a table conatin composite Alternate key which is not unique for each record in that table i.e combination of these columns have unique values which are repeated for one or more rows in that table.

View 8 Replies View Related

Oracle DB 10.2 - Export / Import Alternate Or Improvement

Jul 23, 2013

One of our client is using Oracle DB 10.2 with customize applications. They have 5 schemas (one for each module). In their Head Office they are utilizing 4 schemas and remaining one is being utilized on company site based in another city. Due to internet connectivity issue they don't have VPN available so they export schema dump file (imp/exp) on daily basis from that server, transfer it using FTP then import it on Production Server (in Head Office) on daily basis.

Now they are looking for an alternate because the schema size is getting larger day by day and due to internet connectivity issue they face lots of problems while transferring the file.

View 4 Replies View Related

Replication :: Alternate Solution For Materialized Views

May 17, 2009

I have a Problem regarding Data Replication, I am using Standard Version of Oracle 9i,I am having 5 remote servers , I am replicating the data from all of these servers using Materialized views on a Centralized server , I have created 5 schema for them, there is a application which is Consolidating the Data from all the locations.

When I create materialized views from these Materialized views I am facing problems to Fast refresh them as I am using "Union All" to combine data of all Users (Error is ora -12004) . I would like to know that is there any other way to do this, also I have tried all the ways to solve the problem of Ora-12004.

View 1 Replies View Related

Forms :: Alternate For OLE In Unix Application Server

Feb 19, 2011

Am tring to export a data from oracle forms to excel. I use ole2 option. it is working perfectly in my desktop. But when i move the same to our application server, it is not working. I belive OLE is related to Microsoft office and our server is unix based.

View 1 Replies View Related

Performance Tuning :: Possible To Display Alternate Execution Plans

Jan 27, 2012

When we use the AUTOTRACE / EXPLAIN PLAN we can see the (estimated) best execution plan the Optimizer found for our SQL Command. Is there a way to display all alternate execution plans the Optimizer has considered ?

View 1 Replies View Related

Performance Tuning :: Alternate Query Instead Of Querying Table Twice?

Nov 11, 2012

The Item data for individual cycles is as below.

Item_tbl
ItemRundate StddateStatus
P103-Nov-1203-Nov-12A
P104-Nov-1204-Nov-12D
P2 04-Nov-1203-Nov-12A

The requirement is I have to get the details of all data of previous Active cycle(status A) when the Item became disabled(status = D) for Input date.

In above case,since for Item P1 and on cycle date 04-Nov-12,status is D,I have to consider the previous active cycle which is 03-Nov-12. Based on above std date,the data is queried from another table to get all the Items. Item P2 should not be considered in above case.

Below is the code which I have written which considers the rundate as Input parameter.

-- To get the Items disabled for Input date
with Itemdisabled as
(
select item,stddate maxcycledate
from Item_tbl
where rundate = stddate

[code]....

In above case,I'm querying the Item_tbl twice once for getting the disabled Items and once for getting the Previous cycle which is active.

Is there any way to query above only once and get the required results using Lag/Lead functions etc.

View 5 Replies View Related

SQL & PL/SQL :: Double Quotes In Column Name Can Be Replaced With Alternate Character?

Feb 14, 2012

Any way to replace the Double Quotes used to enclose column names with an alternative character. This is the SQL I have now that Works!

select (case when CUST is null then "/BIC/Z_SUPPLNT" else NM1 end) CMPNTSUPSRCE
from TBL1, TBL2
where "/BIC/Z_MAJVEND"=CUST(+) and Material = '1ABCD456'
order by Material

But would like to do something along these lines below but keep getting error "ORA-00936: missing expression".

select (case when CUST is null then chr(34)||/BIC/Z_SUPPLNT||chr(34) else NM1 end) CMPNTSUPSRCE
from TBL1, TBL2
where chr(34)||/BIC/Z_MAJVEND||chr(34)=CUST(+) and Material = '1ABCD456'
order by Material

View -1 Replies View Related

Data Guard :: Configuring Primary For A Failover (alternate) Remote Destination?

Aug 2, 2012

I have a situation where I want to configure primary database (11.2) with 2 remote destinations. dest_2 is the default and points to a standby on host_2. However, I also want the primary db to continue transporting redo to dest_3 on node_3 when node_2 is taken down (planned or unplanned).

1) Configure the ALTERNATE attribute of dest_2 to point to dest_3.

2) Configure tnsnames client-side failover on primary host to point to 2 nodes (node_2 and node_3).

View 1 Replies View Related

SQL & PL/SQL :: Decode With Like Operator

Jul 12, 2013

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.

View 5 Replies View Related

SQL & PL/SQL :: Uses Of Check Operator

Sep 16, 2010

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))
/

View 6 Replies View Related

SQL & PL/SQL :: Using 2 Union Operator

Jun 28, 2010

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?

View 9 Replies View Related

SQL & PL/SQL :: Minus Operator And NULL

Feb 12, 2013

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

View 3 Replies View Related

Forms :: Use Like Operator With If Condition?

Jan 19, 2013

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%'.

View 1 Replies View Related

SQL & PL/SQL :: Escape Operator Symbol?

Feb 28, 2011

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

View 2 Replies View Related

SQL & PL/SQL :: Difference Between IN And EXISTS Operator

Jan 10, 2012

what the difference between IN and EXISTS operator. Why should we use EXISTS operator?

View 1 Replies View Related

SQL & PL/SQL :: Oracle 10g Quote Operator

Sep 7, 2011

I 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

View 4 Replies View Related

PL/SQL :: Rownum In UNION Operator

Feb 18, 2013

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?

View 11 Replies View Related

Optimizer Cannot Merge A View That Contains Set Operator

Jul 18, 2012

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

View 5 Replies View Related

Error ORA 920 / Invalid Relational Operator

Jul 30, 2010

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.

View 1 Replies View Related

SQL & PL/SQL :: Using Union Operator And Sort By Month?

Jan 10, 2012

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

View 8 Replies View Related

SQL & PL/SQL :: Replace The Like Operator To Increase The Performance

Jul 16, 2012

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';

View 30 Replies View Related

SQL & PL/SQL :: Difference Between Concat Function And (||) Operator?

May 6, 2013

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

View 5 Replies View Related

Forms :: How To Compare Two Text Boxes With IN Operator

Jun 12, 2012

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 .

View 4 Replies View Related

SQL & PL/SQL :: Select From Dual Versus Equals Operator?

Mar 14, 2011

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)

View 6 Replies View Related

Forms :: Details On Mouse Click Using Like Operator?

Jun 9, 2013

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.

View 1 Replies View Related

SQL & PL/SQL :: Select Query Like Operator Getting Wrong Data

Dec 25, 2012

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

View 4 Replies View Related

PL/SQL :: REGEXP_LIKE ORA-00920 Invalid Relational Operator

Apr 18, 2013

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.

View 5 Replies View Related

SQL & PL/SQL :: How Does Operator Works For Varchar2 Datatype Columns

Nov 22, 2011

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.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved