HI - I have one peculiar issue with sql query.   Below query is not returning any rows.  The issue lies with store_code(st.store_code = 'MAUR') filter

select st.store_code, s.source_title,c.*from rpt.Combined c
join dim.store st 
on c.store_key = st.store_key  
join dim.source s
on c.source_key = s.source_key
where c.event_date_key = 20140406
and source_title = 'ADROIT'
and  st.store_code = 'MAUR'

However, if i use LTRIM(st.store_code)= 'MAUR' in the above query its returning the desired results.  I dont have any leading and trialing spaces for st.store_code in dim.store table.  To prove that i have executed the below query which is returning the results

select * from dim.store st where
 st.store_code = 'MAUR'

Can someone help in identifying the root cause for this issue.

thanks

TRIM Function to Remove Leading and Trailing Spaces of String 

please refer the below link

http://support.microsoft.com/kb/316626

• Edited by vanchan 11 minutes ago

Thanks Vanchan.  I clearly mentioned that there are no spaces in table. 

below query is returning the records.

select * from dim.store st where
st.store_code = 'MAUR'

But in the below query its not returning :

select st.store_code, s.source_title,c.*from rpt.Combined c
join dim.store st 
on c.store_key = st.store_key  
join dim.source s
on c.source_key = s.source_key
where c.event_date_key = 20140406
and source_title = 'ADROIT'
and  st.store_code = 'MAUR'

If I use LTRIM function its returning the rows:

select st.store_code, s.source_title,c.*from rpt.Combined c
join dim.store st 
on c.store_key = st.store_key  
join dim.source s
on c.source_key = s.source_key
where c.event_date_key = 20140406
and source_title = 'ADROIT'
and  LTRIM(st.store_code) = 'MAUR'

Netha,

I guess the join is the culprit here. When you jus plainly mention 'join' it takes it to be a inner join by default. So only matching rows are returned.

So, in your case i suppose there are rows in Store table with store_code='MAUR' but these dont have corresponding rows in the other joined tables like Combined and Source. So try changing the join as LEFT JOIN as below?

select st.store_code, s.source_title,c.*
from dim.store st  
left join rpt.Combined c on c.store_key = st.store_key   
left join dim.source s on c.source_key = s.source_key
where c.event_date_key = 20140406
and source_title = 'ADROIT'
and st.store_code = 'MAUR'

HI Jay - Thanks for reply.  I clearly metnioned that its not the issue with JOIN.  Its with  (st.store_code) = 'MAUR'.

If I use LTRIM(st.store_code) = 'MAUR' its retruning.  If I remove the LTRIM() its not returning.  However, there are not trialing spaces in my values.  

To prove this, I ran below query which is returning one value

select * from dim.store st where
st.store_code = 'MAUR'.

However, if i use dim.store table in JOIN its not returning values without using LTRIM fucntion.

select st.store_code, s.source_title,c.*from rpt.Combined c
join dim.store st 
on c.store_key = st.store_key  
join dim.source s
on c.source_key = s.source_key
where c.event_date_key = 20140406 and source_title = 'ADROIT'
and  st.store_code = 'MAUR'

If I use LTRIM function its returning the rows:

select st.store_code, s.source_title,c.*from rpt.Combined c
join dim.store st 
on c.store_key = st.store_key  
join dim.source s
on c.source_key = s.source_key
where c.event_date_key = 20140406
and source_title = 'ADROIT'
and  LTRIM(st.store_code) = 'MAUR'

Hi Netha,

Can you please provide the DDLs of three tables you are using,

Also post us how many rows you are getting output for this query? 

select * from dim.store st where
st.store_code = 'MAUR'

also try to run and update statement on this table as below and execute your query

update dim.store

set store_code = ltrim(rtrim(store_code))

where

store_code = 'MAUR'

once you run this update, then run your query.  Let us know the result.

• Proposed as answer by Ibrahim Basha Shaik 23 hours 32 minutes ago

its returning 1 row.  I updated the table as per your suggestion, but still getting same issue.  If I use LTRIM() its returning rows.

thanks

You are blinded by your own assumptions and your inability to debug your code by actually examining the rows that you think should be in the resultset but are not. Perhaps because your test data is too large - but that is easily compensated for with a little creativity.  You also have not posted DDL or sample data so that forces anyone that is interested to guess.  So here is mine:

SELECT * FROM (VALUES (1, 'MAUR'), (2, '   MAUR'), (3, ' MAUR'), (4, 'MAUR  '), (5, 'MAUU') ) AS MyTable(ID, STORE_CODE)
where STORE_CODE = 'MAUR';

SELECT * FROM (VALUES (1, 'MAUR'), (2, '   MAUR'), (3, ' MAUR'), (4, 'MAUR  '), (5, 'MAUU') ) AS MyTable(ID, STORE_CODE)
where ltrim(STORE_CODE) = 'MAUR';

You can see the effect of the ltrim more clearly - and it does have an effect.  Given that your use of ltrim produces a result that is more desirable (but not necessarily "correct"), then this exposes a potential flaw in your understanding of the data (and perhaps the source of this data since it would be unusual to expect strings starting with spaces in a well designed and constructed application/system). If you let garbage in you will find it difficult to get useful information out.  Since you seem to believe that there should be rows that match your criteria, then the other likely culprit is the additional comparison of source_title (from a table that you did not specify which is a bad habit). 

TRIM Function to Remove Leading and Trailing Spaces of String 

please refer the below link

http://support.microsoft.com/kb/316626

• Edited by vanchan Tuesday, April 22, 2014 10:18 AM

If I use LTRIM(st.store_code) = 'MAUR' its retruning.  If I remove the LTRIM() its not returning.  However, there are not trialing spaces in my values.  

To prove this, I ran below query which is returning one value

select * from dim.store st where
st.store_code = 'MAUR'.

However, if i use dim.store table in JOIN its not returning values without using LTRIM fucntion.

Netha,

Exactly. :) I suppose, the Store table has values WITH and WITHOUT trailing spaces. So it returns records when you fire the query: select * from dim.store st where st.store_code = 'MAUR'

However, the rows of Store table which have a corresponding set of rows in Combined and Source tables (rows of Store that participate in the join) are the ones with space ahead of them. So this seems to be the problem in your case. So there are two options:

• use JOIN as it is and have TRIM functions
• modify JOIN such that all rows of store find a place in the result set. (Note that this might not be the exact functionality that you would want. Do check up and proceed!)