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
ADVERTISEMENT
Jun 20, 2011
I have a materialized view (smpl_mview) if i want to see the SELECT statement written for the materialized view, how can i see/retrive select stmt?
View 1 Replies
View Related
May 15, 2010
how to insert data in oracle table without writing insert statement in oracle 9i or above. i am not going to write insert all, merge, sqlloder and import data.
View 2 Replies
View Related
Oct 28, 2013
I wish to make this simple statement with Toad GUI
INSERT INTO EXCLUDE_xxx
VALUES ('xxx',
'xxx',
'xxx',
'xxx',
SYSDATE);
Insert record is greyed out. How to insert new rows with Toad (click click)?
View 7 Replies
View Related
Feb 27, 2012
when i tried to insert the details from oracle froms..the data inserts twice to the DB..
my table structure:
create table app_sri
(a_id integer primary key,
p_first_name varchar2(30),
p_last_name varchar2(20),
p_age number(3)
);
here a_id can be genarated through simple sequence(pid_seq)...
trigger on app_sri
create or replace trigger pid_trg
[Code]....
form insertion code..
Begin
insert into app_sri(null,'robo','Big',100);
commit
End;
the data inserted...but twice
what is the reason behind the double insertion?
View 8 Replies
View Related
Feb 24, 2012
INSERT INTO LKP_ASSET_LOCATION (LOCATION) VALUES ('AMERICA'S CUP VILLAGE')
View 2 Replies
View Related
May 7, 2013
In 11g, When I am trying to insert the records with select insert option it is failing.
Below is my Query:
insert into table_1 select * from table_2
The above query is not inserting any records into table_1. But when i query select * from table it is returning records.
The same select Insert query is inserting records properly in 10g.
View 5 Replies
View Related
Jan 2, 2009
I'm trying to insert only a few columns (not all of them) from temp_ioi_010209 into mtl_system_items_interface. Both of these tables have more columns than just the 7 I'm specifying but everything I found under INSERT ALL here makes me think I'm doing it right. According to the DESCRIBE of mtl_system_items_interface the only non-nullable column is set_process_id and I'm specifying that one...
Why I'm getting the error "ORA-00947: not enough values"?
INSERT ALL INTO mtl_system_items_interface
VALUES
(process_flag,
transaction_type,
set_process_id,
[code].......
View 3 Replies
View Related
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
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
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
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
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
