Printable View
Hi guys,
The following query is not retuning any rows("0 rows selected"), while there are valid records matching the conditions. Can someone help in finding out the error?
select col1,col2,
decode(a.ittype,1,decode(a.itaddr,b.itaddr,b.servloc),
2,decode(a.itaddr,b.itaddr,b.servloc)) intended_servloc,
decode(a.ittype,3,decode(a.itaddr,c.itaddr,c.servloc),
4,decode(a.itaddr,c.itaddr,c.servloc)) actual_servloc
from a ,b,c;
Thanks
manjunath
Well, as far as I understand this query and the output you reported, at least one of your three tables (a,b,c) is empty! You are performing cartesian join, as there is no where clause. And cartesian product returns no rows only if one or more joined tables contains no rows.
Thanks jmodic.
But I want the query to run even if the table c does not contain any rows. And,does not decode take care of 'WHERE'.
Indeed, if i put some data into c , I get cartesian result which again is not much use to me
thanks
manjunath
The Decode simply allows you to change what you want to display *after* the rows have been built. To control how the rows are built, you need, as you alluded, a WHERE clause.
Specify what you are actually trying to do and maybe we can help.
- Chris
I need to make a report from tables A, B and C and format it.
If A.ittype=1 or 2, then corresponding itaddr from A should be mapped with B.itaddr. If they are same, then the corresponding B.servloc should be displayed as intended_servloc.
If A.ittype=3 or 4, then corresponding itaddr from A should be mapped with C.itaddr. If they are same, then the corresponding C.servloc should be displayed as actual_servloc.
The table A is a partitioned table with each partition having around 60 milliion records. Table B has around 1.6 million records and table C has about 100,000 records.
There are no indexes on table A because of the need of fast insertions.
Thanks
manjunath
Uh....
No indexes???
On 60 Million rows?!!??!?
Okay, it's your database, but that is rather, uh, insane :)
Okay, so we have no choice but to do a full table scan on 60 million rows. The question is how to then join to the other tables...
This query will give you what you are asking for, but I may need to think about whether or not there is a better way.
SELECT
DECODE
------(---
------A.ITTYPE---,
---------1,---(SELECT SERVLOC FROM B WHERE B.ITADDR = A.ITADDR),
---------2,---(SELECT SERVLOC FROM B WHERE B.ITADDR = A.ITADDR),
---------3,---(SELECT SERVLOC FROM C WHERE C.ITADDR = A.ITADDR),
---------4,---(SELECT SERVLOC FROM C WHERE C.ITADDR = A.ITADDR)
------)
------AS---INTENDED_SERVLOC
FROM
---A
The problem, of course, is that you will end up doing a total of 60 million correlated sub-selects, which simply can't be fast. I'll assume that you have *some* kind of restricting criteria and are not actually building a report with 60 million rows. Now, if your restricting criteria is on B or C, this solution will not work.
Here is another solution that might be better
SELECT
---DECODE
------(---
------A.ITTYPE---,
---------1,---B.SERVLOC ,
---------2,---B.SERVLOC ,
---------3,---C.SERVLOC ,
---------4,---C.SERVLOC
------)
------AS---INTENDED_SERVLOC
FROM
---A,
---B,
---C
WHERE
---B.ITADDR (+)= A.ITADDR---AND
---C.ITADDR (+)= A.ITADDR
Unfortunately, this one would need tweaking if you had restrictions on B or C again.
If you let us know your restricting criteria, perhaps we could help further.
- Chris
Thanks Chris.
I will be trying the solutions you suggested in the morning.
No, the restrictions are only on the table A and none on B or C. So, probably I will end up having a temporary table which will be populated using the restrictions on A, and then will be used with B and C in fetching the report.
About the absence of Indexes, as I said fast insertions are the criteria presently. It is felt that indexes may slow down the insertions.
However, after some testing, I intend to have some indexes
on the table A.
Thanks a lot.
manjunath