Prev: Table Design
Next: Output padded result
From: sike11 via SQLMonster.com on 5 Aug 2010 10:24
I got his error message on this scritp which I do not understand:

select Top 1500
p.master_customer_id as parent, c.master_customer_id, c.label_name,
sum(c.usr_pbuntsmngmnt+c.usr_rhuntsmngmnt+c.usr_bsuntsmngmnt+c.
usr_othuntsmngmnt+c.usr_lchountsmngmnt + c.usr_genbedmngmnt + c.
usr_rescaremngmnt + c.usr_shelteredmngmnt + c.usr_supportedmngmnt) as
TotalUnitsManagedByGroup,
sum(c.usr_pbuntsowned + c.usr_rhuntsowned + c.usr_othuntsowned + c.
usr_lchountsowned + c.usr_genbedowned + c.usr_supportedowned + c.
usr_rescareowned + c.usr_bsuntsowned + c.usr_shelteredowned) as
TotalUnitsOwnedByGroup,
sum(c.usr_pbuntsdvlpmnt + c.usr_rhuntsdvlpmnt + c.usr_othuntsdvlpmnt + c.
usr_lchountsdvlpmnt + c.usr_bsuntsdvlpmnt + c.usr_sheltereddvlpmnt + c.
usr_genbeddvlpmnt) as TotalUnitsDevelopementByGroup,
sum(c.usr_supportedmngmnt + c.usr_bsuntsmngmnt + c.usr_rescaremngmnt + c.
usr_shelteredmngmnt)as TotalSupUnitsManagedByGroup,
sum(c.usr_supportedowned + c.usr_rescareowned + c.usr_bsuntsowned + c.
usr_shelteredowned) as TotalSupUnitsOwnedByGroup,
sum(c.usr_bsuntsdvlpmnt + c.usr_sheltereddvlpmnt) as
TotalSupUnitsDevelopmentByGroup,
Group_SizeBand = case
when sum(c.usr_pbuntsmngmnt+c.usr_rhuntsmngmnt+c.usr_bsuntsmngmnt+c.
usr_othuntsmngmnt+c.usr_lchountsmngmnt + c.usr_genbedmngmnt + c.
usr_rescaremngmnt + c.usr_shelteredmngmnt + c.usr_supportedmngmnt)
< '500' then 'Small (<500)'
when sum(c.usr_pbuntsmngmnt+c.usr_rhuntsmngmnt+c.usr_bsuntsmngmnt+c.
usr_othuntsmngmnt+c.usr_lchountsmngmnt + c.usr_genbedmngmnt + c.
usr_rescaremngmnt + c.usr_shelteredmngmnt + c.usr_supportedmngmnt)
between '500' and '2499' then 'Medium (500-2499)'
when sum(c.usr_pbuntsmngmnt+c.usr_rhuntsmngmnt+c.usr_bsuntsmngmnt+c.
usr_othuntsmngmnt+c.usr_lchountsmngmnt + c.usr_genbedmngmnt + c.
usr_rescaremngmnt + c.usr_shelteredmngmnt + c.usr_supportedmngmnt)
between '2500' and '4999' then 'Medium/Large (2500-4999)'
when sum(c.usr_pbuntsmngmnt+c.usr_rhuntsmngmnt+c.usr_bsuntsmngmnt+c.
usr_othuntsmngmnt+c.usr_lchountsmngmnt + c.usr_genbedmngmnt + c.
usr_rescaremngmnt + c.usr_shelteredmngmnt + c.usr_supportedmngmnt)
between '5000' and '9999' then 'Large (5000-9999)'
when sum(c.usr_pbuntsmngmnt+c.usr_rhuntsmngmnt+c.usr_bsuntsmngmnt+c.
usr_othuntsmngmnt+c.usr_lchountsmngmnt + c.usr_genbedmngmnt + c.
usr_rescaremngmnt + c.usr_shelteredmngmnt + c.usr_supportedmngmnt)
>= '10000' then '10,000+' end,
p.label_name as Member,
dbo.NHF_Main_Region_SF(p.master_customer_id, p.sub_customer_id) as
Main_Region,
c3.master_customer_id as Chief_customer_id,
isnull(c3.label_name, 'The Chief Executive') as chief_name,
c3.first_name as chief_first_name,
c3.last_name as chief_last_name,
c3.primary_job_title as chief_job_title,
c3.primary_email_address as chief_email,
c3.allow_solicitation_flag as chief_allow_solicitation,
c3.primary_phone as chief_Phone_number,
c2.master_customer_id as Gp_Chief_customer_id,
isnull(c2.label_name, 'The Chief Executive') as Gp_chief_name,
c2.first_name as Gp_chief_first_name,
c2.last_name as Gp_chief_last_name,
c2.primary_job_title as Gp_chief_job_title,
c2.primary_email_address as Gp_chief_email,
c2.allow_solicitation_flag as Gp_chief_allow_solicitation,
c2.primary_phone as GP_chief_Phone_number
from customer c (nolock), customer p (nolock)
join customer c3 on c3.master_customer_id = dbo.nhf_parent_chief_sf(c.
master_customer_id)
left outer join customer c2 on c2.master_customer_id=dbo.nhf_parent_chief_sf
(p.master_customer_id),
cus_address ca (nolock), cus_address_detail cad (nolock)
where dbo.NHF_Membership_SF(c.master_customer_id)='MEMBER'
and cad.priority_seq = '0'
and p.master_customer_id=cad.master_customer_id
and p.sub_customer_id=cad.sub_customer_id
and ca.cus_address_id=cad.cus_address_id
and p.master_customer_id =isnull(dbo.NHF_Parent_SF(isnull(dbo.NHF_Parent_SF
(c.master_customer_id),c.master_customer_id)),
isnull(dbo.NHF_Parent_SF(c.master_customer_id),c.master_customer_id))
group by p.master_customer_id, p.label_name,
c2.master_customer_id, isnull(c2.label_name, 'The Chief Executive'),c2.
name_prefix,
c2.first_name, c2.last_name,c2.name_suffix, c2.primary_job_title,
c2.primary_email_address, c2.allow_solicitation_flag, p.sub_customer_id, c2.
primary_phone,
c.master_customer_id, c.label_name,
c3.master_customer_id,
isnull(c3.label_name, 'The Chief Executive'),
c3.first_name, c3.last_name, c3.primary_job_title, c3.primary_email_address,
c3.allow_solicitation_flag, c3.primary_phone
order by 1 desc

The error message is as below:
Msg 107, Level 16, State 2, Line 3
The column prefix 'c' does not match with a table name or alias name used in
the query.

Please help.

--
Message posted via http://www.sqlmonster.com

From: Kalle Olavi Niemitalo on 5 Aug 2010 13:36
"sike11 via SQLMonster.com" <u21678(a)uwe> writes:

> from customer c (nolock), customer p (nolock)
> join customer c3 on c3.master_customer_id = dbo.nhf_parent_chief_sf(c.master_customer_id)
> left outer join customer c2 on c2.master_customer_id=dbo.nhf_parent_chief_sf(p.master_customer_id),
> cus_address ca (nolock), cus_address_detail cad (nolock)

I think the problem is in this FROM clause. You are mixing
commas and JOINs. If I understand the syntax correctly at
<http://msdn.microsoft.com/en-us/library/ms177634.aspx>,
SQL Server tries to parse each comma-separated <table-source>
separately. Thus, the definition of "c" in the first <table-source>:

customer c (nolock)

is then not visible to the second <table-source>:

customer p (nolock)
join customer c3 on c3.master_customer_id
= dbo.nhf_parent_chief_sf(c.master_customer_id)
left outer join customer c2 on c2.master_customer_id
= dbo.nhf_parent_chief_sf(p.master_customer_id)

which causes the error.

You could try moving the "join customer c3" into the first <table-source>:

from customer c with (nolock)
join customer c3 on c3.master_customer_id
= dbo.nhf_parent_chief_sf(c.master_customer_id),
customer p with (nolock)
left outer join customer c2 on c2.master_customer_id
= dbo.nhf_parent_chief_sf(p.master_customer_id),
cus_address ca with (nolock),
cus_address_detail cad with (nolock)

This should avoid the error, as each <table-source> now only uses
table aliases defined within itself. I also changed each
occurrence of "(nolock)" to "with (nolock)" because omitting the
WITH keyword is deprecated in SQL Server 2008. (It seems strange
to me that you use the NOLOCK hint on the "c" and "p" aliases of
the customer table but not on "c3" and "c2".)

You could also replace the commas with explicit JOINs and move
conditions from the WHERE clause into them; but I don't know
whether that would make a difference to the optimizer in practice.
From: sike11 via SQLMonster.com on 6 Aug 2010 05:22
Thank you very much!! That worked like a charm. You are a genius....

Kalle Olavi Niemitalo wrote:
>> from customer c (nolock), customer p (nolock)
>> join customer c3 on c3.master_customer_id = dbo.nhf_parent_chief_sf(c.master_customer_id)
>> left outer join customer c2 on c2.master_customer_id=dbo.nhf_parent_chief_sf(p.master_customer_id),
>> cus_address ca (nolock), cus_address_detail cad (nolock)
>
>I think the problem is in this FROM clause. You are mixing
>commas and JOINs. If I understand the syntax correctly at
><http://msdn.microsoft.com/en-us/library/ms177634.aspx>,
>SQL Server tries to parse each comma-separated <table-source>
>separately. Thus, the definition of "c" in the first <table-source>:
>
> customer c (nolock)
>
>is then not visible to the second <table-source>:
>
> customer p (nolock)
> join customer c3 on c3.master_customer_id
> = dbo.nhf_parent_chief_sf(c.master_customer_id)
> left outer join customer c2 on c2.master_customer_id
> = dbo.nhf_parent_chief_sf(p.master_customer_id)
>
>which causes the error.
>
>You could try moving the "join customer c3" into the first <table-source>:
>
> from customer c with (nolock)
> join customer c3 on c3.master_customer_id
> = dbo.nhf_parent_chief_sf(c.master_customer_id),
> customer p with (nolock)
> left outer join customer c2 on c2.master_customer_id
> = dbo.nhf_parent_chief_sf(p.master_customer_id),
> cus_address ca with (nolock),
> cus_address_detail cad with (nolock)
>
>This should avoid the error, as each <table-source> now only uses
>table aliases defined within itself. I also changed each
>occurrence of "(nolock)" to "with (nolock)" because omitting the
>WITH keyword is deprecated in SQL Server 2008. (It seems strange
>to me that you use the NOLOCK hint on the "c" and "p" aliases of
>the customer table but not on "c3" and "c2".)
>
>You could also replace the commas with explicit JOINs and move
>conditions from the WHERE clause into them; but I don't know
>whether that would make a difference to the optimizer in practice.

--
Message posted via http://www.sqlmonster.com

 | 
Pages: 1
Prev: Table Design
Next: Output padded result