CODEWITHSUNDEEP
sqloracle-databaseselectsubquery
James Young
James Young

Reputation: 1432

Is there a better way to write this SQL SELECT statement than using a subquery?

I have this queury:

SELECT A, B, C
FROM (  SELECT 
        lla.id as A,
        max(decode(lla.attrid, 2, lla.valstr, null)) as B, 
        max(decode(lla.attrid, 3, lla.valstr, null)) as C
    FROM 
        llattrdata lla, 
        llattrdata lla2
    WHERE 
        lla.id = lla2.id 
    GROUP BY lla.id)
WHERE C = "Yes"

Is there a better way to do this? I tried to use a HAVING clause, but couldn't get it to work against column C="Yes". Thanks in advance.

Upvotes: 2

Views: 435

Answers (4)

DazzaL
DazzaL

Reputation: 21973

I will let you into a secret. there's nothing wrong with what you have. so don't feed the need to rewrite it.

SELECT A, B, C
FROM (  SELECT 
        lla.id as A,
        max(decode(lla.attrid, 2, lla.valstr, null)) as B, 
        max(decode(lla.attrid, 3, lla.valstr, null)) as C
    FROM 
        llattrdata lla, 
        llattrdata lla2
    WHERE 
        lla.id = lla2.id 
    GROUP BY lla.id)
WHERE C = 'Yes'

is the same performance as :

SELECT 
    lla.id as A,
    max(decode(lla.attrid, 2, lla.valstr, null)) as B, 
    max(decode(lla.attrid, 3, lla.valstr, null)) as C
FROM 
    llattrdata lla, 
    llattrdata lla2
WHERE 
    lla.id = lla2.id 
GROUP BY lla.id
HAVING max(decode(lla.attrid, 3, lla.valstr, null)) = 'Yes';

you will gain no performance by avoiding the inline view here. whichever is more readable to you, go with that.

Upvotes: 3

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

Use the HAVING clause:

SELECT 
    lla.id as A,
    max(decode(lla.attrid, 2, lla.valstr, null)) as B, 
    max(decode(lla.attrid, 3, lla.valstr, null)) as C
FROM 
    llattrdata lla, 
    llattrdata lla2
WHERE 
    lla.id = lla2.id 
GROUP BY lla.id
HAVING max(decode(lla.attrid, 3, lla.valstr, null)) = 'Yes'

This clause is evaluated after the GROUP BY and is used to filter on the grouped results.

Unfortunately you can't use aliases in the having clause. In some cases the subquery may be clearer in intent than the having clause.

Upvotes: 1

Marc
Marc

Reputation: 16512

How about

SELECT 
        lla.id as A,
        max(decode(lla.attrid, 2, lla.valstr, null)) as B, 
        'Yes' as C
    FROM 
        llattrdata lla, 
        llattrdata lla2
    WHERE 
        lla.id = lla2.id 
    GROUP BY lla.id
    HAVING max(decode(lla.attrid, 3, lla.valstr, null)) = 'Yes'

You can't use an alias that you created in the same query. You have to write it again

Upvotes: 2

jazzytomato
jazzytomato

Reputation: 7214

SELECT 
        lla.id as A,
        max(decode(lla.attrid, 2, lla.valstr, null)) as B, 
        max(decode(lla.attrid, 3, lla.valstr, null)) as C
    FROM 
        llattrdata lla, 
        llattrdata lla2
    WHERE 
        lla.id = lla2.id 
    GROUP BY lla.id
    HAVING max(decode(lla.attrid, 3, lla.valstr, null)) = "Yes"

Please read this : Why can't I use alias in a count(*) "column" and reference it in a having clause?

Upvotes: 2

Related Questions