May 21, 2012 at 12:40 pm
Hi,
I take care of such fields in my column ITEM in my query by making then space(1)
then create a ITEM parameter and data set in my report and add extra field as space like below
select space(1) as ITEM where ITEm like ' ' or ITEM is null
union
Select Item from ITEMS where ITEM is not null
but still my report fails to show "SOME" rows where ITEM has nothing in it..Is it carriage retun issue?
Any ideas please??
Thanks
May 21, 2012 at 1:07 pm
Pretty hard to tell without anything solid to look at but I think you could make your query a bit simpler like this:
select case when ITEM is null then ' ' when Item = ' ' then ' ' else ITEM end as Item
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 21, 2012 at 1:22 pm
If it is already a space why change it to a space? Wouldn't this get everything:
Select ISNULL(Item, ' ') from ITEMS
Or are you trying to find values that are exactly one space?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
May 21, 2012 at 1:25 pm
toddasd (5/21/2012)
If it is already a space why change it to a space? Wouldn't this get everything:
Select ISNULL(Item, ' ') from ITEMS
Or are you trying to find values that are exactly one space?
The original query posted was 2 spaces not just a single one. Seems like an odd requirement but who knows?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 21, 2012 at 1:30 pm
why not eliminate the space by using replace
REPLACE ( string_expression , string_pattern , string_replacement )
select replace (column,' ','')
***The first step is always the hardest *******
May 22, 2012 at 8:21 am
Thank you all.
May 22, 2012 at 9:02 am
Sean Lange (5/21/2012)
toddasd (5/21/2012)
If it is already a space why change it to a space? Wouldn't this get everything:
Select ISNULL(Item, ' ') from ITEMS
Or are you trying to find values that are exactly one space?
The original query posted was 2 spaces not just a single one. Seems like an odd requirement but who knows?
I thought it looked too wide for one space, but I went cross-eyed trying to count. 😛
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
May 22, 2012 at 9:04 am
toddasd (5/22/2012)
Sean Lange (5/21/2012)
toddasd (5/21/2012)
If it is already a space why change it to a space? Wouldn't this get everything:
Select ISNULL(Item, ' ') from ITEMS
Or are you trying to find values that are exactly one space?
The original query posted was 2 spaces not just a single one. Seems like an odd requirement but who knows?
I thought it looked too wide for one space, but I went cross-eyed trying to count. 😛
LOL I just copied and pasted. Way too hard to see in the little code window.
It seems that one of the suggestions made by one us must have worked. Much like the requirements, we may never know. :hehe:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply