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
ADVERTISEMENT
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
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
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Sep 21, 2011
I have a table A with 10000 records with values as 1 to 10000. Table B has columns as range,number1,number2. Totally there are three rows as
ROW1=range1,1,100 & ROW2=range2,300,500 & ROW3=range3,800,900
I need to select all records from table A when it is between any one of the three rows from table B.
View 13 Replies
View Related
May 6, 2013
I have a situation where I need to check if a row in the table locked i.e (Select for Update with NO WAIT applied on it). If yes go fetch the next row .
View 4 Replies
View Related
May 4, 2011
Suppose I have a table.
SQL> select * from emp;
EMPNO ENAME DEPTNO SAL
---------- ---------- ---------- ----------
1 RN 10 10
3 C 20 40
4 A 10 20
5 B 20 100
6 D 10 11
7 S 30 300
6 rows selected
Now I need to update the table as for all employees will get sal 100 for depetno=10 and employees of deptno=20 will get sal=200. I need to update this with a single query.
View 12 Replies
View Related
Jun 6, 2012
Is there any way through which we can put forth a condition on Alias name instead of column name in a table.
For example: Select emp_name "Employee Name" fom dual where emp_id is not null;
Instead can there be a workaround so I can put "Employee Name" in where clause?
View 7 Replies
View Related
Feb 16, 2011
I Require to Update the Data in Join Condition. When Run the Query the Error display as ORA-00933: SQL Command Not Properly Ended.
Query:
Update a set a.Dr_Re = Nvl(b.Dr_Amt,0),
a.Cr_Re = Nvl(b.Cr_Amt,0)
FROM xxsc.xxsc_creditors_aging_brnwise a
join (Select Branch,Invoice_id,vendor_site_code,segment1,
case when (sum(nvl(dr_re,0)) - sum(nvl(cr_re,0)) > 0) then sum(nvl(dr_re,0)) - sum(nvl(cr_re,0))
Else 0 End DR_AMT,
[code]........
View 2 Replies
View Related
