0

I have the following query

SELECT *
FROM ChangeLog
INNER JOIN Jobs
  ON CONVERT(Varchar(30), RTRIM(Substring(CAST(XagtableKeyValues as Varchar), 12, 30))) = Convert(Varchar(30), jmoJOBID)
WHERE xagTableName = 'JOBS'
    AND Cast(xagChangeDate as date) = Cast(getdate() as Date)
    AND xagTableOldValues like '%jmpreleasedtofloor%'

Now I know that my substring is a JobID, and I know that these JobIDs exist in the Jobs table. However, my query returns nothing. Seperate the two queries and I get what I would expect.

What is the problem here?

And here is another thing I discovered, if I change the Inner Join to say a Left Outer Join. The query still returns nothing. Despite the fact that if I remove the join altogether, It returns rows as expected.

I ran this query,

SELECT Top(10)CONVERT(Varchar(20), RTRIM(Substring(CAST(XagtableKeyValues as Varchar), 12, 30)))
FROM M1_LV..ChangeLog
WHERE xagTableName = 'JOBS'
    AND Cast(xagChangeDate as date) = Cast(getdate() as Date)
    AND xagTableOldValues like '%jmpreleasedtofloor%'

And that returns this,

29882-01-0001
29882-02-0001
29834-01-0001
29834-02-0001
29834-03-0001
29889-01-0001
29889-02-0001
29898-01-0001
29899-01-0001
29846-01-0001

I copied straight from SQL Server Management studio and noticed there is a newline character between the entries. So, How do I remove the newline character? RTRIM doesn't seem to do the trick.

Although this all still begs the question, why didn't the Left Outer Join Work?

myacobucci
  • 187
  • 2
  • 12
  • 2
    try removing clauses in the where to check if thats the problem or check what `CONVERT(Varchar(30), RTRIM(Substring(CAST(XagtableKeyValues as Varchar), 12, 30)))` does return since it may be producing an extra character – Smog Mar 19 '15 at 21:19
  • Are the JobID's integers? – Stephan Mar 19 '15 at 21:21
  • Nope, They're VarChar. – myacobucci Mar 19 '15 at 21:23
  • 2
    also @myacobucci can you add a couple of rows of your data so we can help you better – Smog Mar 19 '15 at 21:24
  • 1
    It was a newline issue, found my answer here. http://stackoverflow.com/questions/951518/replace-a-newline-in-tsql – myacobucci Mar 19 '15 at 21:39
  • You're still filtering on columns after the join. And if you're filtering on columns from the inner table then you're effectively negating the left join anyway. – shawnt00 Mar 19 '15 at 21:56

0 Answers0