problem in executing the query

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

problem in executing the query

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2009-03-24 : 06:39:36

Adhoc Query:


		select ouinstance,fb_id,invoice_no,convert(datetime,promise_date)as promise_date
		from ramcofin02.scmdb.dbo.cc_commitment_dtl a1 
		where a1.version_no = (select max(b1.version_no) 
								    from   ramcofin02.scmdb.dbo.cc_commitment_dtl b1
								    where  b1.fb_id		    =   a1.fb_id			
								    and	   b1.invoice_no	=	a1.invoice_no		
									and	   b1.seq_no		=	a1.seq_no			
									and   convert(datetime,b1.promise_date)  between '01 mar 2009' and '31 mar 2009'
									and   b1.fb_id like 'RSPTESOLNFB' 
		)
		and  convert(datetime,a1.promise_date)  between '01 mar 2009' and '31 mar 2009'
		and  a1.fb_id like  'RSPTESOLNFB'

when i try to execute the above Adhoc query , i get the following error:

Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 107, Level 16, State 2, Line 1
The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 2, Line 1
The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 2, Line 1
The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-24 : 07:39:49

Have you left out part of query - I can't see any mention of: Tbl1001 in query.

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2009-03-24 : 09:05:02

hi,
that is generated by optimizer

quote:
Originally posted by darkdusky

Have you left out part of query - I can't see any mention of: Tbl1001 in query.

rekabm
Starting Member

1 Post

Posted - 2009-04-24 : 13:04:17

I've been getting this too. Does anyone have a solution. I has to do with the subquery from the linked server. In my scenario, query from SQL 2005, and the subquery is hitting a SQL 2000 linked server:

SELECT
m.mclient AS CLIENT_ID,
m.mmatter AS MATTER_ID,
l.linvoice AS INVOICE_NO,
MAX(CASE WHEN (hm.hmbaty IS NULL OR LEN(RTRIM(LTRIM(hm.hmbaty))) = 0) THEN m.mbillaty ELSE hm.hmbaty END) AS AR_ATTY_ID,
MAX(CASE WHEN (hm.hmbaty IS NULL OR LEN(RTRIM(LTRIM(hm.hmbaty))) = 0) THEN m.mbillaty ELSE hm.hmbaty END) AS BILLING_ATTY_ID
FROM [TYSYS].DBNAME..dbo.ledger l
LEFT OUTER JOIN [TYSYS].DBNAME..dbo.matter m
ON l.lmatter = m.mmatter
LEFT OUTER JOIN [TYSYS].DBNAME..dbo.hmatter hm
ON l.lmatter = hm.hmatter
AND hm.hmdate1 = (SELECT MAX(hm2.hmdate1)
FROM [TYSYS].DBNAME..dbo.hmatter hm2
WHERE hm2.hmatter = l.lmatter
AND hm2.hmdate1 <= l.ltradat)
GROUP BY m.mclient, m.mmatter, l.linvoice

I get:

Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 107, Level 16, State 2, Line 1
The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 2, Line 1
The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2009-04-24 : 18:20:36

Sent_Sara, it looks like the alias are conflicting with the set boundaries and they are called beyond their scope definition.

May be this is what you are trying to do...

regards,
Anil Kumar.

declare @Bill table (Ouinstance Char(10), fb_id char(20),
invoice_no int, promise_date VARCHAR(10), version_no int, seq_no int)
insert into @Bill select '1001','RSPTESOLNFB', 88888, '20090317', 11111, 22222
insert into @Bill select '1001','RSPTESOLNFB', 8888, '20090306', 21111, 22422
insert into @Bill select '1001','RSPTESOLNFB', 88388, '20090307', 31111, 21222
insert into @Bill select '1001','RSPTESOLNFB', 88588, '20090322', 41111, 22322
insert into @Bill select '1001','RSPTESOLNFB', 88688, '20090303', 11111, 22122
insert into @Bill select '1001','RSPTESOLNFB', 88888, '20090310', 31111, 21222
insert into @Bill select '1001','RSPTESOLNFB', 88388, '20090317', 31111, 21222

select ouinstance, fb_id, invoice_no, convert(datetime,promise_date)as promise_date
from @BILL a1
where a1.version_no =
(select max(b1.version_no) from @Bill b1
JOIN @Bill A1 ON b1.fb_id = a1.fb_id
and b1.invoice_no = a1.invoice_no
and b1.seq_no = a1.seq_no
and convert(varchar(10),convert(datetime, b1.promise_date),101)
between '03/1/2009' and '03/31/2009'
and b1.fb_id like 'RSPTESOLNFB' )
and convert(datetime,a1.promise_date) between '01 mar 2009' and '31 mar 2009'
and a1.fb_id like 'RSPTESOLNFB'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources