SQL & PL/SQL :: Not In Condition In IF Statement?

Sep 19, 2011

I need to check somewhere in the code as " Check the variable with in 20 varaiables , if not excute somes statements"

Example

a1=20,a2=30,a3=40......a20=200; (Like this way i have 20 varaibles with values)
v1=150;
if( v1<>a1 AND V1<> a2 AND V1 <> a3...... AND V1<>20)
execute some insert block;
End if;

But it's writting very diffcult as i need to compare with 20 varaiables.Was there any alternative sloution like taking some VARRAY and checking in IF condition like v1 NOT IN(Va) (Where 'Va' is like VARRAY)

View 13 Replies


ADVERTISEMENT

PL/SQL :: Possible To Add Condition In Statement Level Trigger

Jul 17, 2012

is it possible to add condition in statement level trigger

CREATE OR REPLACE TRIGGER insert_trigger after
INSERT ON Table when condition--- or any condition like.

how we use in row level trigger

View 2 Replies View Related

Variable Usage In Type Of Table Declaration Statement And Execute Immediate Statement

Aug 10, 2011

HOW to use variable P_TMPLID in following statement

TYPE typ_unrecon IS TABLE OF REC_' || P_TMPLID ||'_UNRECON%ROWTYPE index by binary_integer;

because its throwing error while compiling

and also in statement
FORALL i IN unrecondata.FIRST .. unrecondata.LAST SAVE
EXCEPTIONS
--STRSQL := '';
--STRSQL := ' INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES ' || unrecondata(i);
-- EXECUTE IMMEDIATE STRSQL;
INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES unrecondata(i);---throwing error on this statement
commit;
--dbms_output.put_line(unrecondata(2).TRANSID);
EXCEPTION

View 2 Replies View Related

SQL & PL/SQL :: Select Statement From Schemas In MERGE Statement In USING Clause

Sep 13, 2013

In the following merge statement in the USINg clause...I am using a select stament of one schema WEDB.But that same select statement should take data from 30 schemeas and then check the condition below condition

ON(source.DNO = target.DNO
AND source.BNO=target.BNO);

I thought that using UNIONALL for select statement of the schemas as below.

SELECT
DNO,
BNO,
c2,
c3,
c4,
c5,
c6,
c7
[code]....

View 5 Replies View Related

SQL & PL/SQL :: Select Statement Is Blocking A Delete Statement

Jan 11, 2012

I am using JDBC to run a few queries from my Java program (multi-threaded one).I am facing an issue where a select statement is blocking a delete statement. From the java code point of view, there are 2 different threads accessing the same tables (whith different DB connection objects).

When the block occurs (which i was able to find out from the java thread dump that there is a lock on oracle), the below is the output:

SQL> SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
2 || ' User '||s1.username || '@' || s1.machine
3 || ' ( SID= ' || s1.sid || ' ) with the statement: ' || sqlt2.sql_text
||' is blocking the SQL statement on '|| s2.username || '@'
4 5 || s2.machine || ' ( SID=' || s2.sid || ' ) blocked SQL -> '
6 ||sqlt1.sql_text AS blocking_status FROM v$lock l1, v$session s1, v$lock l2 ,
7 v$session s2,v$sql sqlt1, v$sql sqlt2
8 WHERE s1.sid =l1.sid
9 AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id
AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1
10 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2;
[code]...

From the above it can be seen that a select statement is blocking a delete. Unless the select is select for Update, it should not block other statements is not it ?

View 10 Replies View Related

SQL & PL/SQL :: Insert Into Statement Doesn't Insert All Rows Return By Select Statement?

Jan 12, 2011

If i inserted the values in table it gets inserting very few rows only.I dont know y it is?

View 15 Replies View Related

SQL & PL/SQL :: Query With Where Condition

Jun 12, 2013

I have EMPLOYEE table that have 3 records with EMP_ID 1, 2, 3. Now I want to run below query

select emp_id from employee where emp_id in (1, 2, 3, 4, 5);

It will return only 3 records but i want those records also which is not available in employee table. Is this possible without using another table or creating another table. Actually I don't have enough privileges to create table.

& want output like below

EMP_ID
1
2
3
4 Not Found
5 Not Found

Here emp_id 4, 5 is not available in employee table, but query should return those value also with comments like "Not Found"

View 6 Replies View Related

SQL & PL/SQL :: Union Condition?

Aug 23, 2012

I am facing an issue in union clause

Below is my query Issue faced is that based on union I want to limit the result and does not want second query to give duplicate result as both queries deal with same table

SELECT TAB1.ID, TAB1.CRNCY_CODE, TAB2.SCHEME_CODE, DECODE( 'INFENG',CAST( '' AS VARCHAR(20)),
NVL(TAB2.ALT1_SCHEME_DESC,TAB2.SCHEME_DESC),TAB2.SCHEME_DESC),
TAB1.SUB_HEAD_CODE, DECODE( 'INFENG' ,CAST( '' AS VARCHAR(20)),NVL(TAB1.ALT1_SUB_HEAD_DESC ,
TAB1.SUB_HEAD_DESC),TAB1.SUB_HEAD_DESC)

[code]...

select null ID,null crncy_code,TAB2.SCHEME_CODE ,
NVL(TAB2.ALT1_SCHEME_DESC,TAB2.SCHEME_DESC),TAB2.SCHEME_DESC,null SUB_HEAD_CODE from TAB2 where TAB2.SCHEME_TYPE ='SCHEME1';

[CODE]

View 4 Replies View Related

SQL & PL/SQL :: Case Condition?

Nov 21, 2010

i have small requirement

case when type=1 then max(col2) else end
case when type=2 then max(col2) else end

i wanted to have difference 2 timestamp

type(datatype- numeric)
col2(datatype-timestamp)

all it should be in one select condition

View 17 Replies View Related

SQL & PL/SQL :: Same Result When Use NOT IN And IN Condition?

Dec 14, 2011

I am using Oracle 8i.

Below is my query...

In the below 2 tables the records are like...

Table_1

IDNameCode
1AII
1AMNET
1AAXIS
1AUAT
2BMNET
2BUAT
2BUTC
3CMNET
3CII
3CUTC

Table_2

IDName
1AAA
2BBB
3CCC
4DDD

When i execute below query with using (NOT IN & IN) i am getting same result..

select * from Table_2 where id in
(select * from Table_1
where SRVC_PROV_CDE not in ('MNET', 'II', 'AXIS', 'UAT'))

Result:

IDName
1A
2B
select * from Table_2 where id in
(select * from Table_1
where SRVC_PROV_CDE in ('MNET', 'II', 'AXIS', 'UAT'))

Result:

IDName
1A
2B

When we execute the 1 query above it should not show 1 and 2 records...as i am using NOT IN condition.

View 5 Replies View Related

SQL & PL/SQL :: Variable Use In IN Condition?

Dec 5, 2010

I had a procedure in which there will be multiple update and select statements Eg: Update table T1 set Column1='X' where Column2 in ('A','B','C') All the update/select queries will have same set of values in the in condition ('A','B','C') will remain same. But the tables and columns will vary.So, I would like to declare a variable/array which holds the values in the in condition

var1 := {'A','B','C'}

and use in my statement like :-

Update table T1 set Column1='X' where Column2 in var1. Is there any way to acheive this?

View 7 Replies View Related

PL/SQL :: REGEXP-LIKE Condition?

Jul 26, 2012

I would like to use REGEXP_LIKE condition expression with '[:alnum:]' operator in Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 for to check the string is alphanumeric or not.Why do i need: I have xinput field [RAW(24)] which not sure to contains always an alphanumeric string. We should use only when xinput field is an alphanumeric string.

x varchar2(50);
x:=rtrim(ltrim(utl_raw.cast_to_varchar2(xinput)));

how should i use REGEXP_LIKE

select 'TRUE' from dual where REGEXP_LIKE( 'True', '[:alnum:]')
select 'TRUE' from dual where REGEXP_LIKE( 'False%*^tt123.***', '[:alnum:]')

View 4 Replies View Related

PL/SQL :: Unquote A Condition

Feb 9, 2013

I'm passing some parameters from java code to a query as follows:

select * from table1
where col1 = 'yes'
--want to make this part dynamic
*$[variable]*

where the $[variable] = 'and col2 like ''some%'' and col3 = ''good'''

So this query is giving me an error saying that it cannot construct such query because i guess what it's trying to run is:

select * from table1
where col1 = 'yes'
*'and col2 like ''some%'' and col3 = ''good'''*

I need to remove the first and last single quote... I tried using double quote for my string and replace all single quotes with empty but the "LIKE" function only works with single quotes (like 'some%')..

I also tried using:

select * from table1
where col1 = 'yes'
and trim (both '''' from 'col2 like ''some%'' and col3 = ''good''')

but i get a "ORA-00920: invalid relational operator"...

View 5 Replies View Related

PL/SQL :: How Where Condition Works

Jun 1, 2013

How oracle retrieves data from a table.like let say a table product as

create table product(id number,name varchar2(10))

table product dont have any index on it

now if this table conatain 500 rows and we have two query
1. select * from product where id=26
2. select * from product

does both query take same time or first query will execute in less time since it has where condition

View 2 Replies View Related

PL/SQL :: Pass Value To If Condition?

Oct 31, 2012

I want to run multiple sql select command after loggin to sqlplus and need to pass the value to variable to check the condition.

When I am running the below script I am getting the below error
----------------------
SP2-0734: unknown command beginning "return :va..." - rest of line ignored.
SP2-0734: unknown command beginning "return :va..." - rest of line ignored.

a2.sh: test: argument expected
------------------------

Example:
sqlplus -s system/SYSTEM_PASS << EOF
set pages 0
set head off

[Code].....

View 5 Replies View Related

Tuning With Order By Condition

Sep 9, 2010

I have a query with order by clause, which takes 30 sec to execute with order by clause. And if i remove the order by clause it executes within 1 sec.

The column in the order by condition has index. but when i see Explain plan output. it doesn't show this index is being used. I tried to execute query with INDEX hint but still explain plan is not showing this index.

View 4 Replies View Related

Multiple Condition Query

Jun 3, 2011

I have a result of Query like this:

Col1 Col2 Col3
T1 15 20
T1 18 19
T1 5 20
T2 15 20
T2 18 19

And I just need to display only the T's that would meet the condition Col2 in (15,18) and Col3 in (20,19).

I try with a condition where col2 in (15,18) and col3 in (20,19) and that works, but I don't need to display T1, because T1 has a Col2=5 and Col3=20, I just have to display T2 that just satisfies all my conditions, Col2 in (15,18) and Col3 in (20,19). It's something like a vertical search.

View 2 Replies View Related

SQL & PL/SQL :: If Condition In Insert Stmt

Aug 12, 2010

I have an INSERT stmt LIKE below.

INSERT INTO emp
VALUES (v_empid, v_ename, v_salary);

where v_empid, v_ename AND v_salary are variables.v_salary IS nullable
Now IF v_salary IS null, oracle IS IS showing an error LIKE below:

ERROR at line 54:
ORA-06550: line 54, column 172:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 52, column 5:
PL/SQL: SQL Statement ignored

Can I use an if stmt inside the insert stmt. If v_salary is null I want null there else the value in v_salary?

View 6 Replies View Related

SQL & PL/SQL :: Best Way To Select Out Of Group Condition

Nov 21, 2011

I am asking about the best way in SQL for the following case:

CREATE TABLE TRY_A (A NUMBER, B NUMBER);

INSERT ALL
INTO TRY_A VALUES (1,0)
INTO TRY_A VALUES (1,1)
INTO TRY_A VALUES (1,2)
INTO TRY_A VALUES (2,0)
[code].......

I need to learn the best way to select all A values where B is never 0. So if the needed result for the above example is:

A
--------------
3
5

As column B was never 0 with column A = 3 or 5.

View 11 Replies View Related

SQL & PL/SQL :: Dummy Row For Non Matched Condition

May 25, 2010

Is it possible to have a dummy row when the record inside the IN condition doesnt matches?

E.g. :

select * from table1 where record in ('A','B','C');

Here record 'A' is not in table.

Expected output:
record name
-------------------
Adummy row
BRajiv
cRobin

View 6 Replies View Related

SQL & PL/SQL :: Stored Function In Where Condition

Sep 29, 2010

I use stored function in where condition but it gives wrong output but output of stored function is use in where condition it gives correct output. i don't know how to use it.

Scripts:

select get_emp_mgr_no('MHR,ERT,TYU') FROM DUAL;

OUTPUT:

'MHR','ERT','TYU'

SELECT count(1)
FROM BANK_details
where notre in ('MHR','ERT','TYU');

count:

12345

select count(1)
from bank_details
where notre in '('||get_emp_mgr_no('MHR,ERT,TYU')||')';

count

0

It shows wrong output. how to use stored function in where condition?

View 3 Replies View Related

SQL & PL/SQL :: How To Use If Exist Condition In Oracle

Oct 11, 2012

i am using one if exist condition in sql server that is like this.

if exists(select 1 from ShiftEmployee where Empid=@EmpID and Month=@month and year=@year)
begin
some value is passing here...
end

how to use the same in oracle?

View 4 Replies View Related

SQL & PL/SQL :: Select Clause With Condition

Jul 22, 2010

Based on same salary i need to retrieve data like below whose hire date should be within a week (7days). The table may have 200000 records.

empno Hiredate salary
1234 03-JUN-10 3000
1354 21-MAY-10 3000
1834 01-JUL-10 3000
1954 08-JUL-10 3000

View 9 Replies View Related

SQL & PL/SQL :: Select Rows With Max Condition

Aug 13, 2010

I have been trying to find out how to write the following query:

Suppose the following table:

ID seq
24 1
24 2
24 3
67 1
67 2
67 3
67 4
67 5
13 1
13 2

I would like to retrieve the rows for every different ID with its max value. For one ID that would be:

select * from TABLE where seq in(
select max(seq) from TABLE where id=24)

How can i do this for all the rows??

View 2 Replies View Related

SQL & PL/SQL :: Join Condition - One Column Vs Many?

Apr 8, 2012

create table ptab(pid number);
create table ctab(aphone varchar2(20));
drop table ctable
create table xtab(pid number,phone varchar2(20), tel1 varchar2(20), tel2 varchar2(20), tel3 varchar2(20))

insert into ptab values(1);
insert into ptab values(2);
insert into ptab values(3);
insert into ptab values(4);

[code]..

i have 3 tables, xtab, ctab and ptab

join condition

ptab.pid = xtab.pid
and
--------------------------------------------------------------------------------

what I want the join between xtab and ctab is, aphone should match with phone, and then tel1, then tel2, and then tel3, (if phone, tel1, tel2, tel3 are not null in that order only), if aphone matches with any of these,then just print that particular pid, important point is, aphone should be checked against phone, tel1, tel2, tel3 that order only

so the results should simply print

pid
1
2
3
4
5 should not be printed here because for pid 5 in xtab, none of the phone numbers match with aphone of ctab

I tried this:
select
DECODE (ctab.aphone,
xtab.phone, 1,
xtab.tel1, 1,
xtab.tel2, 1,

[code]...

but i cannot join ctab and ptab, i dont want to use intersect etc, because we are looking at millions of rows here

View 3 Replies View Related

SQL & PL/SQL :: Outer Join With Cut-off Condition

Mar 10, 2010

Suppose I have two tables

Transaction
TXN_ID:integer
TXN_DATE:date

Return_Transaction
RET_TXN_ID:integer
TXN_ID:integer
RET_TXN_DATE:date
[code]....

Transaction may have returned transactions. We use outer join to join the tables using TXN_ID. We have a report that shows the following data...The report use the following basic query to check all transactions with returned transactions information...Quote:

SELECT t.txn_id,
t.txn_date,
rt.ret_txn_id
FROM TRANSACTION t,
return_transaction rt
WHERE t.txn_id = rt.txn_id (+)
Result
TXN_ID, TXN_DATE, RET_TXN_ID
100, 2010/03/10, 500
102, 2010/03/11, 501

If user want to check all transactions with no returned transactions, a where clause is appended to the query
Quote:

SELECT t.txn_id,
t.txn_date
FROM TRANSACTION t,
return_transaction rt
WHERE t.txn_id = rt.txn_id (+)
AND Nvl((SELECT 1
FROM return_transaction rt
WHERE t.txn_id = rt.txn_id),0) = 0
[code].....

View 4 Replies View Related

SQL & PL/SQL :: Writing A Code For When Condition

Feb 5, 2013

I have a problem in running a sql query.I have a dataset with the following details. Product name,product status,approval date in table product_details. I have a code as follows

select Product_name,
Product_status,
approval_date,
case
when product_status ='Cancelled' or product_status ='Stopped' then approval_date='N/A'
when product_status='Active' then NVL2(approval_date,cast(appoval_date as nvarchar2(30)),'Null')
when product_status='Completed' then NVL2(approval_date,cast(appoval_date as nvarchar2(30)),'Null1')
when product_status='Planned' then NVL2(approval_date,cast(appoval_date as nvarchar2(30)),'Null2')
end as DER_approval_date

from product_details

but i have a error in running this code saying character mismatch.

View 6 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

Forms :: How To Default Where Condition

Nov 2, 2011

I am working on forms 6i. I have set where condition in pre-query of a trigger, my requirement is, in post-query, i want to delete the where condition.

pre-query:

set_block_property('HEADER_S',default_where,'STATUS IN (select meaning from XXSMCQSS_LOOKUP_VALUES where lookup_type=''CONCESSION STATUS CODES'' and primary_flag=''N'' )');

post_query: I want to delete this where condition, i just want make execute query normal by removing the where condition.

View 8 Replies View Related

SQL & PL/SQL :: How To Update Database On Some Condition

Sep 22, 2010

I have data as below:-

select qualifier from nv2_audit_log where qualifier like '%NXPRI%NXUPM%';

/acec/nv2am/data/input/NXPRI/NXUPM.P691011_691110
/acec/nv2am/data/input/NXPRI/NXUPM.P691011_691110
/acec/nv2am/data/input/NXPRI/NXUPM.P691011_691110
/acec/nv2am/data/input/NXPRI/NXUPM.P691011_691110
/acec/nv2am/data/input/NXPRI/NXUPM.P691011_691110
/acec/nv2am/data/input/NXPRI/NXUPM.P691811_691910
/acec/nv2am/data/input/NXPRI/NXUPM.P691811_691910
/acec/nv2am/data/input/NXPRI/NXUPM.P691811_691910
/acec/nv2am/data/input/NXPRI/NXUPM.P691811_691910

The question is how could i update all the qualifier from /acec/nv2am/data/input/NXPRI/NXUPM.P691811_691910 to /acec/nv2am/data/input/NXUPM/NXUPM.P691811_691910 where qualifier like '%NXPRI%NXUPM%' ?

Actually i want to change the NXPRI to be NXUPM only for all record with qualifier like '%NXPRI%NXUPM%'

The output must be like this:-

/acec/nv2am/data/input/NXUPM/NXUPM.P691011_691110
/acec/nv2am/data/input/NXUPM/NXUPM.P691011_691110
/acec/nv2am/data/input/NXUPM/NXUPM.P691011_691110
/acec/nv2am/data/input/NXUPM/NXUPM.P691011_691110
/acec/nv2am/data/input/NXUPM/NXUPM.P691011_691110
/acec/nv2am/data/input/NXUPM/NXUPM.P691811_691910
/acec/nv2am/data/input/NXUPM/NXUPM.P691811_691910
/acec/nv2am/data/input/NXUPM/NXUPM.P691811_691910
/acec/nv2am/data/input/NXUPM/NXUPM.P691811_691910

View 2 Replies View Related







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