SQL & PL/SQL :: Match And Merge Records?
Aug 28, 2012
I have some records as below would like to match and merge them
ID1 ID2
101 103
101 104
205 103
206 103
205 106
151 222
157 222
151 223
156 222
134 223
134 435
156 438
Output should be. All matching records should be grouped together and generate result by new seq
ID1 ID2 Result
101 103 1
101 104 1
205 103 1
206 103 1
205 106 1
151 222 2
157 222 2
151 223 2
156 222 2
134 223 2
134 435 2
156 438 2
View 15 Replies
ADVERTISEMENT
Sep 30, 2010
I'm trying to insert records of one table into another using the insert into table with select logic.
I'm trying to convert a two character value using CASE statement:
CASE REC_TYPE
WHEN '00' THEN to_number('0')
ELSE to_number('1')
END "REC_TYPE"
The target field is defined as number(1,0) and the source field is varchar2(2).
I keep getting an ORA-01861 literal does not match format string error.
View 10 Replies
View Related
Jul 12, 2006
I have two identical tables with same structure and have some duplicates between those two. Finally I want to combine togather and in a single table and knockout the duplicates.
SQL how can we do this in a simple way? each tables has around millions records.
View 4 Replies
View Related
Oct 31, 2011
I have two tables with 113M records in DWH_BILL_DET & 103M in prd_rerate_chg_que and Im running following merge query, which is running for 13 hrs to update records, which is quiet longer time.
SQL> explain plan for MERGE /*+ parallel (rq, 16) */
INTO DWH_BILL_DET rq
USING (SELECT rated_que_rowid,
detail_rerate_flag_code,
rerate_sel_key,
[code].....
View 39 Replies
View Related
Oct 2, 2011
I am new to sql..
I need to join 2 tables based on first match.. I cldnot use distinct on the result, as distinct work with entire row..
I cant use group by as well, since for group by hv to select all the columns which we need to display
View 1 Replies
View Related
Jan 20, 2012
Suppose I have a table in which I have first_name, last_name, dob. Now I have to fetch on the basis of first_name=some_value, last_name=some_value and dob=some_date. I want to sort it on the basis of exactly fetched values. Let me take an example-
test table contains-
first_name last_name dob
---------- --------- ----
Manu Batham 02-Feb-1988
Manu Sharma 01-Jul-1987
Avinash Pandey 03-Feb-1988
Ankit Gupta 02-Feb-1988
Manu Aggrawal 02-Feb-1988
Manu Batham 20-Jan-1985
Sikha Batham 17-Apr-1988
Now if I give parameters-
first_name='Manu'
last_name='Batham'
dob='02-Feb-1988'
then my result should be like below-
result-
first_name last_name dob
---------- --------- ----
Manu Batham 02-Feb-1988
Manu Aggrawal 02-Feb-1988
Manu Batham 20-Jan-1985
Manu Sharma 01-Jul-1987
Ankit Gupta 02-Feb-1988
Sikha Batham 17-Apr-1988
My result is based on the approach-
if matched first_name, last_name, dob --> 1st prefrence in order
if matched first_name, dob --> 2nd prefrence in order
if matched first_name, last_name --> 3rd prefrence in order
if matched last_name, dob --> 4th prefrence in order
if matched first_name --> 5th prefrence in order
if matched last_name --> 6th prefrence in order
if matched dob --> 7th prefrence in order
I designed the following query for the same-
Select first_name,last_name,dob,1 "Order" from test Where
first_name='Manu' and
last_name='Batham' and
dob=to_date('02/02/1988','dd/mm/yyyy')
union
Select a,b,c,2 from test Where
[code]......
I know that this is not the best possible solution as the table is very big and doing so many hits on that table will certainly decrease the performance.
View 19 Replies
View Related
Jul 17, 2013
Seemingly a very simple problem, but I can't seem to figure it out. Table below is a table that holds UserId' s and LinkId's.
CREATE TABLE UserLink (UserId NUMBER, LinkId NUMBER );
ALTER TABLE UserLink ADD PRIMARY KEY (UserId, LinkId);
INSERT INTO UserLink VALUES (1, 1);
INSERT INTO UserLink VALUES (1, 2);
INSERT INTO UserLink VALUES (3, 1);
INSERT INTO UserLink VALUES (4, 1);
I'm looking for a query in which I can plug a List of LinkId's that outputs only users that have a relationship with ALL LinkId's provided. In this example you may assume that the List is equal to (1, 2), but you can't make any assumptions about the size of the list, other than size != 0. The query to find users that match ANY of the provided LinkId's is quite simple:
SELECT UserId FROM UserLink
WHERE LinkId IN (1,2)
The correct query should output:
USERID
------
1
And it should work for any size > 0 list of values.
View 6 Replies
View Related
Dec 28, 2011
I have a problem with a query I'm trying to run. I need to match two columns containing names, first column (NAME1) contains only the surname and the second column (NAME2) contains a surname and initials, with the initials turning up on either side of the surname.
Example:
NAME1: 'Bush', 'Thomas', 'Cook', 'Smith'
NAME2: 'Bush, B.B.', 'Thomas,C.' 'Cook', 'A.A. Smith'
Basically the code I'm trying only turns up complete matches and not partials. what I'm doing wrong or how I can improve it?
CODEselect *
from TEST
where NAME1 like ('%'||NAME2||'%');
View 4 Replies
View Related
Aug 2, 2012
Below is a column 'ADDR' with the data (single column)
ADDR
--------
/shared/Folder_1 :^BIAdministrator:^BIAuthor:^BIConsumer:BISystemUser:OracleSystemUser:System:weblogic: :F
[Code]....
View 2 Replies
View Related
Aug 19, 2013
find two words matching from two different tables.
Example:-
table1 || table2
john Dev || Kab Leva
Zaheer khan || mark dev
Cina maater || jhon dev wood
kab leva Sumo || Tony levis
output:-
john dev || john dev
kab leva || kab leva
View 9 Replies
View Related
Apr 23, 2010
I have table as follows:
create table sample1 (
i number ,
j date,
k number)
insert into sample1 values (1,'23-Apr-2010',11)
insert into sample1 values (2,'22-Apr-2010',12)
insert into sample1 values (3,'21-Apr-2010',13)
insert into sample1 values (4,'19-Apr-2010',14)
insert into sample1 values (5,'18-Apr-2010',15)
insert into sample1 values (6,'17-Apr-2010',16)
I would like to get nulls , if there is no data for a date. As we can see , here i am missing the data for '20-Apr-2010'.
I did it through "UNIX" , but it's not efficient.
The data might be missing for the complete week also. I need to test in this way only for the last 7 days. I tried something like this:
select i, j , sum(k)
from sample1
where j in (select to_date(sysdate - rownum)
from dual
connect by rownum < = 7)
group by i, j
View 3 Replies
View Related
Feb 28, 2011
In query I have WHERE clause like this:
WHERE TO_DATE(TO_CHAR(RR.PEROFOPFROM,'DD-MON-YYYY')||RR.AIRCRAFTSTD,'DD-MON-YYYY:HH24MI') >
TO_DATE(TO_CHAR(RR.PEROFOPFROM,'DD-MON-YYYY')||RR.AIRCRAFTSTA,'DD-MON-YYYY:HH24MI')
I have data like this:
PEROFOPFROMAIRCRAFTSTD
29/03/20102150
NULL NULL
NULL NULL
30/03/20102150
When I execute the query it always gives me the error "literal does not match format string".
View 7 Replies
View Related
Dec 6, 2012
I'm trying to do data mining on a web log which recorded one day web access information from a busy web server. I imported the data into Oracle Data miner, and created a table (WEBLOG). The idea is to create a new field, i.e. session, for the users so that each session could be thought as a representative of a user-intent (aka topic). Now based on this, data mining models would be used to cluster(group) the users based on their similarity. The first step is to prepare the data which involves using SQL queries. So first, all I did was to create a function for date and time. This is the following code I used,
create or replace function ssndate(p_date in varchar2 default '03-01-18',
p_time in varchar2)
return number
$if dbms_db_version.ver_le_10 $then
deterministic
$elsif dbms_db_version.ver_le_11 $then
result_cache
$end
as
begin
return trunc((to_date(p_date||' '||p_time, 'dd-mm-yy hh24:mi:ss')
- to_date('01-01-90','dd-mm-yy')) * (86400/2400));
end ssndate;
/
The function ssndate compiled successfully.The next step I took was to create a view through the following query,
create or replace view WEBLOG_VIEWS
as
select (select ssndate(LOG_DATE, LOG_TIME) from dual) as "SESSION_DT",
C_IP,
CS_USER_AGENT,
(CS_URI_STEM||'?'||CS_URI_QUERY) as WEB_LINK
from WEBLOG;
This was successful as well. The problem is in the next step where I try to do data grouping.
create table FINAL_WEBLOG as
select SESSION_DT, C_IP, CS_USER_AGENT,
listagg(WEB_LINK, ' ')
within group(order by C_IP, CS_USER_AGENT) "WEB_LINKS"
from WEBLOG_VIEWS
group by C_IP, CS_USER_AGENT, SESSION_DT
order by SESSION_DT
[code]....
the to_date function should be fine. In the data that I possess, the date and time are in no format. Example: 30118 and 0:00:09 respectively.
View 6 Replies
View Related
Sep 3, 2012
I've created a table, i have to insert data into it. Herwith Create table statement:
CREATE TABLE IOSTAT_MAC (
IS_DATE DATE,
IS_RS NUMBER(4,2),
IS_WS NUMBER(2,2),
IS_KRS NUMBER(5,2),
IS_KWS NUMBER(5,2),
[code]....
herewith data I have to insert
insert into iostat_mac values(to_date('2012/03/28 08:00:00 AM'),'653.6','20.7','15392.0','451.8','0.0','5.5','0.0','8.2','0','64','/vol/sun_dc_u2');
Receive ora-01861 error when trying to insert.Suspected it had to do with the date format, I changed the nls parameter for my session to include the time format, but to no avail.
View 4 Replies
View Related
Jul 27, 2012
Here is the code :
CREATE OR REPLACE PROCEDURE SEND_MESSAGE_PRC (P_MESSAGE_CONTROL_id varchar2)
IS
BEGIN
DBMS_SCHEDULER.DROP_job(job_name => 'ftp_job');
DBMS_SCHEDULER.drop_program (program_name => 'ftp_prg');
DBMS_SCHEDULER.create_program(program_name => 'ftp_prg',
[code]...
When I execute this : I get an error saying that "too many declarations of set_job_argument_value match this call"..It worked before.
View 2 Replies
View Related
Jan 9, 2013
I would like add an additional column to the data below:
create table test(
id number
cust_num varchar2(5));
[Code]....
Result:
001, AODER, 'Y'
001, BODER, 'Y'
001, CODER, 'Y'
001, DODER, 'Y'
001, 'NONE', 'Y'
001, 'NONE', 'Y'
[Code]...
I would like to add an additional column indicator (Y or N) to specify which ID's do not contain all records of 'NONE'. There can be an occurrence of 'NONE' as long as there is another cust_num different to 'NONE' These should be marked as 'Y' but in cases where all the ID's cust_num = 'NONE' only then these should be marked as 'N'.
View 5 Replies
View Related
May 23, 2012
TABLE 1 : COSTS
I have a table of Costs. We have Jobs that run and there will be a cost associated with a particular machine.So JobNo 1 may run on Machine A and have a cost of 50 dollars. Although its now shown below JobNO 1 could run on MachineB and so on.
JOBNO MACHINE COST
1 MachineA 50
2 MachineA 70
3 MachineA 100
TABLE 2: OPERATIONS
We have operators (PERSONCODE) run the jobs on the machines. So Job 1 may be run by PERSONCODE 8 (e.g. Tony) and it may run on MachineA or MachineB.Multipe people may run a particular job. The PERSONCODE will be unique to the Job and it is actually unique to the list. A person never works on more than one job.
JOBNO MACHINE PERSONCODE
1 MachineA8
1 MachineA7
1 MachineB6
2 MachineA3
2 MachineA2
2 MachineA1
3 MachineA4
DESIRED RESULT:
I need to assign the COSTs to the OPERATIONS table but only want it to show next to one person (next to the appropriate machine).
DESIRED RESULT:
JOBNO MACHINE PERSONCODE COST
1 MachineA8 50
1 MachineA7
1 MachineB6
2 MachineA3 70
2 MachineA2
2 MachineA1
3 MachineA4 100
[code]....
View 5 Replies
View Related
Aug 11, 2010
I've got a oracle install [non production, but devel] that is a tad screwed up. We moved the box and as a result changed the hostname to match the new naming scheme. Ever since then OracleEM has been somewhat confused. In anycase, I don't want OEM anyways now. Plan is to learn SQLplus.
That being said I've used emctl to shut down dbconsole, but it seems there is something somewhere that keeps restarting 2 processes that like to sit around and take up 100% cpu. I can kill them, they stay dead for a few hours then crop up again.I was able to find this out about them:
[jmacdonald@devoracle ~]$ ps auxwww|grep 2033
oracle 20334 84.1 12.3 994052 255824 ? Rs Aug10 1740:43 ora_j000_orcl
oracle 20336 80.9 14.1 998140 294288 ? Ss Aug10 1674:18 ora_j001_orcl
And then this, which caused me to conlucde its OracleEM:
SELECT sess.process, sess.status, sess.username, sess.schemaname, sql.sql_text
FROM v$session sess,
v$sql sql
WHERE sql.sql_id(+) = sess.sql_id
AND sess.process in (20334,20336)
[code]...
View 6 Replies
View Related
Jul 3, 2012
i have three tables ot_cut_head,ot_cut_det and om_mc_master based on which fourth table ot_cut_opr and fifth table ot_cut_mc must get populated , Conditions are as follows
first one is based on job_no in ot_cut_head the selection criteria will be filtered,if the job number is like '%M' then type MISC will be chosen ,if job number is '%G' then GRAT TYPE will be picked from om_mc_master (Machine Master) and operations and machines based on this will be filtered.
Second all the cd_ps_desc will be taken from ot_cut_det and will be compared with om_mc_master to get their corresponding operation codes and machine codes , there can be 2 operations or 1 operation.
Finally if the match is found record will be inserted into ot_cut_opr and ot_cut_mc ,based on the criterias and what i want is the search criteria to be more flexible and if there are 2 operations 2 rows will be inserted and if one opeation is defined in om_mc_master ,then only one record will be inserted.
We have to make sure that if based on operation number stage will be populated ,if its first operation then stage will be 1 and if its second operation the stage will be 2.like previous operation also depends on them , the second operation will have the previous operation as first operation and so on.
CREATE TABLE om_mc_master ( mc_type VARCHAR2(12),mc_prof VARCHAR2(30),mc_prep_cd1 VARCHAR2(30),mc_mach_cd1 VARCHAR2
(30),mc_prep_cd2 VARCHAR2(30),mc_mach_cd2 VARCHAR2(30));
INSERT INTO OM_MC_MASTER VALUES ('MISC','TEE SCH','IR','HO','RE','HO');
insert into om_mc_master values('MISC','Vertical Brace','R','HM','I','HO');
insert into om_mc_master values('MISC','Pipe','IR','HO',NULL,NULL);
INSERT INTO OM_MC_MASTER VALUES ('GRAT','PL','RE','HO',NULL,NULL);
SQL> SELECT * FROM OM_MC_MASTER;
[code]....
View 6 Replies
View Related
Dec 9, 2011
We have a SSRS Front end screen which sends multi-select column values as comma separated strings to back end ( Oracle 10g) procedure .
The procedure builds the string by inserting single quotes in the following manner.
P_BU_LST is the parameter which have comma separated values
'1234,3456,4577' i.e, BU ids selected by user in front end
the procedure inserts single quotes to this paramer value
i.e., '1234','3456','4577
v_bu_lst := '''' || REPLACE(v_selbu, ',', ''',''')|| '''';
This is used the where clause of the REF CURSOR SELECT query which send the data back to SSRS
ie.,
SELECT BU.*
FROM BU_DETAIL BU
WHERE INSTR(V_BU_LST,BU_ID) <> 0;
INSTR has a chance to fail in this scenario if the value send from the front end is 123456,3456,4577
here 123456 does not exist in table, but it will be true for INSTR and values 1234 from table will be send back to SSRS which is wrong. Earlier I was using a function to convert the comma separated values to multi-rows and treat it like a lookup table.
But the main table has around million records , and each row has to processed against each row of lookup table, which makes it slower. To avoid this I used INSTR which is faster but can give wrong results.
View 8 Replies
View Related
Mar 21, 2013
I want to find the row with invalid day, month which are not matching with calendar day and month. Also the program should capture the data if the year <1900
with xx as
(select 101 as ID, '24/05/1899' as create_date from dual
union all
select 101, '32/03/2012' from dual
union all
select 102 ,'30/02/2012' from dual
union all
select 101 , '29/02/2013' from dual
[code]...
View 16 Replies
View Related
Jul 1, 2012
Every command I issue in RMAN I get these errors:
RMAN-03002: failure of report command at 07/01/2012 12:33:40 ORA-01861: literal does not match format stringHow should I resolve it?
View 2 Replies
View Related
Nov 30, 2011
While starting up my database i am getting this error.by mistake i had moved log files, and after some time again i inserted those log files again into same directory i am getting this error.
View 13 Replies
View Related
Nov 29, 2007
When I try to update a date column with this:
UPDATE
event_request
SET
REQ_EVENT_DATE = TO_CHAR(REQ_EVENT_DATE - INTERVAL '1' HOUR, 'yyyy-mm-dd hh24:mi')
WHERE
eventID=123
Oracle returns this error:
ERROR at line 4:ORA-01861: literal does not match format string
where line 4 is REQ_EVENT_DATE = TO_CHAR(REQ_EVENT_DATE - INTERVAL '1' HOUR, 'yyyy-mm-dd hh24:mi').The REQ_EVENT_DATE field was originally populated with this stripped down query:
$date = '2007-12-25 08:35'; INSERT INTO (REQ_EVENT_DATE) VALUES (to_date('$date', 'yyyy-mm-dd hh24:mi'));
So - what is wrong with the UPDATE query?
View 1 Replies
View Related
Feb 29, 2012
When i open the primary database in my dataguard env,it raise error?
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00314: log 1 of thread 1, expected sequence# 77 doesn't match 0
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/oracl/redo01.log'
View 9 Replies
View Related
Nov 25, 2011
While creating a controlfile while bringing the database up we are consistently getting the below error
ORA-01161: database name CCSPS in file header does not match given name of CCSP
ORA-01110: data file 312: '/aoq53/appl/oracle/ccsps/dtmcs01_07.dbf'
The controlfile creation script is as follows.
CREATE CONTROLFILE SET DATABASE "CCSPS" RESETLOGS NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 255
MAXLOGMEMBERS 2
MAXDATAFILES 1022
MAXINSTANCES 8
MAXLOGHISTORY 904
[code]....
our production database is not coming up due to this issue and we are on the verge of missing SLA.
View 6 Replies
View Related
May 10, 2013
I did import from 9i to 11gr2 , 1. i create 11gr2 DB , 2.created tablespace with 8kb block, 3 imported 9i dump to 11gr2 DB.
Now iam getting SOME ERRORS: In IMP LOG
1. ORA-29339: tablespace block size 4096 does not match configured block sizes == for all the tablespaces.(But i create TBS with 8kb block before IMPORT)
2. ORA-23327: imported deferred rpc data does not match platform of importing db
View 4 Replies
View Related
Sep 8, 2011
run down of the implications of MERGE by ROWID in such a fashion:
CODE MERGE
INTO XXWT_AP_ACCRUALS_RECEIPT_F EXT
USING (
SELECT PO_DISTRIBUTION_ID,
[code]...
Can this lead to an "Unstable Set of Rows?". Is it possible for the ROWID's to change during the execution of this statement - meaning certain ROWIDs identified in the SELECT will not actually be updated when it comes to the MERGE operation?
Basically, is it sound practice to use ROWID to merge on - in cases where you dont have a WHEN NOT MATCHED condition?
View 8 Replies
View Related
Sep 17, 2011
i HAVE TWO TABLES
EMP_NAME_DETAILS
EMP_ID EMPNAME
EMP_ADD_DETAILS
EMP_ID EMPCITY
NOW I WANT TO MERGE/COMBINE THIS TWO TABLES INTO ONE FLAT TABLE SO MY REPORTS WILL RUN ONLY FROM ONE TABLE INSTEAD OF TWO.
MY DESIRED OUTPUT TABLE WOULD BE
EMP_ID EMPNAME EMPCITY
View 2 Replies
View Related
Nov 23, 2012
the query ..
Below are the codes
create table testing1
(
id number,
data varchar2(20)
)
insert all into testing1 values (1,'100')
into testing1 values (2,'Ram')
[Code]....
I want the output as
100 Ram Pune
200 Shyam Mumbai
300 Kamal bangalore
View 8 Replies
View Related
