SQL & PL/SQL :: Cross Join Query To Remove Repeating Combination
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
ADVERTISEMENT
Aug 3, 2011
i have two tables
Quote:
Table has following data.
id indicator
---------------
1 A
2 A
3 A
Quote:
Table2 has
id indicator
---------------
1 X
1 X
2 X
3 X
I would like to have following output ( Am running query on Toad)
t1_id t1_indicator t2_id t2_indicator
---------------------------------------------
1 A 1 X
1 X
2 A 2 X
3 A 3 X
View 4 Replies
View Related
Aug 22, 2013
Getting error ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
create table try1
(id_number varchar2(10),
item1 varchar2(10),
item2 number(10),
item3 varchar2(10))
[code]....
Table's data:
ID_NUMBERITEM1ITEM2ITEM3ROWID
1asasas12dadasdaAAA9/BAAOAAA0JtAAA
22dadad231fsfsfAAA9/BAAOAAA0JtAAB
View 7 Replies
View Related
May 31, 2013
I have a editing database with an eversions table:
NAME OWNER
---------------------- --------------------------------
WR5936_DN6676 FRED
WR6739_DN7507 FRED
WR12744_DN13994 FRED
WR6739_DN7511 BARNEY
WR6801_DN7513 BARNEY
I have a process database with a pversions table:
SOID
----------------
5936
6739
12744
6739
I need to select from the editing.eversions table all the records that do not have a matching record in process.pversion. The eversion table is text, and has some additional crap surrounding the characters I want to use for creating a join.
View 3 Replies
View Related
May 22, 2013
Oracle version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
OS: Linux Fedora Core 17 (x86_64)
I was practicing on Recursive Subquery Factoring based on oracle examples available in the documentation URL....I was working on an example which prints the hierarchy of each manager with his/her related employees. Here is how I proceed.
WITH tmptab(empId, mgrId, lvl) AS
(
SELECT employee_id, manager_id, 0 lvl
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT employee_id, manager_id, lvl+1
FROM employees, tmptab
WHERE (manager_id = empId)
[code]....
107 rows selected.
SQL> However, by accident, I noticed that if instead of putting a comma between the table names I put CROSS JOIN, the very same query behaves differently.That is, if instead of writing
UNION ALL
SELECT employee_id, manager_id, lvl+1
FROM employees, tmptab
WHERE (manager_id = empId)I write
. . .
UNION ALL
SELECT employee_id, manager_id, lvl+1
FROM employees CROSS JOIN tmptab
WHERE (manager_id = empId)I get the following error message
ERROR at line 4: ORA-32044: cycle detected while executing recursive WITH query
I remember, oracle supports both comme notation and CROSS JOIN for Cartesian product (= cross product). For example
SQL> WITH tmptab1 AS
2 (
3 SELECT 'a1' AS colval FROM DUAL UNION ALL
4 SELECT 'a2' AS colval FROM DUAL UNION ALL
5 SELECT 'a3' AS colval FROM DUAL
6 ),
[code]....
SQL> So if both comma notated and CROSS JOIN have the same semantic, why I get a cycle for the above mentioned recursive subquery factoring whereas the very same query works pretty well with comma between the table names instead of CROSS JOIN? Because if a cycle is detected (ancestor = current element) this means that the product with CROSS JOIN notation is generating some duplicates which are absent in the result of the comma notated Cartesian product.
View 9 Replies
View Related
Mar 21, 2012
I have a employee table with the below format
EMP
EMPID DEPID
1A
2A
2B
1B
4A
5B
5A
6C
7A
8D
Now I need to start with the employee 1 . As we can see the emp 1 is mapped to dept A and also with multiple dept. Similarly dept id is mapped to multiple emp.
I need the output as below (many to many I.e. cross referencing)
EMPID DEPIDGroup
1A A1
2A A1
2B A1
1B A1
4A A1
5B A1
5A A1
6C B1 -- new group as the emp and dept are not mapped previously
7A A1
8D C1
View 1 Replies
View Related
May 6, 2010
I write a Query to get Deptno wise Employee Names as fallows..
SQL> select max(decode(d.deptno,10,e.ename)) dept10,
2 max(decode(d.deptno,20,e.ename)) dept20,
3 max(decode(d.deptno,30,e.ename)) dept30
4 from emp e,
5 dept d
6 where e.deptno = d.deptno
[code]....
But i don't want to include the blank(null) spaces in the query output.
The Required output should be like this..
DEPT10 DEPT20 DEPT30
------ ------ ------
CLARKADAMSALLEN
KINGFORDBLAKE
MILLERJONESJAMES
SCOTTMARTIN
SMITH TURNER
WARD
View 5 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
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
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
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
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
Jan 7, 2011
I have a requirement in this format.
These are the scripts for the 2 input tables
CREATE TABLE TMP_split
AS
SELECT 1000 AS grp_id, 'abc' AS ATTRIB, 60 AS PCT FROM DUAL
UNION ALL
SELECT 1000 AS grp_id, 'pqr' AS ATTRIB, 40 AS PCT FROM DUAL;
[Code].....
The output needs to be in this format
key | val | grp_id | pct
------------------------------------------
6~7~8 |DIM_11~DIM21~DIM31 | 1000 | 60
6~7~8 |DIM_11~DIM21~DIM31 | 1000 | 40
6~7~8 |DIM_11~DIM22~DIM31 | 1000 | 60
6~7~8 |DIM_11~DIM22~DIM31 | 1000 | 40
6~7~8 |DIM_11~DIM23~DIM31 | 1000 | 60
6~7~8 |DIM_11~DIM23~DIM31 | 1000 | 40
Is there a good way to dynamically obtain this output either by PL/SQL or by query?
This is a case where combination needs to be used. However, I am not able to arrive at the right algorithm for this
View 6 Replies
View Related
Aug 28, 2007
I need to create a report that show the overall performance of the system in hourly basic. I need to check all the transaction table in the system. So I try to find Cross Tab query. I need display hourly basic in column. From 00 hour to 23 hour.
In my table i will have a sysdate that track all the record DD/MM/YYYY HH24:Mi, so now i need so split it to a hourly in column. This is how it will look like. Think show in picture is easier.
Each row actually retrieve from a table. So basically i need to count each table, on that day got how many transactions then split all to a hourly basic and display it.
View 5 Replies
View Related
Apr 27, 2011
I'm trying to write a cross tab query but which groups by periods.
Test Case: -
CREATE TABLE COMPANIES
(
"ID" NUMBER(9,0),
"CODE" NUMBER(8,0),
"DESCRIPTION" VARCHAR2(40 CHAR),
CONSTRAINT "PK_COMPANIES" PRIMARY KEY ("ID")
);
[Code]..
The cross tab is to have columns 1 - 31 which sum the quantity of the article sold. Represnting days of the month.
In addition the description of the company.
I would then like to end up with 3 entries in the results for each company, but these are for summing the quantity of articles by the following periods: -
Breakfast: 01:00 - 10:00
Lunch: 11:00 - 14:00
Dinner: 17:00 - 22:30
I hope that makes sense?
View 13 Replies
View Related