SQL select AS command - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

SQL select AS command

Author

Topic

cherman
Starting Member

15 Posts

Posted - 2011-05-04 : 16:25:26

I'm using the following subquery. This subquery works great within one query, but when I add the exact same logic to a new query I get an error message saying, "Incorrect syntax near the keyword 'as'."

My subquery is:
(select bk.target_amt from cs_fund_broker bk where oa.exec_broker = bk.acct_cd)as target_amt

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-04 : 16:29:57

Show us the whole query that is failing.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

cherman
Starting Member

15 Posts

Posted - 2011-05-04 : 16:30:59

select 1 sort,
b.bkr_cd,
b.bkr_name,
o.prin_local_crrncy,
c.crrncy_name,
o.net_trade_ind,
o.broker_reason,
(select cod.desc_text
from csm_code cod
where cod.desc_type='BKRRNE'
and cod.desc_cd=o.broker_reason)broker_reason_desc,
sum(oa.commision_amt)comm_amt,
case when o.broker_reason is null then
(select sum(oa1.commision_amt)
from ts_order o1,
ts_order_alloc oa1
where oa1.order_id=o1.order_id
and o1.settle_date>=dateadd(year,
datediff(year,
'1900-01-01',
getdate()),
'1900-01-01')
--and o1.settle_date'Y'
or o1.ipo is null)
else
(select sum(oa1.commision_amt)
from ts_order o1,
ts_order_alloc oa1
where oa1.order_id=o1.order_id
and o1.settle_date>=dateadd(year,
datediff(year,
'1900-01-01',
getdate()),
'1900-01-01')
--and o1.settle_date'Y'
or o1.ipo is null)end bkr_prin_ytd,
0 bkr_rea_prin_ytd,
0 bkr_rea_agcy_ytd,
b.bkr_typ_cd,
b.credit_ratio,
b.credit_bkr_cd,
oa.bkr_of_credit,
oa.exec_broker,
--bk.target_amt,
oa.step_out_broker,
'' exec_or_credit,
'ob' row_type
from ts_order o,
ts_order_alloc oa,
cs_broker b,
--cs_fund_broker bk,
csm_currency c
where oa.order_id=o.order_id
--and o.exec_broker=bk.acct_cd
and((oa.exec_broker=b.bkr_cd
and(oa.step_out_broker is null
or oa.step_out_broker=''))
or(oa.step_out_broker=b.bkr_cd))
and(oa.bkr_of_credit is null)
and o.prin_local_crrncy=c.crrncy_cd
and(o.reason_cd not in('IPO', 'PP', 'SO')
or o.reason_cd is null)
and(o.ipo<>'Y'
or o.ipo is null)
and((o.status in('ACCT')
or(o.status in('ACCT')
and 'Y'='N'))
and o.trade_date>='2011-01-01'
and o.trade_date<='2011-01-31'
and b.bkr_cd in(select blm.bkr_cd from cs_broker_list_member blm where blm.bkr_list_cd='EQUITY')
and 1=1)
(select bk.target_amt from cs_fund_broker bk where oa.exec_broker = bk.acct_cd)as target_amt
group by b.bkr_cd,
b.bkr_name,
o.prin_local_crrncy,
c.crrncy_name,
o.broker_reason,
o.net_trade_ind,
b.bkr_typ_cd,
b.credit_ratio,
b.credit_bkr_cd,
oa.bkr_of_credit,
oa.exec_broker,
oa.step_out_broker
--bk.target_amt
union all
select 2 sort,
b.bkr_cd,
b.bkr_name,
o.prin_local_crrncy,
c.crrncy_name,
o.net_trade_ind,
o.broker_reason,
(select cod.desc_text
from csm_code cod
where cod.desc_type='BKRRNE'
and cod.desc_cd=o.broker_reason)broker_reason_desc,
sum(oa.commision_amt)comm_amt,
case when o.broker_reason is null then
(select sum(oa1.commision_amt)
from ts_order o1,
ts_order_alloc oa1
where oa1.order_id=o1.order_id
and o1.settle_date>=dateadd(year,
datediff(year,
'1900-01-01',
getdate()),
'1900-01-01')
--and o1.settle_date'Y'
or o1.ipo is null)
else
(select sum(oa1.commision_amt)
from ts_order o1,
ts_order_alloc oa1
where oa1.order_id=o1.order_id
and o1.settle_date>=dateadd(year,
datediff(year,
'1900-01-01',
getdate()),
'1900-01-01')
--and o1.settle_date'Y'
or o1.ipo is null)end bkr_prin_ytd,
0 bkr_rea_prin_ytd,
0 bkr_rea_agcy_ytd,
b.bkr_typ_cd,
b.credit_ratio,
b.credit_bkr_cd,
oa.bkr_of_credit,
oa.exec_broker,
--bk.target_amt,
oa.step_out_broker,
'FromExec' exec_or_credit,
'oa' row_type

from ts_order o,
ts_order_alloc oa,
cs_broker b,
--cs_fund_broker bk,
csm_currency c
where oa.order_id=o.order_id
--and o.exec_broker=bk.acct_cd
and((oa.exec_broker=b.bkr_cd
and(oa.step_out_broker is null
or oa.step_out_broker=''))
or(oa.step_out_broker=b.bkr_cd))
and(oa.bkr_of_credit is not null)
and o.prin_local_crrncy=c.crrncy_cd
and(o.reason_cd not in('IPO', 'PP', 'SO')
or o.reason_cd is null)
and(o.ipo<>'Y'
or o.ipo is null)
and((o.status in('ACCT')
--or(o.status in('ACCT', 'READY')
and 'Y'='N')
and o.trade_date>='2011-01-01'
and o.trade_date<='2011-01-31'
and b.bkr_cd in(select blm.bkr_cd from cs_broker_list_member blm where blm.bkr_list_cd='EQUITY')
and 1=1)

group by b.bkr_cd,
b.bkr_name,
o.prin_local_crrncy,
c.crrncy_name,
o.broker_reason,
o.net_trade_ind,
b.bkr_typ_cd,
b.credit_ratio,
b.credit_bkr_cd,
oa.bkr_of_credit,
oa.exec_broker,
oa.step_out_broker
--bk.target_amt

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-04 : 16:33:06

Could you please add formatting to your query so that we can read it? And please color code the problem area.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

cherman
Starting Member

15 Posts

Posted - 2011-05-04 : 16:35:13

What do you mean by formatting the query?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources