The multi-part identifier "t.clms" could not be bound.

[nc297]

I am getting this on the first line what am I doing wrong?

The multi-part identifier "t.clms" could not be bound.

SELECT n.doc, t.clms, Isnull(pendfoover1, 0) AS PendFOover1 , Isnull(pendfoover2, 0) AS PendFOover2, Isnull(pendfoover3, 0) AS PendFOover3
into #temppd
FROM natdocfile n
LEFT JOIN
(SELECT t.fo, t.clms, Isnull(COUNT(t.clms), 0) AS PendFOover1
FROM t16pendall t
WHERE(mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y'))
and t.clms not in(select clms from t16pendmvt)
and (Datediff(DAY, t.flg_cdt, Getdate()) > 300)
group by t.fo, t.clms)d
on n.doc=d.fo
LEFT JOIN
(SELECT t.fo, t.clms, Isnull(COUNT(t.clms), 0) AS PendFOover2
FROM t16pendmvt m JOIN t16pendall t ON m.clms= t.clms
WHERE( mvt_typ = 'R' ) AND not (mvt_loc LIKE 'R%'
or mvt_loc LIKE 'S%'
OR mvt_loc LIKE 'V%' )
and (Datediff(DAY, t.flg_cdt, Getdate()) > 300 )
GROUP BY t.fo, t.clms)f
on n.doc=f.fo
LEFT JOIN
(SELECT t.fo, t.clms, Isnull(COUNT(t.clms), 0) AS PendFOover3
FROM t16pendmvt m JOIN t16pendall t ON m.clms= t.clms
WHERE mvt_typ = 't'
and (Datediff(DAY, t.flg_cdt, Getdate()) > 300 )
GROUP BY t.fo, t.clms)g
on n.doc = g.fo

 

[markros]

There is no T alias in your select statement. Your original table aliases as T is now a derived table aliased as D. Try changing t.clms to d.clms

PluralSight Learning Library

 

[nc297]

Thanks when I did this it worked but I am only getting the d results. How do I get the other subquery results (the f and the g)?

SELECT n.doc, d.clms, Isnull(pendfoover1, 0) AS PendFOover1 , Isnull(pendfoover2, 0) AS PendFOover2, Isnull(pendfoover3, 0) AS PendFOover3
into #temppd
FROM natdocfile n
LEFT JOIN
(SELECT t.fo, t.clms, Isnull(COUNT(t.clms), 0) AS PendFOover1
FROM t16pendall t
WHERE(mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y'))
and t.clms not in(select clms from t16pendmvt)
and (Datediff(DAY, t.flg_cdt, Getdate()) > 300)
group by t.fo, t.clms)d
on n.doc=d.fo
LEFT JOIN
(SELECT t.fo, t.clms, Isnull(COUNT(t.clms), 0) AS PendFOover2
FROM t16pendmvt m JOIN t16pendall t ON m.clms= t.clms
WHERE( mvt_typ = 'R' ) AND not (mvt_loc LIKE 'R%'
or mvt_loc LIKE 'S%'
OR mvt_loc LIKE 'V%' )
and (Datediff(DAY, t.flg_cdt, Getdate()) > 300 )
GROUP BY t.fo, t.clms)f
on n.doc=f.fo
LEFT JOIN
(SELECT t.fo, t.clms, Isnull(COUNT(t.clms), 0) AS PendFOover3
FROM t16pendmvt m JOIN t16pendall t ON m.clms= t.clms
WHERE mvt_typ = 't'
and (Datediff(DAY, t.flg_cdt, Getdate()) > 300 )
GROUP BY t.fo, t.clms)g
on n.doc = g.fo

 

[gmmastros]

Your query is using multiple derived tables, which is perfectly fine. The point you need to realize is that whatever is inside the derived table(s) is not available outside the derived table(s) except for the columns you return. Those columns are only accessible using the alias(es) you used. Let me show you what I mean by removing the queries from the derived table.

SELECT [!]t[/!].clms, OtherColumns
FROM natdocfile [!]n[/!]
LEFT JOIN
()[!]d[/!]
       on n.doc=d.fo
LEFT JOIN 
()[!]f[/!]
       on n.doc=f.fo
LEFT JOIN 
()[!]g[/!]
     on n.doc = g.fo

In the select line, you are referencing a table [!]t[/!], but you are only using one real table [!]n[/!] and 3 derived tables [!]d, f, and g[/!].

Your query appears to be returning clms from the 3 derived tables, as left join. So, instead of:

t.clms

use:

Coalesce(d.clms, f.clms, g.clms) as clms

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Depending on what exactly do you want. You can use coalesce(d.clms, f.clms, g.clms) as clms to take first NON NULL value out of these 3 or you can put each clms as
d.clms as dclms, f.clms as fclms, g.clms as gclms

PluralSight Learning Library

 

[nc297]

Thanks so much! Did I set my query up the most efficient way?

 

[markros]

No, your queries are not set in efficient way and also it's not clear at all what do you want to achieve. If you want to count a field, then why do you group by the same field?

The DateDiff expression in the WHERE clause is preventing this query to use indexes if you have index on the flg_cdt field.

Perhaps if you can post data definition for your table(s), some input and desired output, we can help you with the query.

PluralSight Learning Library

 

[nc297]

Thanks Markros. How's this is it efficient? I've changed the query around to this:

What I am doing is getting the clms that are over 300 days so I called them type 1 The ones that are over 250 < 299 days I called them type2. I'm putting the results into PendingClms this way I can query by doc.

Here's the query and the table definitions are down below for PendingClms and Natdocfile.

--Pendinfo > 300
select n.area, n.dist, n.doc, n.reg, n.regionacronym, '1' as type, clms, t.flg_cdt
into PendingClms
from t16pendall t inner join natdocfile n on t.fo = n.doc
WHERE(mft_POSN1_CD in('b','d') or (MFT_POSN1_CD='a' and aged_alien_rsw='y'))
and t.clms not in(select clms from t16pendmvt)
and (Datediff(DAY, t.flg_cdt, Getdate()) > 300)

insert PendingClms
select n.area, n.dist, n.doc, n.reg, n.regionacronym, '1' as type, t.clms, t.flg_cdt
FROM t16pendmvt m inner JOIN t16pendall t ON m.clms= t.clms
inner join natdocfile n on t.fo = n.doc
WHERE( mvt_typ = 'R' ) AND not (mvt_loc LIKE 'R%' or mvt_loc LIKE 'S%'
OR mvt_loc LIKE 'V%' )
and (Datediff(DAY, t.flg_cdt, Getdate()) > 300 )


insert PendingClms
select n.area, n.dist, n.doc, n.reg, n.regionacronym, '1' as type, t.clms, t.flg_cdt
FROM t16pendmvt m inner JOIN t16pendall t ON m.clms= t.clms
inner join natdocfile n on t.fo = n.doc
WHERE mvt_typ = 't'
and (Datediff(DAY, t.flg_cdt, Getdate()) > 300 )

--Pending over 250 < 299
insert PendingClms
select n.area, n.dist, n.doc, n.reg, n.regionacronym, '2' as type, t.clms, t.flg_cdt
FROM t16pendall t
inner join natdocfile n on t.fo = n.doc
WHERE (mft_POSN1_CD in('b','d') OR (MFT_POSN1_CD='a' and aged_alien_rsw='y'))
AND ( Datediff(DAY, flg_cdt, Getdate()) > 250 )
AND ( Datediff(DAY, flg_cdt, Getdate()) < 299 )


CREATE TABLE [dbo].[PendingClms](
[area] [varchar](2) NULL,
[dist] [char](3) NULL,
[doc] [char](3) NOT NULL,
[reg] [char](1) NULL,
[region] [char](3) NULL,
[type] [varchar](1) NOT NULL,
[clms] [char](6) NOT NULL,
[flg_cdt] [datetime] NULL
) ON [PRIMARY]

GO
Insert into pendingclms

Select '03', '010', '010', 'a', 'BOS', '1', '543298', '10/08/2009' union all
Select '03', '010', '013', 'a', 'BOS', '1', '879628', '02/24/2010' union all
Select '05', '226', '241', 'c', 'PHI', '1', '548972', '08/03/2009' union all
Select '02', '276', 'A96', 'c', 'PHI', '1', '895004', '11/30/2009' union all
Select '05', '641', 'B09', 'd', 'ATL', '1', '249875', '06/03/2009' union all
Select '05', '400', '400', 'e', 'CHI', '2', '547777', '03/28/2010' union all
Select '04', '216', '235', 'c', 'PHI', '2', '563247', '04/20/2010' union all
Select '04', '207', '236', 'c', 'PHI', '2', '056987', '04/19/2010' union all
Select '06', '287', '287', 'c', 'PHI', '2', '659874', '04/28/2010' union all
Select '06', '285', '300', 'c', 'PHI', '2', '157896', '04/29/2010'

CREATE TABLE [dbo].[natdocfile](
[doc] [varchar](3) NOT NULL)


insert into natdocfile

select '010' union all
select '276' union all
select '013' union all
select 'A96' union all
select '216' union all
select 'B14' union all
select '913' union all
select '287' union all
select '300' union all
select '207' union all
select '400'