SQL & PL/SQL :: Table Join Query
Nov 10, 2010
I have the following 2 tables.
A) Docversion
CREATE TABLE DOCVERSION
("OBJECT_ID" VARCHAR2(250 BYTE),
"OBJECT_CLASS_ID" VARCHAR2(250 BYTE),
);
[Code]..
Join column : object_id and parent_id.
The object id in docversion will have multiple values for element value in listofstring table.
ordinal value represents teh sequence of element value
Eg:
data for docversion:
1 23
2 34
data for LISTOFSTRING:
1 11 0 100
1 11 1 109
1 11 2 119
2 22 0 A
2 22 1 B
ouptut:
I want the output as follows
docversion.objectid,listofstring.elementvalue
1 100,109,119
2 A,B
View 14 Replies
ADVERTISEMENT
Jun 22, 2011
I have this table
create table ACTIONARI_ARH
(
actionar_id NUMBER(10) not null,
id VARCHAR2(20) not null,
id_2 VARCHAR2(20),
tip VARCHAR2(1),
nume VARCHAR2(100),
prenume VARCHAR2(100),
adresa VARCHAR2(200),
[code]....
and this view
CREATE OR REPLACE VIEW ACTIONARI AS
SELECT "ACTIONAR_ID","ID","ID_2","TIP","NUME","PRENUME","ADRESA","LOCALITATE","JUDET","TARA","CERT_DECES","DATA_REGISTRU" Data_operare,"USER_MODIF","DATA_MODIF","REZIDENT"
FROM (
select
[code]....
The table has about 30 milion records and holds persons names, addresses, personal id (id), and internal id(actionar_id) and date when a new adress has been added.
The view is about getting only the most recent info for one person (actionar_id).
if i run a
a) select * from actionari a where a.actionar_id = 'nnnnnnn', result is returned immediatly, oracle uses index and does not do a full table scan.
b) select * from actionari a where a.actionar_id in ('nnnnnnn','mmmmmm','ooooooo'), result is returned immediatly, oracle uses index and does not do a full table scan.
my problem when i use this view in a join.let's assume i have another table with no more than 500 records, something like
create table SMALL_TABLE
(
actionar_id NUMBER(10) not null,
......
);
and if i run
select *
from SMALL_TABLE s
join actionari a
on a.actionar_id = s.actionar_id;
it takes like forever to process, forever means 1~3 minutes.by looking at the execution plan, oracle does a full table scan, creates the view for all unique 7milion persons, and only then joins the result with the actionar_is's in the small table and returns the desired 500 record result.i am using oracle 10g.
View 2 Replies
View Related
Nov 13, 2011
The existing format of the records are
MCF_ID MCF_PRDGRP MCF_BSCNME MCF_BSCSUP MCF_CRDNME Cno
------ ---------- ---------- ---------- ---------- ---
41956 1001 LIM KOK HWA Base LIM KOK HWA 101
41957 1102 CHEN ZHEN Base CHEN ZHEN 102
41958 1102 CHEN ZHEN Sub CHEN HONGJIAN 103
41960 2007 CHEN ZHEN Base CHEN ZHEN 104
41961 2007 CHEN ZHEN Sub CHEN HONGJIAN 105
41968 2108 WEE LIANG Base WEE LIANG 106
41969 2108 LOW KAH Sub LOW KAH 107
This should be modified as below.
MCF_ID MCF_PRDGRP MCF_BSCNME MCF_BSCSUP MCF_CRDNME Indicator Sub Name baseCno SubCno
------ ---------- ---------- ---------- ---------- ---------- -------- ------ ------
41956 1001 LIM KOK HWA Base LIM KOK HWA 101
41957 1102 CHEN ZHEN Base CHEN ZHEN Sub CHEN HONGJIAN 102 103
41960 2007 CHEN ZHEN Base CHEN ZHEN Sub CHEN HONGJIAN 104 105
41968 2108 WEE LIANG Base WEE LIANG 106
41969 2108 LOW KAH Sub LOW KAH 107
[Code]..
Throught this query, I get a extra record with null value in the Sub_name and Sub_Cno.update this query using a self join.
View 5 Replies
View Related
Nov 2, 2012
I have an attendance table in which we have empno, time_in, time_out..etc.
I want to create a matrix report that should shows all dates of any specific required month in columns and present empno in rows and time_in in cells.
I did it with using a temporary table by filling all the required dates and outer join with attendance table, it works fine, but it takes so long, is there any other better fast way to do it?
View 7 Replies
View Related
Jun 26, 2012
How to update single table column using join query
Example:
Update table1 t1,table2 t2
set t1.column2 = 'Y'
where t1.column1 = t2.column1
View 8 Replies
View Related
Oct 26, 2011
I have come across a join in a query which has the (+) symbol at the end of it.
View 3 Replies
View Related
Oct 11, 2013
I have been told to create a query which should give out the same output as the UI of an exception management application . However , many referenced tables have one to many relationship . I created a query using a function which was giving the required output . But the performance of the query was not good as for each row . Is there a method to create a select using joins to show one to many relationship in a table .
View 4 Replies
View Related
Feb 27, 2008
In my Oracle 9i Schema, I have two tables:
Table One
pocOne pocTwo
2 3
2 4
1 2
Table Two
TableTwoId Name
1 Jones
2 Smith
3 Edwards
4 Camden
My SQL to fetch all records with Smith works great:
select Name from TableTwo
Inner Join TableOne
on TableTwo.TableTwoId in (TableOne.pocOne, TableOne.pocTwo)
where Name = 'Smith'
Now I need to create an alias for the Name field. Here is my attempt:
select myAliasName from TableTwo
Inner Join TableOne
on TableTwo.TableTwoId in (TableOne.pocOne, TableOne.pocTwo),
(select Name as myAliasName from TableTwo)
where myAliasName = 'Smith'
This attempt pulls up all the records instead of just Smith records. How I can create an alias for the Name field in my above query?
View 3 Replies
View Related
Sep 10, 2012
I want to convert join to subquery. how cna i do this.
FROM
TABLE_1 A
INNER JOIN TABLE_2 B
ON ( A.COL_1 = B.COL_1)
[Code]....
View 6 Replies
View Related
May 19, 2011
TABLE - 1
CIDDOB(DATE)
12312-MAR-63
58918-JAN-78
658927-DEC-43
46515-FEB-80
TABLE - 2
DIDDOB_INFO(VARCHAR2)
34425 Mar 1967
123 12 MAR 1963;25 FEB 1974;25 AUG 1978
46515 FEB 1980
I want to join the columns DOB from table -1 and DOB_INFO from table 2 but the datatype of DOB is DATE and DOB_INFO is VARCHAR. TO_DATE function is not working here.
View 13 Replies
View Related
Dec 2, 2010
the following statement gives each customer owns how many promotions.
Q:) how do i retrieve a customer who has max. promotions?
SELECT C.CUSTOMER_ID,COUNT(P.PROMOTION_ID)
FROM PROMOTIONS P,CUSTOMERS C
WHERE C.CUSTOMER_ID = P.CUSTOMER_ID
GROUP BY C.CUSTOMER_ID
=====================================================
CUSTOMER_ID COUNT(P.PROMOTION_ID)
-------------------------------------
001 | 5
002 | 8
003 | 4
004 | 6
005 | 5
006 | 3
View 7 Replies
View Related
Dec 14, 2010
In SQL, almost all the thing which are possible with join is possible with sub-query also and vice-a-versa.
So when should I use sub-query and when should I go for join?
View 9 Replies
View Related
Jun 27, 2013
How to use outer join condition in my below query. In the table APPS_JP.GEDIS_OFFER_HEADER goh I have more records in the table APPS_JP.GEDIS_ORDER_BUILDS gob I have less number of records.
I want all the records from APPS_JP.GEDIS_OFFER_HEADER goh including other conditions. I have tried goh.OFFER_NO=gob.OFFER_NO(+) but same result.
SELECT GOH.ORIG_SYSTEM,
gsp.USER_NAME,
goh.ORDER_NO,
goh.OMEGA_ORDER_NUMBER,
goh.ORDER_TYPE,
[code].......
View 10 Replies
View Related
Jan 20, 2011
joining this query instead of using the left join. Reason is want to show the score column in a different place and also do not want to show the second IPS column that is used in the joined query.
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
select * from
(
select i.ips,
p.project_name,
p.project_segment,p.location,p.project_exec_model,
p.project_exec_model||' - '||p.project_config pmodel,
one.score schedule,two.score cost,three.score execution,four.score commercial,
nvl(one.score,0)+nvl(two.score,0)+nvl(three.score,0)+nvl(four.score,0) as total,
[code]....
View 6 Replies
View Related
May 13, 2013
I saw a strange plan for one query in TESTING DB today. Although 2 tables are involved i dont see any join , NL/HJ/SMJ !! i am not facing any performance issue but curious to know what type of optimization oracle is doing here.
Query text and plan :
SELECT FIRST_NAME
FROM CAMPA.TABLE_A
WHERE NAME_ID =
(SELECT NAME_ID
FROM CAMPA.TABLE_B
WHERE ban=:b1);
[code]....
View 7 Replies
View Related
Oct 24, 2012
must generate a Cartesian join, but I do not know why it happens. dt.debtorkey, cl.clientkey, inv.invoicekey, ag.agingkey are primary keys of each table. The problem is: I got same tuple for 8 times.
select dt.debtorkey, cl.clientkey, inv.invoicekey, ag.agingkey, dt.DebtorNo, dt.Name as "debtor Name", dt.State, cl.ClientNo, cl.Name as "Client Name", inv.InvNo, inv.PurchOrd, inv.Amt,
to_char(inv.InvDate, 'MM-DD-YY') invoice_date, to_char(ag.DateLastBuy, 'MM-DD-YY') aging_lastbuy, to_char(ag.DateLastPmt, 'MM-DD-YY') aging_lastpmt
[code]...
View 14 Replies
View Related
Nov 16, 2012
I've seen this example numerous places, and tried to implement it, but I keep getting an "invalid identifier" error message, despite the fact that I've got the table and column specifically identified.For instance, my query reads like:
UPDATE tbl1
SET tbl1.EMPID =
(SELECT tbl2.EMPIDA FROM tbl2
WHERE LOWER(tbl1.EMAILCOL) = LOWER(tbl2.EMAILCOL2)
)
WHERE tbl2.EMPIDA IN ('Z1O435','S8M4722','M0D5156')
AND EXISTS
(SELECT tbl2.EMPIDA
FROM tbl2
WHERE tbl1.EMAILCOL= tbl2.EMAILCOL2 );
But I'll keep getting flagged at the tbl2.EMPIDA column reference. I have not tried this in SQL Plus, just in TOAD, but it seems to repeatedly fail.I have had to dump records to standalone Access tables and link back to perform the updates.
View 12 Replies
View Related
Jun 16, 2011
I have two tables. By joining these two tables, I need to update a field in table1.
UPDATE table1
SET table1.FLAG = 'Fixed'
where table2.lastname = table1.lastname
and table2.status in ('fulltime','parttime')
I keep getting error 'table1.lastname' is invalid identifier.
I can't understand the error message. I made sure that the fields exist.
View 5 Replies
View Related
Jul 23, 2013
The full statement is:
SELECT v.key$ FROM VERSION_TABLE v, DOCUMENT_TABLE d, CLASS_TABLE z WHERE
v.documentKey = d.key$ AND
d.classKey = z.key$ AND
z.key$ IN (SELECT zz.key$ FROM CLASS_TABLE zz
START WITH zz.name = 'esDTTemplate'
CONNECT BY PRIOR zz.key$ = zz.parentKey) AND
v.ESGROUP = 'SearchOperatorsMapping' ORDER BY d.name
Now I noticed that the subquery is never used to seed the join: indexes - if any - are used. Otherwise a full table scan is performed.In the example - if ESGROUP is indexed, then it's chosen to start the join evaluation. If not, a full table scan is performed.Is there any way to suggest to the optimizer to use the subquery in case there are no indexes - as a fallback ?
In the above example where VERSION_TABLE contains nearly two million records, the no index solution takes 60 secs. vs. less than 1 sec. in the index case.Wrapping the hierarchical query in a inline view leads to same result.
PS: the execution plan (without index) is:
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| |
| 1 | SORT ORDER BY | | 1 | 171 | 9 (23)| 00:00:01 |
|* 2 | HASH JOIN SEMI | | 1 | 171 | 8 (13)| 00:00:01 |
[code]...
View 3 Replies
View Related
Nov 15, 2010
I have constructed a cross join query, with the test case below
create table ajit_sites (
site_id char(1));
insert into ajit_sites values ('A');
insert into ajit_sites values ('B');
insert into ajit_sites values ('C');
COMMIT;
sql below is constructed to display combination of all sites (cross-join), it also removes records where "origin" is the same with "dest"
select
a.site_id origin, b.site_id dest
from
(select site_id from ajit_sites) a,
(select site_id from ajit_sites) b
where
a.site_id <> b.site_id b
Is there any way i could remove records with the behavior below
Origin , Dest
A , B
B , A
For instance from the example above, i want to only retain one of the records since record (A, B) or record (B, A) means the same.
View 3 Replies
View Related
Apr 5, 2013
How Can I delete the returned two rows?
1 select s.reg_no,s.course_code,
2 s.section src_sec,a.section a_sec,a.att_date,a.att_flag
3 from attendance a ,src s
4 where a.semester_code=1
5 and a.semester_year=2013
6 and s.semester_code=1
[code]....
View 6 Replies
View Related
Jul 30, 2012
I stumbled about some weird 11gR2 behavior (running on AIX).When I performed a join between a table with user based content (parts belonging to an sourcing scope) and a base table (parts available) whereas the parts have to fulfill a special regular expression, it showed that the same query is faster when using outer join than inner join (about 0.7sec vs. 20sec; which makes me believe that regexp_like works wrong when involved in an inner join).
i tried the same statement with a standard like (but not fulfilling the same condition).This time performance was as expected (inner join outperforming outer join).
Oracle version information
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
[code]...
I can see it, the execution plan for the "inner join" doesn't show so much more costs than the one for the outer (but why at all is does an inner join cost more?) ...The execution plan for both "not like" is the same and (surprisingly ;-) ) similar to "outer-regexp".
I hope sample data are not needed as there would be needed a lot...this is the second time I came across the "plan worse but execution time better" phenomenon.
View 10 Replies
View Related
Jul 18, 2012
I am trying to join column names from a table with data from a different table. I think i should be able to pass the parameter to a 'select list' in a query. Look at my sample data below. And the data in sales table can grow till 15 rows and similarly corresponding columns in saleshist.
CREATE TABLE SALESHIST
(
PRODUCT VARCHAR2(30 BYTE),
Q1 VARCHAR2(30),
Q2 VARCHAR2(30),
Q3 VARCHAR2(30),
Q4 VARCHAR2(30)
)
[code]......
View 6 Replies
View Related
Sep 19, 2011
I am having trouble retrieving the Max, latest date, from a table with a join to one another table and other fields from both.I was able to get the MAX service_date grouped by id. But once I tried to add more fields to the query and another table it won't work.
Here is what I have:
selectMAX(cs.service_date), cs.notes, cs.applicant_id,wr.program_code,wr.last_name,wr.first_name,wr.region_code,wr.status_cd
from cs join wr on cs.applicant_id=wr.applicant_id
where wr.status_cd='AC'
group by cs.applicant_id
View 3 Replies
View Related
Nov 1, 2010
I have two table in different instance .
IMEI in instance A
RCA_SMART_CARD in instance B
Below is the desc table :
SQL> desc RCA_SMART_CARD;
Name Null? Type
----------------------------------------- -------- ----------------------------
N_CARD_ID NOT NULL NUMBER(10)
C_CARD_SERIAL_NUMBER NOT NULL VARCHAR2(20)
C_SIM_MSISDN VARCHAR2(20)
C_SIM_IMSI VARCHAR2(20)
C_LINKED_CARD VARCHAR2(20)
N_PRO_IDENTIFIER NOT NULL NUMBER(4)
C_CARD_TYPE VARCHAR2(1)
N_SIM_STATE NUMBER(1)
N_EEPROM_SPACE_LEFT NUMBER(9)
N_VOLATILE_SPACE_LEFT NUMBER(9)
N_NONVOLATILE_SPACE_LEFT NUMBER(9)
N_CARD_OPTI NOT NULL NUMBER(15)
N_PRODUCT_ID NUMBER(10)
D_CREATION_DATE DATE
D_MODIFICATION_DATE DATE
D_STATUS_MODIFICATION_DATE DATE
SQL> desc IMEI;
Name Null? Type
----------------------------------------- -------- ----------------------------
MSISDN NOT NULL VARCHAR2(20)
IMEI NOT NULL VARCHAR2(16)
DATE_MOD NUMBER(13)
IMSI VARCHAR2(18)
ICCID VARCHAR2(20)
T_PROF RAW(20)
EXTRA_DATA VARCHAR2(100)
If I want to join two table together . I want to search the number of record in IMEI that have N_SIM_STATE =1 in RCA_SMART_CARD . The MSISDN in IMEI is equal to C_SIM_MSISDN in RCA_SMART_CARD .
View 1 Replies
View Related
May 17, 2010
oracle 10g to select column from about 8 table . I start with this statement
select A.a, B.b, C.c, D.d, E.e, F.f, G.g, H.h
from A
full outer join B on(A.a=B.b)
full outer join C on(B.b=C.c)
full outer join D on(C.c=D.d_
.
.
..
.
.
.
View 12 Replies
View Related
Aug 16, 2010
two history tables with each record having effective date and end date needs to join (date is in dd/mm/yyyy)
table one
effdate enddate ID Name
01/08/2010 04/08/2010 01 devendra
04/08/2010 06/08/2010 01 deven
table two
effdate enddate ID Family
01/08/2010 02/08/2010 01 X
02/08/2010 03/08/2010 01 Y
03/08/2010 05/08/2010 01 Z
05/08/2010 06/08/2010 01 W
Expected output
effdate enddate ID Name Family
01/08/2010 02/08/2010 01 devendra X
02/08/2010 03/08/2010 01 devendra Y
03/08/2010 04/08/2010 01 devendra Z
04/08/2010 05/08/2010 01 deven Z
05/08/2010 06/08/2010 01 deven W
what can be optimum sql for this?
View 3 Replies
View Related
Apr 12, 2010
I have a table that contains 15 columns. Say each columns contains name of fruits. And another master table which contains names of valid fruits. Now I have to create another table from these tables such that for each column if the fruit name is present in the master table then fruit-name is populated else "invalid" is populated. I know using left join for 15 times to the same master table can work. But I found this very clumsy. Is there any other way of doing it.This is just a sample case. In real scenario I have a table of 800 million records and master table of 30000 records.
View 7 Replies
View Related
Mar 18, 2011
I have create a select statment using more then 10 table which is returning only two columns as per my requirement.
From this select statement I have create a View?
In order to proceed further first I would like to as If I can join a table and a view?
View 3 Replies
View Related
Nov 1, 2010
I have two table in different instance .
IMEI in instance A
RCA_SMART_CARD in instance B
Below is the desc table :
SQL> desc RCA_SMART_CARD;
Name Null? Type
----------------------------------------- -------- ----------------------------
N_CARD_ID NOT NULL NUMBER(10)
C_CARD_SERIAL_NUMBER NOT NULL VARCHAR2(20)
C_SIM_MSISDN VARCHAR2(20)
C_SIM_IMSI VARCHAR2(20)
C_LINKED_CARD VARCHAR2(20)
N_PRO_IDENTIFIER NOT NULL NUMBER(4)
C_CARD_TYPE VARCHAR2(1)
N_SIM_STATE NUMBER(1)
N_EEPROM_SPACE_LEFT NUMBER(9)
N_VOLATILE_SPACE_LEFT NUMBER(9)
N_NONVOLATILE_SPACE_LEFT NUMBER(9)
N_CARD_OPTI NOT NULL NUMBER(15)
N_PRODUCT_ID NUMBER(10)
D_CREATION_DATE DATE
D_MODIFICATION_DATE DATE
D_STATUS_MODIFICATION_DATE DATE
SQL> desc IMEI;
Name Null? Type
----------------------------------------- -------- ----------------------------
MSISDN NOT NULL VARCHAR2(20)
IMEI NOT NULL VARCHAR2(16)
DATE_MOD NUMBER(13)
IMSI VARCHAR2(18)
ICCID VARCHAR2(20)
T_PROF RAW(20)
EXTRA_DATA VARCHAR2(100)
If I want to join two table together. I want to search the number of record in IMEI that have N_SIM_STATE =1 in RCA_SMART_CARD . The MSISDN in IMEI is equal to C_SIM_MSISDN in RCA_SMART_CARD .
How can I do and what is the sql statment ??
View 5 Replies
View Related
