Dazed and Confused

Question

Dazed and Confused

Eliza

SSC Eights!

Points: 967
More actions
September 7, 2013 at 11:58 am

#298633

Hello,
I have an SQL query that is not returning any data for one of the columns. The data for this column is coming from a table and there is no other data coming from this table, just this one column. I am left joining to this table.
The join looks like
LEFT JOIN DataStore.dbo.t_YearDataYD
ON (
YD.Year = Z.ExpYear
AND YD.Framework = 'Year End'
AND YD.[Level] = 'All'
AND YD.[Type] = 'T'
)
Z is the alias for the main table I am joining to.
If I change the join to
LEFT JOIN DataStore.dbo.t_YearDataYD
ON (
YD.Year = Z.ExpYear
AND YD.Framework LIKE 'Y%'
AND YD.[Level] LIKE 'A%'
AND YD.[Type] = 'T'
)
I have data returned to the column. What I don't understand is if I do
SELECT *
FROM DataStore.dbo.t_YearDataYD
WHERE YD.Year = '2013'
AND YD.Framework = 'Year End'
AND YD.[Level] = 'All'
AND YD.[Type] = 'T'
The data is returned no problems.
I don't understand why my original join doesn't work but does as a where clause on a direct select. I am substituting the text 2013 for Z.ExpYear but this value does defiantly exist in my table along with the data I am trying to retrieve.
Thanks
Eliza

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

TomThomson SSC Guru Points: 104773 More actions · Answer 1

What happens if, in the final select that does return the data, you replace "= 'Year End'" by "LIKE 'Y'", and similar for "= 'All'" ?

Tom

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

What is the datatype for Z.ExpYear and YD.Year?

Also, since you're relatively new to these forums, see the first "Helpful Link" in my signature line below for how to get better help more quickly for future questions. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 3

If you add YD.Framework and YD.Level to the result set, what values do you see in these columns?

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Eliza SSC Eights! Points: 967 More actions · Answer 4

Hi All,

Thanks for the replies.

The issue has now been resolved. We found closing the session and creating a new one and running the SQL made it work

The Line

AND YD.[Level] = 'All'

We have found the Level column had a length of 4 for its value saved in the database. We updated the value to 'All' and its worked fine in the on clauses. The extra character appeared to be a space.

So guessing there was some setting on the original session we had open. Not sure what though.

Thanks

Eliza

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 5

Certainly a bit of a mystery!

I can explain the part about the trailing space: if you save a value with trailing spaces in a varchar column, the trailing spaces, provided that the column was creating with the setting ANSI_PADDING ON, and this setting is the default.

However, when comparing character data, trailing spaces are not significant. That is, [font="Courier New"]'All'[/font] and [font="Courier New"]'All '[/font] are equal to each other, and there is no setting to change this.

I don't think we will be able to understand what happened, but a couple of questions.

1) Can you post the output of "SELECT @@version".?

2) What is the data type of this column?

3) What is the collation of the column? (You can view this with sp_help.)

4) How did you conclude that the length of the value was 4?

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

TomThomson SSC Guru Points: 104773 More actions · Answer 6

Erland Sommarskog (9/8/2013)
Certainly a bit of a mystery!
I can explain the part about the trailing space: if you save a value with trailing spaces in a varchar column, the trailing spaces, provided that the column was creating with the setting ANSI_PADDING ON, and this setting is the default.
However, when comparing character data, trailing spaces are not significant. That is, [font="Courier New"]'All'[/font] and [font="Courier New"]'All '[/font] are equal to each other, and there is no setting to change this.
I don't think we will be able to understand what happened, but a couple of questions.
1) Can you post the output of "SELECT @@version".?
2) What is the data type of this column?
3) What is the collation of the column? (You can view this with sp_help.)
4) How did you conclude that the length of the value was 4?

Looks to me as if it was a leading space, not a trailing one. That's an input error that happens now and again, and would explain all we've heard here.

Tom