Derive columns with multiple sql statement
Moderators: chulett, rschirm, roy
Derive columns with multiple sql statement
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
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
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...
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 23
- Joined: Thu Oct 27, 2005 12:51 am
Cartesian Joins
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!
Example:
Select
sum(a.cust_balance) TotalCustBalances
, sum(b.acct_balance) TotalAcctBalances
from
example_custs a, example_accounts b
Good luck!
Hi,kcbland wrote: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...
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
-
- Participant
- Posts: 23
- Joined: Thu Oct 27, 2005 12:51 am
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.
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 23
- Joined: Thu Oct 27, 2005 12:51 am
According to Ken's idea, I have put the select query as belowgpatton wrote: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.
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?
-
- Participant
- Posts: 23
- Joined: Thu Oct 27, 2005 12:51 am
Ken,kcbland wrote: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...
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?
Excellent. Thanks a ton.PhilipDuPlessis wrote: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
Regards,
Dprasanth
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.
Now, add a single subselect as a column to prove the point.
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):
Get rid of the :1, you're confusing yourself with 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...
Code: Select all
select yct.customer_key,
(select sysdate from dual) "NOW"
from your_customer_table yct
where customers_are_these_guys...
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...
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle