to introduce exist in sql script

[bhenot aria]

the script below can not work and i have been encountering these problems everytime i use subquery. how can i execute this script?

select distinct a.brn_code, a.cmp_code, a.cmp_name, (select min(eff_date) from application where pay_company = a.cmp_code),
(select max(eff_date) from application where pay_company = a.cmp_code),
(select count(or_number ),sum(prm_gross )from apply_prm where datediff(m,eff_ date,prm_due)+1 <=12 and (or_number is not null or or_number <>''))
from company a left join application b on a.cmp_code = b.pay_company
join apply_prm c on c.app_number = b.app_number
where a.cmp_code like 'ssi%'
order by a.brn_code,cmp_ code

Server: Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

[deepuv04]

the script below can not work and i have been encountering these problems everytime i use subquery. how can i execute this script?

select distinct a.brn_code, a.cmp_code, a.cmp_name, (select min(eff_date) from application where pay_company = a.cmp_code),
(select max(eff_date) from application where pay_company = a.cmp_code),
(select count(or_number ),sum(prm_gross )from apply_prm where datediff(m,eff_ date,prm_due)+1 <=12 and (or_number is not null or or_number <>''))
from company a left join application b on a.cmp_code = b.pay_company
join apply_prm c on c.app_number = b.app_number
where a.cmp_code like 'ssi%'
order by a.brn_code,cmp_ code

Server: Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Hi,

the problem is witht the
select count(or_number ),sum(prm_gross )from apply_prm where datediff(m,eff_ date,prm_due)+1 <=12 and (or_number is not null or or_number <>'') statement, you can not select more than one coulmn at a time when you are using inside select query.

you use this as subquery in joining with the other tables as follows :

select distinct a.brn_code, a.cmp_code, a.cmp_name,
(select min(eff_date) from application where pay_company = a.cmp_code),
(select max(eff_date) from application where pay_company = a.cmp_code),
c.or_number ,c.prm_gross
from company a left join application b on a.cmp_code = b.pay_company
join (select app_number,coun t(or_number) as or_number, sum(prm_gross) as prm_gross from apply_prm
where datediff(m,eff_ date,prm_due)+1 <=12 and (or_number is not null or or_number <>'')
order by app_number ) as c
on c.app_number = b.app_number
where a.cmp_code like 'ssi%'
order by a.brn_code,cmp_ code

( i have no complete idea about your table structure and requirement, so make changes to your from clause and where clause accoring... )

thanks