Join Using First Match

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


ADVERTISEMENT

SQL & PL/SQL :: Sort By Best Match

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

SQL & PL/SQL :: Normal Join And Outer Join

Oct 19, 2013

Lets say I have three tables t1 and t2 and t3.

SELECT * FROM T1;

Id
____
1
2
3
4

SELECT * FROM T2;

Id
____
1

SELECT * FROM T3;

Id
____
1

Now when data exists in T2 and T3, I want to return only the records in T1 that match the records in T2 and T3 which is basically a normal join

select t1.id from t1, t2,t3 where t1.id = t2.id and t1.id = t3.id

However when there are no records in T2 or T3, I want to return all records in T1 i.e 1,2,3,4

One way of doing that is using the not exists clause

select * from t1 where not exists ( select null from t2 where t2.Id != t1.id) and not exists ( select null from t3 where t1.Id != t3.id)

Is there a better way of doing this in sql ?

View 5 Replies View Related

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

PL/SQL :: Only Get Users That Match All Links

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

Like Condition - Match Two Columns Containing Names?

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

SQL & PL/SQL :: Find A Match Within String And Compare?

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

SQL & PL/SQL :: How To Match Two Words Matching From Two Table

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

SQL & PL/SQL :: To Display Null Rows When No Match

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

SQL & PL/SQL :: Where Clause - Literal Does Not Match Format String

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

SQL & PL/SQL :: ORA-01861 - Literal Does Not Match Format String

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

PL/SQL :: ORA-01861 / Literal Does Not Match Format String

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

SQL & PL/SQL :: Error / Too Many Declarations Of Set_job_argument_value Match This Call

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

PL/SQL :: Match Condition - Add Additional Column Indicator

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

SQL & PL/SQL :: Linking Table Challenge - Showing A Value Only Once Not For Each Match?

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

Result Changed Hostname To Match New Naming Scheme

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

SQL & PL/SQL :: Insert Using String Matches / Matching And Searching Match

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

SQL & PL/SQL :: Using INSTR In Where Clause To Give COMPLETE MATCH Strings?

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

PL/SQL :: Find Invalid Date And Month Not Match With Calendar?

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

Recovery Manager (RMAN) :: Does Not Match Format String

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

Backup & Recovery :: ORA-00314 - Log 3 Of Thread 1 - Expected Sequence 207 Does Not Match 198?

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

SQL & PL/SQL :: Insert Records Of One Table Into Another - Literal Does Not Match Format String

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

UPDATE Date / Literal Doesn't Match Format String

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

Data Guard :: ORA-00314 / Log 1 Of Thread 1 - Expected Sequence# 77 Doesn't Match 0

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

Backup & Recovery :: ORA-01161 Database Name CCSPS In File Header Doesn't Match Given Name Of CCSP

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

Export/Import/SQL Loader :: ORA-23327 / Imported Deferred RPC Data Does Not Match Platform Of Importing DB

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

SQL & PL/SQL :: Left Outer Join Versus Right Outer Join

Aug 14, 2009

i want to know the difference between Left outer join Vs. Right outer join? Its like which join is safer to use or is there any recommendations to use any join?

View 6 Replies View Related

SQL & PL/SQL :: Interface Table Compared With Normal Table - Result Dumped If Match Found

Aug 17, 2012

Oracle 10g, Windows XP

There is an interface table and there is an normal transcational table..interface table is being compared with normal table and if match found the result is dumped into another normal table.

I am using two cursors one is to query the interface table and in a for loop pass the results to the second cursor..The interface table is having 5000 + rows and the transcation table is having more than 3.7 millions ..and the program is taking lots of time to execute..took almost 35-45 minutes..

create table x_interface /* INterface table */ -- 5000 + rows
( name varchar2(80), addr_line1 varchar2(35), addr_line2 varchar2(35), addr_line3 varchar2(35),
addr_line4 varchar2(35), addr_line5 varchar2(35), addr_line6 varchar2(35), suffix varchar2(35),
city varchar2(15), state varchar2(10), zcode varchar2(10))
[code]....

View 7 Replies View Related

Application Express :: Trigger To Update Team State Which Have Same Match ID Of Another Team

Jun 11, 2012

I have a table which contain match ID , team and the state of the team Each match ID can contain only two teams and I did trigger for that, what I want to do is when the user update the table and add the state after the match is played,, the other team state is changed to the opposite ,, except for the draw which both of them should have the same state..For example when team 1 state is set to win, team 2 which have same team 1 ID, should be updated to lose....

I want to create a trigger to do that,,I tried to do a trigger but not working

create or replace TRIGGER  "UPDATE_STATE"
AFTER UPDATE ON  "SPORT_MATCH"
FOR EACH ROW
  BEGIN
   if (:new.state = 'Wins') then
  UPDATE SPORT_MATCH SET STATE  = 'LOSES' WHERE SPORT_MATCH.MAtchID = :NEW.MATCH_ID and Team != :New.Team;         
END IF;
END;I got this error
•ORA-04091: table MATCH is mutating, trigger/function may not see

View 3 Replies View Related

Text :: Make Part After AND Not Match Already Matched First Part?

Nov 2, 2012

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?

View 2 Replies View Related







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