simple sql join has stopped working

the problem is likely in the view  View_Endowment_Summary_Report itself.
can you run that one alone without error(s)?

Yes I can access that file fine.  In fact, I can take the view and access other files that are linked by the projectid and I can take the temp file and link it those other files by projectid.  Any combination works except linking the 2 files together!!  It's the most bizarre thing I've ever seen.  I've had several others look at it too - spent hours looking at it!  No one can figure out why - especially since it worked for so long and nothing has changed.

Hi there,

I would try dropping the view and creating it again.
There may have been some db structure changes that has not updated the schema for the view internally.

You can also try just
select top 10 * from View_Endowment_Summary_Report
and
select top 10 * from tempTableEP
and make sure the structure is still what you expect

I did try recreating the view and that didn't help.  I can access each table separately and have done that.  I've looked through each of them to make sure there isn't garbage info in one of the fields that I'm using for the join and everything is fine.  (One file can have as few as 1 or 2 records - the other has only about 2500.)  And as I mentioned originally I can link each table separately to another table, I just can't link the 2 of them together any more.

The syntax you show is solid - can't see anything wrong with it.
Yet "In its simplest the join is:" - I believe the problem lies in the non-simplest form.. as in your actual code!
Are you using it in a union? Is there a coalesce in there?

I can replace those table names with other names using the same join statement and they work fine.  Its just those 2 tables that suddenly won't join.    Both tables will separately join to another file with the same key.  

I might sound stubborn, but the problem IS the data.
you might be looking at the wrong database/table, but the error message tells you what is wrong.
somewhere you have the value 'Endowed' in a varchar column, which is presumed to have only numerical data.

Hi,

Can you please show the output of these statements?

sp_columns View_Endowment_Summary_Report
sp_columns tempTableEP

Stubborn, no - helpful yes!  I appreciate all comments.  I would tend to agree except that both tables successfully join to another table with the same key.   If they didn't do that I'd be convinced you were right.   The view actually originates from that third table.  Someone locally suggested that SQL is hosed up, but you would think this would occur in more reports if that was the problem.    Someone else has said they've seen this before, but can't recall how to fix it.  

Here you go:

   
sql-problem-tables.doc

BTW, I noticed they were the same type but a different length.  So I changed the temp file so the projectid is the same length.  But I get the same error.

This may be a performance killer, but give it a go if the data is not large

SELECT TP.*
FROM View_Endowment_Summary_Report as TP INNER JOIN tempTableEP AS ESR ON
CONVERT(VARCHAR(MAX),TP.ProjectID) = CONVERT(VARCHAR(MAX),ESR.ProjectID)

Even though the view and table both have ProjectID as type varchar, sql server is expanding the view and trying to go back to the base tables.

In reporting services it won't allow this code - says it can't graphically represent the code.  I copied it out to just run it separately and it won't run that either - just said there was an error.  I took out the comma after the (max) and it still shows as an error.

FYI, I sure appreciate your efforts.  I wanted to let you know that I am going to be leaving to set up for an auction shortly so may not be able to get back to this until tomorrow.  This report is critical in our business, so I want to continue working on it.  I didn't want you to think my lack of response was because I didn't care about this code.

Not sure about SSRS, but how about another way to change it to varchar...

SELECT TP.*
FROM View_Endowment_Summary_Report as TP INNER JOIN tempTableEP AS ESR ON
''+TP.ProjectID = ''+ESR.ProjectID

I missed a ) in the convert above.  It looks like this might work - it does for the simple statement above.   but...  here's the actual code that is bombing out.  At least now the error relates to the actual field value.  But I can't see where it is trying to convert it to int.

SELECT     View_Endowment_Summary_Report.ProjectID, View_Endowment_Summary_Report.ProjectDimID,
                      View_Endowment_Summary_Report.ProjectDescription, View_Endowment_Summary_Report.FundDimID,
                      View_Endowment_Summary_Report.FundDescription, View_Endowment_Summary_Report.AccountCategorySystemID,
                      View_Endowment_Summary_Report.Amount, View_Endowment_Summary_Report.NaturalAmount, View_Endowment_Summary_Report.AccountCode,
                      View_Endowment_Summary_Report.PostDate, View_Endowment_Summary_Report.ReportClass,
                      View_Endowment_Summary_Report.EndowedProjectID, View_Endowment_Summary_Report.DistributionProject,
                      View_Endowment_Summary_Report.Sandinista, View_Endowment_Summary_Report.CollegeDesc, View_Endowment_Summary_Report.DeptDesc,
                      View_Endowment_Summary_Report.FullName, View_Endowment_Summary_Report.LastName,
                      View_Endowment_Summary_Report.ScholarshipRecipient, View_Endowment_Summary_Report.PrepForName,
                      View_Endowment_Summary_Report.COBPA_UDM, View_Endowment_Summary_Report.Reinvest, tempTableEP.EP_Amt
FROM         View_Endowment_Summary_Report LEFT OUTER JOIN
                      tempTableEP ON CONVERT(VARCHAR(MAX),View_Endowment_Summary_Report.EndowedProjectID) = CONVERT(VARCHAR(MAX),tempTableEP.ProjectID)
WHERE     (View_Endowment_Summary_Report.EndowedProjectID IN (41438)) AND (View_Endowment_Summary_Report.Sandinista = 0)

The error says:  

Conversion failed when converting the varchar value '46209-21-20' to data type int

Hi there,

That's simple, real simple now.

WHERE     (View_Endowment_Summary_Report.EndowedProjectID IN ('41438')) AND (View_Endowment_Summary_Report.Sandinista = 0)

Add the quotes because EndowedProjectID can have non-numeric values

Ooops - that was my error.  I knew that one!  Its actually a prompt field - had to enter a value.  Still doesn't work - get back to the:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Endowed' to data type int.

I don't understand
Do you mean that you entered "46209-21-20" as a prompt field and it got put into here?

WHERE     (View_Endowment_Summary_Report.EndowedProjectID IN (<prompt>)) AND (View_Endowment_Summary_Report.Sandinista = 0)

Try wrapping it as a varchar again, you are comparing it to EndowedProjectID which is varchar(255)

WHERE     (View_Endowment_Summary_Report.EndowedProjectID IN (convert(varchar(max),<promptfield>))) AND (View_Endowment_Summary_Report.Sandinista = 0)

Yes - I had entered the 46209-21-20 in the prompt field.

So far this convert statement is still giving me the error listed above

I am not able to work on this again, so hoping you can provide more assistance in the morning.

Wrap both sides...

WHERE     (convert(varchar(max),View_Endowment_Summary_Report.EndowedProjectID) IN (convert(varchar(max),<promptfield>))) AND (View_Endowment_Summary_Report.Sandinista = 0)

When I put the convert statement in this section, I keep getting an incorrect syntax error.  I've removed it and replaced it 3 times to make sure I have the ) in the right spot - but no luck.  Here's my copied code
WHERE     (CONVERT(VARCHAR(MAX)View_Endowment_Summary_Report.EndowedProjectID) IN CONVERT(VARCHAR(MAX)('41438'))) AND (View_Endowment_Summary_Report.Sandinista = 0)

I'm still getting the error.  I actually took the one out around the 41438 and still get the message.  It must not like the first convert in the where statement.

>>I'm still getting the error.  I actually took the one out around the 41438 and still get the message.  It must not like the first convert in the where statement.

You need it at both ends... don't take the one around 41438 out

I'm getting a syntax error whether I have the convert in both places or just right after the Where statement begins.  I can't get past the syntax error.

I know this has dragged on so hope you can stick with me on this!

what about this?

WHERE  CAST( View_Endowment_Summary_Report.EndowedProjectID AS VARCHAR(MAX))  = CAST(41438 AS VARCHAR(MAX))
   AND View_Endowment_Summary_Report.Sandinista = 0

Select allOpen in new window

This person was right on in using the convert(varchar).  It turned out that this wasn't needed on the 2 values I was joining - it was needed on another field entirely.