Derive columns with multiple sql statement

[dprasanth]

Ever seen SQL like this?

Code: Select all

select 
   (select count(1) from your_attendance table yat 
    where yct.customer_key=yat.customer_key
    and some_criteria...
   ) "FIRST_METRIC",
   (select count(1) from your_attendance table yat 
    where yct.customer_key=yat.customer_key
    and some_other_criteria...
   ) "SECOND_METRIC",
   (select count(1) from your_attendance table yat 
    where yct.customer_key=yat.customer_key
    and even_more_criteria...
   ) "THIRD_METRIC",
   (select case when yat.attendance_date between '01-jul-2004' and '01-jul-2005' then yat.gross_dollars else yat.net_dollars end
    from your_attendance table yat 
    where yct.customer_key=yat.customer_key
    and even_more_criteria...
   ) "FOURTH_METRIC",
from your_customer_table yct
where customers_are_these_guys...

Ken,
How may multiple single row subqueries can we include at a time. I mean taking your example, you have FOUR single sub queries. In that way , what is the maximum sub queries we can have?

Ken
I did try whatever you have told me at the first instance. It worked perfectly fine. The above question was just out of curiosity. Now I know that there is no relevance for whatever I asked. Sorry about that.
Thanks a lot for helping me out.

[kcbland]

Ken,
How may multiple single row subqueries can we include at a time. I mean taking your example, you have FOUR single sub queries. In that way , what is the maximum sub queries we can have?

As much logic as your database SQL parser can handle. It is just SQL after all. If you have something like a 64K character limit, than your query can only be so big.

Walk yourself thru the logic. For every row in the outer query, the columns will be be derived in order from first to last. So each of those column queries will be serially executed, for every row.

That's why you write a script and launch background queries to compute each column independently into work tables, and then follow with a single query to outer-join collect all of the data. (See Michael Whitman and Dave Bolinskis Oracle OpenWorld whitepaper in 2001 about "baby-steps" scripting with work tables for building faster results than intergalactic deathstar queries).

http://oraclesvca2.oracle.com/openworld ... W12681.doc