DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: sql decode statement

  1. #1
    Join Date
    Oct 2000
    Posts
    211
    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

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Oct 2000
    Posts
    211
    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

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  5. #5
    Join Date
    Oct 2000
    Posts
    211
    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


  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  7. #7
    Join Date
    Oct 2000
    Posts
    211
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width