Derive columns with multiple sql statement

[dprasanth]

I have 2 tables. Customer table and the attendance table. With these two tables I need to create a new table CUSTOMER_OUT. This customer_out table has columns derived from Customer table and attendance table. I had the impression that I can run multiple select statement and can derive the value. But when I went through the Server Job Developer's Guide, it says we can run only two statements at a time. I need to derive more than 20 columns with these two table.I can give you one small example

Consider that the attendance column has
Attendance_id
Customer_id
club_id

Customer table has
Customer_id

Now I need new columns like in the CUSTOMER_OUT
Total number of visits by a client
Total number of clubs visited by a client

Can I put all these sql statements in a file and run it. For eg I have these two statements for the above columns(Total number of visits by a client, Total number of clubs visited by a client)

SELECT COUNT(DISTINCT CLUB_ID) TOTALC_3
FROM
RNKT0.ATTENDANCE ATTENDANCE
WHERE
ATTENDANCE.CUSTOMER_ID = :1
AND ATTENDANCE_DATE BETWEEN to_date('15/11/2004', 'dd/mm/yyyy') and to_date('14/02/2005','dd/mm/yyyy')

SELECT
ATTENDANCE.CUSTOMER_ID,count(ATTENDANCE.CUSTOMER_ID) count
FROM
RNKT0.ATTENDANCE ATTENDANCE
WHERE
ATTENDANCE.CUSTOMER_ID = :1
GROUP BY
ATTENDANCE.CUSTOMER_ID
ORDER BY
ATTENDANCE.CUSTOMER_ID ASC

So is there any way of deriving these columns with different sql statement in a single go. Or is there any other way of doing it?

Thanks in Advance

Regards,
Dprasanth

[kcbland]

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...

[PhilipDuPlessis]

Basically, a cartesian join would do this. By selecting from 2 tables and joining without specifying anything in the join criteria, it will return everything from both resultsets.

Example:
Select
sum(a.cust_balance) TotalCustBalances
, sum(b.acct_balance) TotalAcctBalances
from
example_custs a, example_accounts b

Good luck!

[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...

Hi,
I don't understand French err Greek Errrr Kidding .. I have never used this type of quering. But good to know that I can use this way. I will try my query and will let you know

Regards,
dprasanth

[Bala R]

That would sure work!
If you want you can break it down to individual lookups into attendance table and derive those counts for each customer id. But performance would be poor compared to Ken's suggestion.

[PhilipDuPlessis]

Point taken... Ken has it spot on in terms of the performance on the query.

[dprasanth]

Point taken... Ken has it spot on in terms of the performance on the query.

Thanks a lot for all your suggestions

[kcbland]

Well, actually, my posted query is rather poor in performance, but easy to understand.

If you want to do it at optimal speed, you would create a work table for each subselect column and simultaneously execute the queries. As independent queries, they'd be working simultaneously populating their own work table. Now you just need the driver table of customer keys and do all outer joins to the work tables to pull in the attributes.

But that's a script of queries and work tables, not a single query per the original requirement.

[PhilipDuPlessis]

To add to this, the beauty of work tables would be the ability to create indexes "on the fly" (depending naturally on RDBMS etc etc). Therefore, when performing the actual cartesian join, you could have a beast of a statement running on steriods...

[gpatton]

If you want to improve the performance of the queries without using temp tables (depending upon the DBMS being used) , you could rewrite the query that Ken proposed to put the queries into the from clause instead of the select clause.

That will improve performance on DB2 and Oracle normally.

[dprasanth]

If you want to improve the performance of the queries without using temp tables (depending upon the DBMS being used) , you could rewrite the query that Ken proposed to put the queries into the from clause instead of the select clause.

That will improve performance on DB2 and Oracle normally.

According to Ken's idea, I have put the select query as below
SELECT
(SELECT
COUNT(*) COUNT
FROM
RNKT0.ATTENDANCE ATTENDANCE
WHERE
ATTENDANCE.CUSTOMER_ID = :1
)"FIRST",
(select count(distinct(CLUB_ID)) TOTALC
FROM
RNKT0.ATTENDANCE ATTENDANCE
WHERE
ATTENDANCE.CUSTOMER_ID = :1
)"SECOND"
FROM MY_CUSTOMER_TABLE, How do I put this queriies into the from clause?

[PhilipDuPlessis]

Really easy once you get used to it....

Select
a.Count as FIRST, b.TOTALCALC as Second
from
(SELECT
COUNT(*) COUNT
FROM
RNKT0.ATTENDANCE ATTENDANCE
WHERE
ATTENDANCE.CUSTOMER_ID = :1
) a, (select count(distinct(CLUB_ID)) TOTALC
FROM
RNKT0.ATTENDANCE ATTENDANCE
WHERE
ATTENDANCE.CUSTOMER_ID = :1) b

[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?

[dprasanth]

Really easy once you get used to it....

Select
a.Count as FIRST, b.TOTALCALC as Second
from
(SELECT
COUNT(*) COUNT
FROM
RNKT0.ATTENDANCE ATTENDANCE
WHERE
ATTENDANCE.CUSTOMER_ID = :1
) a, (select count(distinct(CLUB_ID)) TOTALC
FROM
RNKT0.ATTENDANCE ATTENDANCE
WHERE
ATTENDANCE.CUSTOMER_ID = :1) b

Excellent. Thanks a ton.

Regards,
Dprasanth

[kcbland]

I don't think you understand what's going on.

There's an outer select that simply qualifies the customers you are interested in having in the output. This query is stand-alone, it will work.

Code: Select all

select yct.customer_key,
...
...
from your_customer_table yct 
where customers_are_these_guys...

Now, add a single subselect as a column to prove the point.

Code: Select all

select yct.customer_key,
   (select sysdate from dual) "NOW"
from your_customer_table yct 
where customers_are_these_guys...

You see how easy it is?

So, you need to pass any information from the outer query to the inner query so that each row discriminates for the data of interest.

Back to the original example. Do you notice the outer table (yct) is passed to the inner subselect table to limit the data for the row (see the where clause on the inner query):

Code: Select all

select 
   (select count(1) from your_attendance table yat 
    where yct.customer_key=yat.customer_key 
    and some_criteria... 
   ) "FIRST_METRIC", 
...
...
from your_customer_table yct 
where customers_are_these_guys...

Get rid of the :1, you're confusing yourself with what's going on.