2 Queries - 1 returning row, 1 not

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

2 Queries - 1 returning row, 1 not

Author

Topic

bluemetal
Starting Member

26 Posts

Posted - 2005-08-18 : 20:48:32

Hi Guys:

Please see the Queries below. The A returns a row, but for some records, B wont. How can i still show the records from A (with records of B left blank?). And it also sometimes says "Single Row Subquery returns more than 1 row, it runs fine sometimes!

Select a.* , b.tcount, (TCount * Premium) Prem_Paid--, (Tran_Count * Commission) Tot_Comm
from
(
Select p.policy_id,
pv.policy_number,
pv.version,
pv.start_datetime,
pv.created_date,
pv.expiry_datetime,
p.cancel_date,
pv.payfreq_code,
po.premium

From policy p, policy_version pv, policy_risk pr, policy_option po--, transaction t

Where p.policy_id = pv.policy_id
and pv.policy_id = pr.policy_id
and pv.version = pr.version
and pr.polrisk_id = po.polrisk_id

and pv.version = (select version_post_action
from policy_action
where p.policy_id = policy_id
and p.org_id = org_id
and uw_id = 10
and trunc(action_date) = trunc(to_date('09/05/2004','DD/MM/YYYY')))

and (trunc(p.cancel_date) >= trunc(to_date('09/05/2004','DD/MM/YYYY')) or p.cancel_date is null)

and pv.polstage_code = 'POLICY'
and pv.org_id = 207
and p.uw_id = 10

) A,

(

select count(t.tran_id) tcount, t.policy_id

from transaction t, policy_version pv

where pv.policy_id = t.policy_id
and pv.org_id = t.org_id
and pv.version = (select version_post_action
from policy_action
where pv.policy_id = policy_id
and pv.org_id = org_id
and uw_id = 10
and trunc(action_date) = trunc(to_date('09/05/2004','DD/MM/YYYY')))

and trunc(t.paid_date) between trunc(pv.start_datetime) and trunc(to_date('09/05/2004','DD/MM/YYYY'))
and t.tranreason_code <> 'ENDORS'
group by t.policy_id
) B
Where a.policy_id = b.policy_id
Order by a.policy_id

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-08-18 : 20:52:16

It might help to post Oracle queries on an Oracle web forum, such as:

http://dbforums.com/

SQL Team is Microsoft SQL Server only.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources