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
My requirement if id, join_date, join_time, result of table1 is matched with table2 at least one time then if repeating rows associated with the id should not come.Here is the test case.
create table table1
( id number , join_date varchar2(8), join_time varchar2(6), status varchar2(10));
create table table2
( id number , join_date varchar2(8), join_time varchar2(6), status varchar2(10));
insert into table1 values (01, '20010101', '0500', 'PASS');
insert into table1 values (01, '20010102', '0501', 'FAIL');
insert into table1 values (02, '20010103', '0502', 'PASS');
insert into table1 values (03, '20010104', '0503', 'FAIL');
insert into table1 values (04, '20010105', '0504', 'PASS');
insert into table1 values (05, '20010106', '0505', 'FAIL');
[code]...
I have tried the below mentioned query, whether any better query is there than this because in real-time data have 2 millions of record in table 1 and 60 thousand in table2.
select distinct a.id, a.join_date, a.join_time, a.status
from table1 a, table2 b
where a.id = b.id
and (a.id, a.join_date, a.join_time, a.status) not in (select b.id, b.join_date, b.join_time, b.status
from table2 b)
and a.id = (
select distinct a.id
[code]....
my need is to perform merge - update when id column is matched, but one of others columns not.When id column is not matched then I perform insert.
It works fine for matched or not matched id column.
Commented code is my try to perform check for others columns, The code should not update when all columns match. It should update only when on of columns doesn't match (except id column of course, because it's key column).
begin
merge into copy.table1 rr
using
(
select
ID ,
DEALID ,
ESTIMATIONDATE ,
BOUNDOVERESTIMATDATE ,
ESTIMATIONTYPEID ,
MARKETAMOUNT ,
LIQUIDATINGAMOUNT ,
[code]....
I want to Suppress the Dummy columns from the UNION Clause of Query.
SELECT
'HEADER' AS record_type
,null C2
,null C3
,null C4
,null C5
FROM dual
UNION ALL
SELECT empid
[code]....
Now I need to remove the all the Dummy fields associated to "HEADER" and "TRAILER" records.
I have a table emp as mentioned below:
SELECT * FROM EMP_TEST;
EMP_TEST
-----------------------------------------------------
ENO EFIRSTNAME ESECONDNAME DEPTNO
1 JOHN PAI 10
2 ABC DEF 20
3 EFG GHI 30
Now the primary key in this above table is pk_emp_test_eno on eno.
I have a requirement where i need to dump some dummy data (600000000 numbers of data ) into the emp_test based on these existing data without disabling the constraints (maintaining unique constraint for each record). And while inserting i want to commit after every 1000 insertion.
in bulk inserting dummy datas into the table as it is taking much more time to insert into the same.
i want to display dummy background text in one of the item of data block. is it possible is forms 10g.
for ex: i have lov on one item with WHEN MOUSE CLICK trigger.when u click mouse in that item lov popups.
so for user to understand is just want to display dummy text as CLICK HERE FOR LIST in item's background or something like disable text.
Is it possible to take export import to the Oracle DB/Schema/Table from other linux user? i have plan to create dummy user like oracle on Linux box, so developer will use this dummy user and logging to oracle BOX and take the dump. I have created one user "TEST" in linux OS. but what is the next? what privilege is required or take dump from other linux user?
oracle:x:501:502::/home/oracle:/bin/bashtest:x:502:504::/home/test:/bin/bash
I have a table where user can store images in clob format.Need to convert the same image to blob and store in a dummy table.
I tried using this
--function creation
CREATE OR REPLACE FUNCTION FN_CLOB_TO_BLOB(CLOB_IN IN CLOB) RETURN BLOB IS
POS PLS_INTEGER := 1;
BUFFER RAW(32767);
[code]...
I have a table of addresses where the indexed column consists of the city, an optional area name, the street name and the street number. For example 'Stockholm Drottninggatan 2'.
The users must enter the full city name and the beginning of the street name. So if the user wants to find all the addresses of both the streets Stockrosvägen and Stockbergsvägen which are in Stockholm, the query would look something like this:
Select * From AddressSearch
Where Contains(AddressSearch.Address, 'Stockholm AND Stock%') > 0;
But this will select all the addresses of Stockholm. Is there a way to make the part after the AND not match the already matched first part?
I have inherited a query that union alls 2 select statements, I added a further field to one of the select statements ( a date field). However I need to add another dummy field to the 2nd select statement so the union query marries up I have tried to do this by simply adding a
select
'date_on'
to add a field called date on populated by 'date_on' (the name of the column in the first query)
however when I run the union query i get the error Ora-01790 expression must have same datatype as corresponding expression.
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"
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)
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]
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
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.
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?
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:]')
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"...
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
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].....
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.
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.
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?
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.
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?
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?
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
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??
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
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].....