August 7, 2008 at 10:14 am
Hello folks my programmer is trying to run this query and he received the error below, please provide your expert help.
select profile.acct, date_appl_sent, date_appl_recd,
CONVERT(char(100),DecryptByKey (enemail)) as email , rtrim(firstname) as 'firstname',
rtrim(lastname) as 'lastname', ltrim(convert(char(4),productcode)) as productcode
From BACKUPSQLSRV3.tradecs.dbo.profile inner join BACKUPSQLSRV3.trading.dbo.account
on profile.acct = BACKUPSQLSRV3.trading.dbo.account.acct
Where Convert(smalldatetime,date_appl_sent) =
DateAdd(wk, -2, datename(month,getdate()) +str(datepart(day,getdate())) +str(datepart(year,getdate())))
and date_appl_recd = ' ' and date_reminder_sent = ' ' and date_pen_appl_signed = ' '
Server: Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "BACKUPSQLSRV3.trading.dbo.account.acct" could not be bound.
Thanks
August 7, 2008 at 10:31 am
Check out this code and see if it provides what you the results you require. Also, when writing multiple table queries, you should use table aliases for all columns, not just the ones that are used in both tables. this does two things; one, you know which tables each of the values are coming from easily; and two, if a design change occurs and a new column is added to a table with the same name as a column in an existing column, things wont break.
select
pr.acct,
date_appl_sent,
date_appl_recd,
CONVERT(char(100),
DecryptByKey (enemail)) as email ,
rtrim(firstname) as 'firstname',
rtrim(lastname) as 'lastname',
ltrim(convert(char(4), productcode)) as productcode
From
BACKUPSQLSRV3.tradecs.dbo.profile pr
inner join BACKUPSQLSRV3.trading.dbo.account ac
on pr.acct = ac.acct
Where
-- Convert(smalldatetime,date_appl_sent) = DateAdd(wk, -2, datename(month,getdate()) + str(datepart(day,getdate())) + str(datepart(year,getdate())))
Convert(smalldatetime,date_appl_sent) = DateAdd(wk, -2, getdate())
and date_appl_recd = ' '
and date_reminder_sent = ' '
and date_pen_appl_signed = ' '
Edit: Curious, what is the data type for date_appl_sent?
😎
August 7, 2008 at 10:45 am
Try changing this:
on profile.acct = BACKUPSQLSRV3.trading.dbo.account.acct
to:
on profile.acct = account.acct
See if that does what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 7, 2008 at 12:56 pm
Again sorry about the duplicated post but this is my first time posting in this forum. I tried this query:
select
pr.acct,
date_appl_sent,
date_appl_recd,
CONVERT(char(100),
DecryptByKey (enemail)) as email ,
rtrim(firstname) as 'firstname',
rtrim(lastname) as 'lastname',
ltrim(convert(char(4), productcode)) as productcode
From
nt80.tradecs.dbo.profile pr
inner join nt80.trading.dbo.account ac
on pr.acct = ac.acct
Where
-- Convert(smalldatetime,date_appl_sent) = DateAdd(wk, -2, datename(month,getdate()) + str(datepart(day,getdate())) + str(datepart(year,getdate())))
Convert(smalldatetime,date_appl_sent) = DateAdd(wk, -2, getdate())
and date_appl_recd = ' '
and date_reminder_sent = ' '
and date_pen_appl_signed = ' '
It returned:
Acct Date_appl_sent Date_appl_recd Email Firstname Lastname pro
But no data in the columns. Any idea why?
August 7, 2008 at 1:14 pm
Because nothing met the criteria specified in the WHERE clause??
I can't really answer this question as I have no data (tables, sample data, expected results) with which to test the query against. this is your data, you have to answer why the query didn't return any results.
Take some time and read this article, it will tell you how best to ask for help:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
😎
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply