Home » SQL & PL/SQL » SQL & PL/SQL » query on a result of another query
query on a result of another query [message #267452] |
Thu, 13 September 2007 05:45  |
sonalshastry
Messages: 52 Registered: September 2007
|
Member |
|
|
Hi
SELECT ent_id,ent_name, SUM(BROKERAGE1) Brokerage ,DT , ent_address_line_1,ent_address_line_2,ent_address_line_3,
ent_address_line_4,ent_address_line_5,ent_address_line_6,ent_address_line_7
FROM
(
select distinct e.ent_id, e.ent_name, to_CHAR(cm.cnm_date,'MONYYYY') DT,
sum(cm.cnm_brokerage_amt) Brokerage1,
e.ent_address_line_1,e.ent_address_line_2,e.ent_address_line_3,
e.ent_address_line_4,e.ent_address_line_5,e.ent_address_line_6,e.ent_address_line_7
from contract_note_master cm, entity_master e
where e.ent_id = cm.cnm_ent_id
and cm.cnm_date >= '01-april-2007' and cm.cnm_date <= '31-Jun-2007'
and cm.cnm_sd_stage <> 'C'
and e.ent_status = 'E'
group by e.ent_id,(to_CHAR(cm.cnm_date,'MONYYYY')), e.ent_name,e.ent_address_line_1,e.ent_address_line_2,
e.ent_address_line_3,e.ent_address_line_4,e.ent_address_line_5,e.ent_address_line_6,e.ent_address_line_7
Union all
select distinct e.ent_id, e.ent_name, to_CHAR(dcn_date,'MONYYYY')DT,
sum(dm.dcn_brokerage_amt) Brokerage1,
e.ent_address_line_1,e.ent_address_line_2,e.ent_address_line_3,
e.ent_address_line_4,e.ent_address_line_5,e.ent_address_line_6,e.ent_address_line_7
from dtm_contract_note_master dm, entity_master e
where dm.dcn_ent_id = e.ent_id
and dcn_date >= '01-april-2007' and dcn_date <= '31-Jun-2007'
and dm.dcn_stage <> 'C'
and e.ent_status = 'E'
group by e.ent_id,(to_CHAR(dcn_date,'MONYYYY')), e.ent_name,e.ent_address_line_1,e.ent_address_line_2,
e.ent_address_line_3,e.ent_address_line_4,e.ent_address_line_5,e.ent_address_line_6,e.ent_address_line_7
)
group by ent_id, ent_name, DT,ent_address_line_1, ent_address_line_2,ent_address_line_3,
ent_address_line_4,ent_address_line_5,ent_address_line_6, ent_address_line_7
having SUM(BROKERAGE1)>='2000'
the above query gives me all the clients who gives me brokerage of 2000 or more below is the few records of result
ENT_ID ENT_NAME BROKERAGE DT ENT_ADDRESS_LINE_1
1 2174 SUNDAR 7631.36 JUN2007 MODEL TOWN
2 2174 SUNDAR 7240.1 MAY2007 MODEL TOWN
3 2330 NANDKISHOR 13899.0 JUN2007 ALI CHAMBERS
4 2330 NANDKISHOR 13159.0 MAY2007 ALI CHAMBERS
5 2330 NANDKISHOR 11299.0 APR2007 ALI CHAMBERS
but i want to add another condition that will gives me only those clients who gives me brokerage of 2000 or more for every month. like only recode of ENT_ID 2330 which is giving me brokerage in every month
please help me
sonal
[Updated on: Thu, 13 September 2007 07:14] Report message to a moderator
|
|
|
|
Re: query on a result of another query [message #267547 is a reply to message #267452] |
Thu, 13 September 2007 12:25   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
sonalshastry wrote on Thu, 13 September 2007 06:45 |
and cm.cnm_date >= '01-april-2007' and cm.cnm_date <= '31-Jun-2007'
and dcn_date >= '01-april-2007' and dcn_date <= '31-Jun-2007'
|
Invalid query. You cannot compare a DATE column to a character string. Please read Oracle documentation or search here or Google for the TO_DATE function.
|
|
|
|
Goto Forum:
Current Time: Sat Feb 15 13:53:53 CST 2025
|