CODEWITHSUNDEEP
sqloracle-database
Gregory Mazur
Gregory Mazur

Reputation: 2468

sql function case returns more than one row

Going to use this query as a subquery, the problem is it returns many rows of duplicates. Tried to use COUNT() instead of exists, but it still returns a multiple answer. Every table can only contain one record of superRef.

The below query I`ll use in SELECT col_a, [the CASE] From MyTable

SELECT CASE
        WHEN 
         EXISTS (SELECT 1 FROM A WHERE 
            A_superRef = myTable.sysno AND A_specAttr = 'value')
        THEN 3
        WHEN     EXISTS (SELECT 1 FROM B 
            INNER JOIN С ON С_ReferenceForB = B_sysNo WHERE C_superRef = myTable.sysno AND b_type = 2)
        THEN 2
        ELSE (SELECT C_intType FROM C 
            WHERE C_superRef = myTable.sysno)
        END
    FROM A, B, C

result:

3
3
3
3
3
3...

Upvotes: 0

Views: 1098

Answers (2)

gbtimmon
gbtimmon

Reputation: 4332

What if you did this? Because Im guessing you are getting an implicit full outer join A X B X C then running the case statement for each row in that result set. 

SELECT CASE
    WHEN 
     EXISTS (SELECT 1 FROM A WHERE 
        A_superRef = 1000001838012)
    THEN 3
    WHEN     EXISTS (SELECT 1 FROM B 
        INNER JOIN С ON С_ReferenceForB = B_sysNo AND C_superRef = 1000001838012 )
    THEN 2
    ELSE (SELECT C_type FROM C 
        WHERE C_superRef = 1000001838012)
    END
FROM ( SELECT COUNT(*) FROM A ) --This is a hack but should work in ANSI sql.
                                --Your milage my vary with different RDBMS flavors.

Upvotes: 1

Gregory Mazur
Gregory Mazur

Reputation: 2468

DUAL is what I needed, thanks to Thorsten Kettner

SELECT CASE
    WHEN 
     EXISTS (SELECT 1 FROM A WHERE 
        A_superRef = 1000001838012)
    THEN 3
    WHEN     EXISTS (SELECT 1 FROM B 
        INNER JOIN С ON С_ReferenceForB = B_sysNo AND C_superRef = 1000001838012 )
    THEN 2
    ELSE (SELECT C_type FROM C 
        WHERE C_superRef = 1000001838012)
    END
FROM DUAL

Upvotes: 0

Related Questions