Re: How to name a subquery?

From: David Sanabria <david.sanabria_at_hartfordthe.com>
Date: Tue, 24 May 2005 22:59:13 GMT
Message-ID: <5lOke.414$0V6.348@newssvr31.news.prodigy.com>

Garfield wrote:
> I need to build a large query that uses the subquery I created. I know
> there is a way to name the subquery so that I could pull the name of
> the subquery and the constances in it. It's just nor working properly
> at the moment. Would you mind take a look and let me know?
>

[SNIP!] I'll take a stab at this...

THE CONTEXT

Assuming we are looking to join three tables:

1A. Table 1: Naughty_List (Person_ID, xmas_year, Name, Reason, Create_date)
1B. Table 2: People (Person_ID, Name, birth_date)
1C. Table 3: Good_Deeds (Person_ID, deed_date, description)

2. Santa's elves categorize presents into several categories:

    0=Coal
    1=Small Present
    2=Big Present
    3=Gigantic Present
    4=Trip to Disney World

3. If a child is on the naughty list, they get Coal (category=0).

4. For every 10 good deeds a child performs, they are "bumped" up a category, with the base category being Small Present (category=1).

5. Santa's elves have a little problem with the 2004 xmas season: They need to make sure that children (age < 18) who have been really good (> 10 good deeds during the year) get a present regardless of being on the naughty list. However, if they are on the naughty list and _do_ get a present, it will only be a "Small Present"

Given these requirements to add context to this little query, there are a number of ways to slice this up, but I want to create an example that illustrates named subqueries. Given the SQL provided by Garfield, I have striven to create an example appropriate to my perception of his skill level.

THE ANSWER (Partial)

SELECT People.People_ID                    people_id
        ,People.Name                        given_name
        ,naughty_list.name                  name_on_rap_sheet
        ,DEED_COUNT.Frequency               good_deeds
        ,decode( naughty_list.people_id
                 ,People.People_ID, 'YES'
                 ,'NO' )                    on_naughty_list
FROM    naughty_list
         ,People
         ,( SELECT People_ID,
		  COUNT(*)  Frequency
            FROM   Good_Deeds
	   WHERE  to_char( deed_date, 'yyyy' ) = '2004'
            GROUP BY People_ID ) DEED_COUNT
WHERE   people.People_ID = naughty_list.people_id (+)
	AND people.People_ID = DEED_COUNT.People_ID (+)
         AND (2005 - to_number( to_char( people.birth_date
                                         ,'yyyy')) < 18)



================================================================================

THE EXPLANATION In looking at the SQL, you can see that we performed a simple aggregation query to get the count of good deeds for the year. We gave this sub-query the name of DEED_COUNT and we also roped it into an outer join in order to figure out how many good deeds each child had done during the year.

If you are really interested in seeing who gets what, you can then take the entire SQL statement, wrap it with a couple of parentheses and give it your own sub-query name. We can call this the "Optional exercise" for our gifted audience to attempt. (Hint: decode (or CASE) <result>.good_deeds).

:) Best Regards and happy coding!

Dave

N. David Sanabria
Specialist, DA
The Hartford, Infrastructure Solutions Department Online Services Information Delivery
@: david.sanabria_at_hartfordthe.com
Don't be afraid to take a big step if one is indicated; you can't cross a chasm in two small jumps. David Lloyd George Received on Tue May 24 2005 - 17:59:13 CDT