PL/SQL :: Difference Between CROSS JOIN And Comma-notated Cartesian Product?

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


ADVERTISEMENT

SQL & PL/SQL :: Use Cartesian Product?

Mar 7, 2013

When to use Cartecian product???? is there any special cases to use cartecian product?

View 2 Replies View Related

SQL & PL/SQL :: Replicating Records With Cartesian Join

Dec 24, 2011

Table script:

CREATE TABLE TEST_ITEM_BU_ID
(
CCN VARCHAR2(100 CHAR),
SKU VARCHAR2(100 CHAR),
BU_ID NUMBER
)

select * from test_item_bu_id;

CCN SKU BU_ID
------------------------------
M10000616-10502414545
M10000600-11437414545
M10000205-113380
M10000205-113390
M10000600-114370

The requirement is to replicate the bu_id records with bu_id=0 as bu_id=414545 ( there is no lookup available) so the same table should act as a lookup table to populate bu_id for the records where bu_id=0

i.e., here it will replicate for the sku set with bu_id value=0

M10000205-113380
M10000205-113390
M10000600-114370

will be replicated against

M10000616-10502414545M10000600-11437414545

so the output should be :

CCN SKU BU_ID
------------------------------
M10000205-11338414545
M10000205-11338414545
M10000205-11339414545
M10000205-11339414545

The below query is supposed to do this.

select a.ccn,b.bu_id,a.sku,b.sku
from test_item_bu_id a ,
( select distinct ccn,sku_num, bu_id
from test_item_bu_id
where bu_id in (414545) and CCN in ('M10000') ) b
where a.bu_id = 0 and a.sku <> b.sku and a.ccn= b.ccn

But we have wrong result here.

CCN BU_ID SKU SKU_1
----------------------------------------------
M10000414545205-11338600-11437
M10000414545205-11338616-10502
M10000414545205-11339600-11437
M10000414545205-11339616-10502
M10000414545600-11437616-10502

How can we avoid the last record, i.e., SKU=600-11437 since it is already having bu_id no need to replicate it, but it is getting replicated since the extra record with bu_id=0 exist for the same sku.

View 1 Replies View Related

PL/SQL :: Avoid Cartesian Join In Query?

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

Performance Tuning :: Select Distinct From Cartesian Join

Sep 12, 2011

Having production system: 11.2.0.1 on Windows Server x64
Test system: 9.2.0.1 on Windows XP

Problem preface: to get all unique CASEID which should be checked up by biometric system.What i should check - all CASEs for different PERSONs having same PHONEs at least among one phone type (1..4).Real table contains little bit more than 10 million records.I made test scripts.

Below the DDL for test table creation:
------------------------------------------
-- Create CASEINFO test table
------------------------------------------
DROP TABLE CASEINFO;
CREATE TABLE CASEINFO

[code]...

Below i've put SQL/DLL to make test data.number of records inserted 2 millions.
PERSON_COUNT := #/8;
------------------------------------------
-- fill CASEINFO with sample data
------------------------------------------
DECLARE
I INTEGER;

[code]...

Below SQL select to check the data in created table.
------------------------------------------
-- Check test data counters
------------------------------------------
SELECT 'TOTAL',count(*) from CASEINFO
UNION ALL
SELECT 'LEGAL',count(*) from CASEINFO where

[code]...

The PROBLEM is that i am experiencing HUGE perfomance problems on both test and production systems with that query:

select distinct b.caseid
from CASEINFO a, CASEINFO b
where (a.person<>b.person) and (a.sex=b.sex) and
(
(a.phone1=b.phone1) or
(a.phone1=b.phone2) or
(a.phone1=b.phone3) or

[code]...

This query takes almost 90 minutes to execute.And i do not know how to avoid this.Full SQL file to make test attached.

View 13 Replies View Related

SQL & PL/SQL :: Columns To Rows Using Case And Cross Join?

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

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

SQL & PL/SQL :: Cross Database Join On Fields With Mismatched Data Types

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

SQL & PL/SQL :: Difference Between Equi And Inner Join

Aug 22, 2005

What are the main differences between an Equi Join and an Inner Join ?

View -1 Replies View Related

PL/SQL :: Difference - In Conditions (Join And Where Clause)

Sep 19, 2013

I need to be clear about what exactly difference when we put any condition in INNER JOIN and WHERE Clause. I tried both way and found same results. Even in Statistics Plan not much differences. 

1. Here I am using location filter in Inner join condition -

"SELECT I.*, Gl * From Sc1.Item I   Inner Join Sc1.Part P  On P.Part_Id = I.Part_Id       Inner Join Sc1.Location Gl  On Gl.Location_Id = I.Location_Id   And Gl.Location_Id In ( 1767, 1747,202,1625)    Inner Join Sc1.Condition C On C.Condtion_Id = Gl.Condition_Id Where  I.Inactive_Ind = 0  And I.Condition_Id != 325         

2. Here I am using location filter in Where clause

SELECT I.*, Gl * From Sc1.Item I   Inner Join Sc1.Part P  On P.Part_Id = I.Part_Id       Inner Join Sc1.Location Gl   On Gl.Location_Id = I.Location_Id   Inner Join Sc1.Condition C        On C.Condtion_Id = Gl.Condition_Id Where  I.Inactive_Ind = 0       and I.LOCATION_ID in ( 1767, 1747,202,1625)    And I.Condition_Id != 325.

View 23 Replies View Related

SQL & PL/SQL :: Difference Between MINUS And LEFT Outer Join?

Jul 27, 2012

What is the fundamental difference between MINUS keyword and LEFT outer join in Oracle.

I can achieve same results using either one of them.

View 8 Replies View Related

SQL & PL/SQL :: Cartesian Selects After Update From 10.0.2.0.3 To 10.0.2.0.5?

Jul 27, 2010

we just installed the patch 10.0.2.0.5 on a 10.0.2.0.3 database and some selects didn't work as before. While changing the select clause, there are different counts.

There are 3 tables:

Detail_1 => MASTER_1 <= Detail_2

MASTER_1 has a primary and an unique Key. The 2 detail tables have FK. Now when selecting only columns from the detail tables (joined with master) we get cartesian selects. If i use one column of the master table in the select clause everything is fine.

Here is an example:

CREATE TABLE MASTER_1
( "KUNID" NUMBER NOT NULL ENABLE,
"CUSTOMER_NO" VARCHAR2(20 BYTE),
CONSTRAINT "PK_MASTER1" PRIMARY KEY ("KUNID"),
CONSTRAINT "UK_MASTER1" UNIQUE ("CUSTOMER_NO"));

[code].......

So you see, we get different rows only by changing the select clause.

Note: It seemed to be important to have both FK. When deleting one of them - or both - we get the same correct results: 3 rows with any select clause.

View 10 Replies View Related

Creating A Cross Tab Report

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

SQL & PL/SQL :: Cross Tab Group By Period?

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

Client Tools :: View Red Cross

Aug 29, 2013

We have a materialized view showing red cross in oralce sql developer. But it functions well, we can select on it, no errors.

I searched on web, and some tells if underlying table changed, it may be broken. and need to refresh.

So I think I need to refresh it, using alter materialized view ... compile, but it told me the table or view doesnot exist, but it exists.

View 21 Replies View Related

PL/SQL :: Cross Environment Table Access

Mar 13, 2013

I am working in all 3 environments: development , Test and Production. How can we access the tables created in one environment in the other environment. Also, request to share the SQL code for the same.

I am using Oracle Sql Developer tool.

View 4 Replies View Related

Cross Platform Restore - Solaris - Linux?

Aug 4, 2011

In my present env, Oracle runs in Solaris 10 and I am planning to restore it to Linux5. I have read thro oracle docs, metalink that cross platform restore with different endian can be done by TTS. But it says that tablespace shud be in read-only until we plug in it in the dest server.

Also My db size will be 800-1000GB

View 3 Replies View Related

SQL & PL/SQL :: Cross Referencing Query From A Single Table

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

Reports & Discoverer :: Down Cross Printing Direction?

Jul 10, 2013

how to display data in tabular format but the printing direction is down/cross? I managed to change set printing direction but the column header is not showing on the cross section.

I expect:

Header1 Header 2 Header1 Header2
John India Max Singapore

But it displays without header name on the next part.

Header1 Header 2
John India Max Singapore

And also is that possible for matrix query to be printed in down/cross direction like above?

View 1 Replies View Related

How To Create Cross-tab / Matrix Style Output In Forms

Sep 16, 2011

I am essentially trying to reproduce an extremely complex spread-sheet with an engineering mathematical model in it and that has been in use for 5 years now. The Excel spreadsheet was a good environment for tuning the model, but now I have been asked to design an Oracle Form that incorporates their mathematical model and shows their final and intermediate data so they can validate my app against their proven spreadsheet.

To do that I have to create a matrix style table for use as a child data-block in my Oracle Form.

View 4 Replies View Related

10g RAC HP-UX To AIX Cross Platform Migration Along With Raw File System To ASM Conversion?

May 7, 2011

We got a request from Customer to migrate a RAC database of size 1.8TB from HP unix raw file system to AIX ASM with a min downtime. I have seen lot of methods of doing cross plat form migration and non-ASM to ASM but not together.

Do we have any proven method for migration such cross platform migration with raw file system to ASM conversion in a single go with min downtime?

View 1 Replies View Related

Application Express :: Cross Form Updates For Searched Record?

Aug 30, 2012

How to update a form fields which taking a searched value from another table .

I have a form that records student data and which has a drop down to select the 'student registration No' that comes from different table.

When insert or update a student record, first we select the student registration No from drop down to see if he is an existing student.

If he is already registered student then relevant student name and other details should be shown in the form and should be able to enter few other details as well.

View 3 Replies View Related

SQL & PL/SQL :: Product Of Values In The Column?

Jul 29, 2010

how to find the product of the values in the column.. assume that the column has number(2) as its data type..

as we use sum(colname) for the sum of the values in the column there s no function to find the product.

View 3 Replies View Related

SQL & PL/SQL :: SUM Product By DATE By LOCATION

Dec 14, 2011

I am trying to query a Oracle database table that contains sample records like the one below...

DATE LOCN PROD1 PROD2
09/12/2011 L1 6 2
09/12/2011 L2 3 7
10/12/2011 L1 4 1
10/12/2011 L2 3 3
11/12/2011 L1 2 2
11/12/2011 L2 2 0
12/12/2011 L1 4 1
12/12/2011 L2 5 0

I am trying to use the Oracle Sum() to get a grouping by DATE, LOCATION, SUM(PROD1+PROD2) for DATE periods 10/12/2011 and 11/12/2011. Below is the desired end result.

DATE LOC1 LOC2
10/12/2011 5 6
11/12/2011 4 2

View 1 Replies View Related

SQL & PL/SQL :: Calculates Value Of Product Sold

Nov 25, 2011

Stored Procedure: Receives the MFR and PRODUCT identifying the product sold as well as the Quantity purchased by the customer.

 Calculates the value of the product sold (looks up the cost of the product in the products table and multiplies by the quantity sold)
 Returns the calculated value.

View 12 Replies View Related

PL/SQL :: Add Four More Column And Group By PRODUCT

Sep 28, 2012

I am new to PL/SQL environment.. ( USING PL/SQL Oracle)

SELECT TABLE1.PRODUCTID, TABLE1.TYPE , TABLE1.PRODUCT, TABLE2.#SOLD
FROM TR.TABLE1
INNER JOIN
PR.TABLE2
ON (TR. PRODUCTID = PRODUCTID )

Query result.

Product ID     Product          Type          #Sold
1     Fruit          Banana          2
2          Food          Rice          4
3          Food          Sugar          6
4          Fruit          Orange          2
5          Fruit          Apple          10
6          Food          Sugar          12
7          Food          Corn          3
8          Fruit          Apple          5
9          Fruit          Banana          2
10          Food          Sugar          1
11          Fruit          Banana          5
12          Food          Sugar          7

But would like to add Four More Column and group by by PRODUCT.Final Result will look like :

     {Product}     ,     {Type}     ,     {#Sold}     ,     0 to 3     ,     4 to 9     ,     >10          Total Sold     
          ,     {Corn}     ,     3     ,          ,          ,                    
          ,     {Rice}     ,          ,     4     ,          ,                    
          ,     {Sugar}     ,          ,          ,          ,     26               
     {Food}     ,          ,          ,          ,          ,               33     
          ,     {Apple}     ,          ,          ,          ,     15               
          ,     {Banana}     ,          ,          ,     9     ,                    
          ,     {Orange}     ,          ,     2     ,          ,                    
     {Fruit}     ,          ,          ,          ,          ,               36     }

View 9 Replies View Related

Getting Query To Display Only 150 And 162 As They Have Purchased Only One Product?

Jun 24, 2011

Is there a way to find customers purchased only single product from the following table?

cusno Product Date
----- ------ ----
121 ES 03/12
121 NT 30/12
131 ST 03/12
13 WT 04/12
150 ES 05/12
150 ES 06/12
150 ES 07/12
160 MN 05/12
160 ES 06/12
160 ES 07/12
162 NT 08/12

I need a query to display only 150 and 162 as they have purchased only one product.

View 2 Replies View Related

SQL & PL/SQL :: List Most Popular Product For Every State?

Feb 11, 2011

so here is the query : For every state, list the most popular product. Here is the query I have so far :

SELECT C.State, P.Product_Name, Count(*) cnt FROM Product P, Customer C, OrderTable O, LineItem L WHERE C.CID = O.CID AND O.OID = L.OID AND L.PID = P.PID GROUP BY C.State, P.Product_Name;

Result :
STATE PRODUCT_NAME COUNT(*)
---------- -------------------- ----------
New Jersey Computer 3
Texas Computer 1
New Jersey Speaker 2

I would need the result to only say New Jersey Computer and Texas Computer because I only want a list of the states with the product name that is sold the most in each state.All I need to do is have the query only select the product name with the max count for each state...

View 4 Replies View Related

Windows :: Oracle 10g Installation Asking For Product.xml

Jun 18, 2013

I have oracle10g installable in .rar format. I have unzipped it and started installer through setup.exe file, but in the secnd step itself it is asking product.xml file location , i searched entire installables but could not find any thing with such name, installing this oracle 10g on windows 2003 server.

View 12 Replies View Related

SQL & PL/SQL :: Return Any Product That Doesn't Have TEST_UOM_GROUP

Jan 13, 2011

I have the following tables: -

CREATE TABLEtest_articles
(idNUMBER(9),
code VARCHAR2(20),
description VARCHAR2(40),
uom_group_id NUMBER(9),
in_exchange NUMBER(1) DEFAULT 0);
[code]....

However, I'd also like it to return any product that doesn't have a TEST_UOM_GROUP, for example the 'Bud'.I've hit a brick wall and just keep going around in circles and not acheiving the result I'm after!how to either change the SQL Query.

View 22 Replies View Related







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