Derived table problem - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Derived table problem

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2003-02-20 : 17:00:15

the following is the query that I am trying to run. but, It keep showing the error message says:
Server: Msg 8155, Level 16, State 2, Line 10
No column was specified for column 1 of 'a1'.

I am sure the problem comes from the SUM statement. Any help would be appreciated..

Declare @asof_date Datetime

Set @asof_date = getdate()

BEGIN

SELECT
@asof_date as asofdate,
policy_start_date, policy_expire_date, ' ' as [total claims],
Openclaims = case when clm_status = 'O' then 1
when clm_status = 'R' then 1
ELSE 0
END ,
xref_totpmt as [Total Paid],
xref_totres as [Outstanding reserve],
xref_totrec as [Total recovery],
xref_totnet as [Total Incurred],
policy_premium as premium,
EarnedPreminum = Case when @asof_date > policy_expire_date then policy_premium
when @asof_date < policy_start_date then 0
ELSE (convert( real,datediff(dd,policy_start_date,@asof_date) + 1 )
/ convert( real ,datediff(dd,policy_start_date,policy_expire_date) + 1))
* policy_premium
END,
a1.pay_amt

FROM
policy , claim, location_xref, location,clmxref,payroll, (select sum(pay_amt) from payroll group by pay_policy) as a1

WHERE
claim.ref_location = location_xref.location_key and
claim.ref_policy = policy.policy_key and
location.location_key = location_xref.ref_loc2 and
claim.claim_key = clmxref.xref_key and
policy.policy_key = a1.pay_policy and
location_xref.ref_loc1 = 56845798 and
clm_subtype <> 7

Order by policy_start_date desc

END

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-20 : 17:11:46

When you are using a derived table, I believe you have to label the derived value to be able to reference outside. SO for example:

a1.pay_amt

FROM
policy , claim, location_xref, location,clmxref,payroll, (select sum(pay_amt) from payroll group by pay_policy) as a1

You might try:

sum_pay_amt

FROM
policy , claim, location_xref, location,clmxref,payroll, (select sum(pay_amt) as sum_pay_amt from payroll group by pay_policy) as a1

Hope this helps

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources