Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2006
    Posts
    2

    Question Unanswered: a table may be outer joined to at most one other table in CASE statement

    I have a CASE statemenet that don't work, and how do i get it to work ??:

    Code:
    (CASE WHEN tabel1.column1 = tabel2.column2 THEN tabel1.column1
    WHEN SUBSTR(tabel1.column1,1,11) = tabel2.column2 THEN SUBSTR(tabel1.column1,1,11)
    WHEN SUBSTR(tabel1.column1,1,10) = tabel2.column2 THEN SUBSTR(tabel1.column1,1,10)
    WHEN SUBSTR(tabel1.column1,1,9) = tabel2.column2 THEN SUBSTR(tabel1.column1,1,9)
    WHEN SUBSTR(tabel1.column1,1,8) = tabel2.column2 THEN SUBSTR(tabel1.column1,1,8)
    WHEN SUBSTR(tabel1.column1,1,7) = tabel2.column2 THEN SUBSTR(tabel1.column1,1,7)
    WHEN SUBSTR(tabel1.column1,1,6) = tabel2.column2 THEN SUBSTR(tabel1.column1,1,6)
    WHEN SUBSTR(tabel1.column1,1,5) = tabel2.column2 THEN SUBSTR(tabel1.column1,1,5)
    WHEN SUBSTR(tabel1.column1,1,4) = tabel2.column2 THEN SUBSTR(tabel1.column1,1,4)
    WHEN SUBSTR(tabel1.column1,1,3) = tabel2.column2 THEN SUBSTR(tabel1.column1,1,3)
    WHEN SUBSTR(tabel1.column1,1,2) = tabel2.column2 THEN SUBSTR(tabel1.column1,1,2)
    WHEN SUBSTR(tabel1.column1,1,1) = tabel2.column2 THEN SUBSTR(tabel1.column1,1,1)
    ELSE NULL
    END) 
    = tabel2.column2 (+)

    If i remove the (+) in the last line the job runs perfectly exept that some records are missing, because of the (+) isn't there The error is "ORA-01417:a table may be outer joined to at most one other table", tabel2.column2 is NOT outer joined any other place in the code, but because of the test SUBSTR(tabel1.column1,1,x) = tabel2.column2 i CASE statement if just goes wrong with the error , how do u get around this ?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Try using the ANSI join syntax instead, as that doesn't have the one table restriction:
    Code:
    FROM tabel2
    LEFT OUTER JOIN tabel1 ON 
    (CASE WHEN tabel1.column1 = tabel2.column2 THEN tabel1.column1
    WHEN SUBSTR(tabel1.column1,1,11) = tabel2.column2 THEN SUBSTR(tabel1.column1,1,11)
    WHEN SUBSTR(tabel1.column1,1,10) = tabel2.column2 THEN SUBSTR(tabel1.column1,1,10)
    WHEN SUBSTR(tabel1.column1,1,9) = tabel2.column2 THEN SUBSTR(tabel1.column1,1,9)
    WHEN SUBSTR(tabel1.column1,1,8) = tabel2.column2 THEN SUBSTR(tabel1.column1,1,8)
    WHEN SUBSTR(tabel1.column1,1,7) = tabel2.column2 THEN SUBSTR(tabel1.column1,1,7)
    WHEN SUBSTR(tabel1.column1,1,6) = tabel2.column2 THEN SUBSTR(tabel1.column1,1,6)
    WHEN SUBSTR(tabel1.column1,1,5) = tabel2.column2 THEN SUBSTR(tabel1.column1,1,5)
    WHEN SUBSTR(tabel1.column1,1,4) = tabel2.column2 THEN SUBSTR(tabel1.column1,1,4)
    WHEN SUBSTR(tabel1.column1,1,3) = tabel2.column2 THEN SUBSTR(tabel1.column1,1,3)
    WHEN SUBSTR(tabel1.column1,1,2) = tabel2.column2 THEN SUBSTR(tabel1.column1,1,2)
    WHEN SUBSTR(tabel1.column1,1,1) = tabel2.column2 THEN SUBSTR(tabel1.column1,1,1)
    ELSE NULL
    END) 
    = tabel2.column2
    (That's a horrible join condition BTW!)

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking


    Or try this:
    Code:
    ...etc...
    FROM tabel2
    LEFT OUTER JOIN tabel1 ON 
     SUBSTR(tabel1.column1,1,length(tabel2.column2)) = tabel2.column2
    ...etc...


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Dec 2006
    Posts
    2
    Quote Originally Posted by andrewst
    Try using the ANSI join syntax instead, as that doesn't have the one table restriction:
    (That's a horrible join condition BTW!)
    i just started programming sql, and it have to search all the different lengths through, u could make a loop that have like i = 1 to 12 or something, but that will be another time, well to my 2 question, all this is in the WHERE part of the query, can u just make a from in the WHERE part of the query? or am i just a newbie?
    thanks andrewst

    and thanks LKBrwn_DBA for the quicker fix

    ill try it when i get back to work after christmas the 27 December

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking


    Please define "in the where part of the query".

    You are referencing two tables in the condition (tabel1 and tabel2), therefore you need some type of "JOIN".

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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