none
SQL Query issue RRS feed

  • Question

  • 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

    Tuesday, April 22, 2014 9:56 AM

Answers

  • 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). 

    Tuesday, April 22, 2014 1:08 PM
  • 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!)

    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    Wednesday, April 23, 2014 6:20 AM

All replies

  • 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
    Tuesday, April 22, 2014 10:14 AM
  • 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'

    Tuesday, April 22, 2014 10:24 AM
  • 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'


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    Tuesday, April 22, 2014 10:36 AM
  • 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'

    Tuesday, April 22, 2014 10:43 AM
  • 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.

    Tuesday, April 22, 2014 10:59 AM
  • 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

    Tuesday, April 22, 2014 11:13 AM
  • 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). 

    Tuesday, April 22, 2014 1:08 PM
  • 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!)

    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    Wednesday, April 23, 2014 6:20 AM