Re: Table cross joining problem
Date: 22 Feb 2007 03:49:51 -0800
Message-ID: <1172144991.687091.138580_at_k78g2000cwa.googlegroups.com>
On Feb 22, 12:50 am, vidhidwiv..._at_gmail.com wrote:
> Hi everyone i m facing a problem while constructing a query involving
> two table for gathering dataset.
>
> Table:
> t1(dataset number, target varchar2);
> t2(val number);
>
> below is a sub query which causes problem,
>
> select DECODE(TRUNC((dataset/3391.52)),0,0,1) from t1
> order by 1
>
> here "3391.52" is one of value present in t2 table; same way for all
> the values present in t2 we need to run same query but if i put t2 in
> from clause due to no relation between t1 & t2 tables the resultant
> cross join consists of a very large dataset.
> Please revert if any solution available.
>
> Thanks
> vidhi
What you are obtaining is a Cartesian product. If the first table contains 6 rows, and the second table contains 6 rows, Oracle will return a total of 36 rows. A quick example: CREATE TABLE T1(DATASET NUMBER(22,2), TARGET VARCHAR2(2)); CREATE TABLE T2(MY_VAL NUMBER(22,2));
INSERT INTO T1 VALUES (4000,'A'); INSERT INTO T1 VALUES (2000,'B'); INSERT INTO T1 VALUES (18000,'C'); INSERT INTO T1 VALUES (3340.8,'D'); INSERT INTO T1 VALUES (3390,'E'); INSERT INTO T1 VALUES (3392,'F'); INSERT INTO T2 VALUES (3391.52); INSERT INTO T2 VALUES (1500); INSERT INTO T2 VALUES (1600); INSERT INTO T2 VALUES (1492);
INSERT INTO T2 VALUES (3340.8);
INSERT INTO T2 VALUES (19000); Based on you SQL statement, if T1.DATASET is greater than or equal to T2.MY_VAL, then return 1, otherwise return 0. This can be expressed in at least three ways (assuming that both numbers are positive), one of which avoids a potential division by 0: SELECT
T1.TARGET, T1.DATASET, T2.MY_VAL, DECODE(TRUNC(T1.DATASET/T2.MY_VAL),0,0,1) R1,SIGN(TRUNC(T1.DATASET/T2.MY_VAL)) R2,
DECODE(SIGN(T1.DATASET-T2.MY_VAL),-1,0,1) R3 FROM
T1,
T2;
TA DATASET MY_VAL R1 R2 R3 -- ---------- ---------- ---------- ---------- ----------
A 4000 3391.52 1 1 1 A 4000 1500 1 1 1 A 4000 1600 1 1 1 A 4000 1492 1 1 1 A 4000 3340.8 1 1 1 A 4000 19000 0 0 0 B 2000 3391.52 0 0 0 B 2000 1500 1 1 1 B 2000 1600 1 1 1 B 2000 1492 1 1 1 B 2000 3340.8 0 0 0 B 2000 19000 0 0 0 C 18000 3391.52 1 1 1 C 18000 1500 1 1 1 C 18000 1600 1 1 1 C 18000 1492 1 1 1 C 18000 3340.8 1 1 1 C 18000 19000 0 0 0 D 3340.8 3391.52 0 0 0 D 3340.8 1500 1 1 1 D 3340.8 1600 1 1 1 D 3340.8 1492 1 1 1 D 3340.8 3340.8 1 1 1 D 3340.8 19000 0 0 0 E 3390 3391.52 0 0 0 E 3390 1500 1 1 1 E 3390 1600 1 1 1 E 3390 1492 1 1 1 E 3390 3340.8 1 1 1 E 3390 19000 0 0 0 F 3392 3391.52 1 1 1 F 3392 1500 1 1 1 F 3392 1600 1 1 1 F 3392 1492 1 1 1 F 3392 3340.8 1 1 1 F 3392 19000 0 0 0
36 rows selected.
If a full Cartesian product is not wanted, you need some way to retrict the input from one or both of the data sets. You did not mention Oracle version, so we will not use analytical functions to help.
Slide T2 into an inline view, and retrieve the ROWNUM for each row in T2. We can now retrict the rows returned from T2: SELECT
T1.TARGET, T1.DATASET, T2.MY_VAL, DECODE(TRUNC(T1.DATASET/T2.MY_VAL),0,0,1) R1,SIGN(TRUNC(T1.DATASET/T2.MY_VAL)) R2,
DECODE(SIGN(T1.DATASET-T2.MY_VAL),-1,0,1) R3 FROM
T1,
(SELECT
MY_VAL,
ROWNUM MY_ROW
FROM
T2) T2
WHERE
T2.MY_ROW BETWEEN 5 AND 6; TA DATASET MY_VAL R1 R2 R3 -- ---------- ---------- ---------- ---------- ----------
A 4000 3340.8 1 1 1 B 2000 3340.8 0 0 0 C 18000 3340.8 1 1 1 D 3340.8 3340.8 1 1 1 E 3390 3340.8 1 1 1 F 3392 3340.8 1 1 1 A 4000 19000 0 0 0 B 2000 19000 0 0 0 C 18000 19000 0 0 0 D 3340.8 19000 0 0 0 E 3390 19000 0 0 0 F 3392 19000 0 0 0
12 rows selected.
It is not clear whether or not this is what you are attempting.
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Thu Feb 22 2007 - 12:49:51 CET