Case sensitive issue

Question

Case sensitive issue

kutemom09

SSC-Addicted

Points: 463
More actions
July 7, 2011 at 10:53 am

#254197

I am using a REPORT to count the number of TOTALMEMBERS for each REGION ex.:
Region totalmembers
West 1234
WEST 236
My problem is that the Region is NOT case-sensitive, 'WEST' and 'West' are same But my report counts them as different (Case sensitive).can someone please help me on this.I checked the column collation and its not case sensitive.please I need some help.

Viewing 12 posts - 1 through 11 (of 11 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Try using lower() or upper() in your select clause for the region. It should then treat all of them the same in your report.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 2

The table, or database, must have a case-sensitive collation if this is what you get. As SQLRNNR mentioned, this should help:

SELECT UPPER(region)

, count(sales)

from MyTable

group by UPPER(region)

http://msdn.microsoft.com/en-us/library/ms180055.aspx

kutemom09 SSC-Addicted Points: 463 More actions · Answer 3

Jason thats what I am using for now but my Manager is so picky she wants to find what is causing it.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

kutemom09 (7/7/2011)
Jason thats what I am using for now but my Manager is so picky she wants to find what is causing it.

What generates the report? SSRS, Cognos, or is it just in SSMS?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

kutemom09 SSC-Addicted Points: 463 More actions · Answer 5

I checked if it is case-sensitive collation but it not

kutemom09 SSC-Addicted Points: 463 More actions · Answer 6

kutemom09

SSC-Addicted

Points: 463

July 7, 2011 at 11:18 am

#1349877

We use crystal reporting to genarate report

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

kutemom09 (7/7/2011)
We use crystal reporting to genarate report

Crystal reports is case sensitive. In order to get around that, you have to use the upper or lower sql functions. You can also use the uppercase or lowercase functions of Crystal Reports.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 8

If SQL is not case sensitive, you ought to be getting back just one row there. What query are you running, or how are you doing this. If Crystal is compiling lots of data from SQL and then summing it, you would need to add the UPPER or LOWER functions in Crystal.

kutemom09 SSC-Addicted Points: 463 More actions · Answer 9

Crystal is not case sensitive.I checked with my manager as he is the one who does reporting.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

kutemom09 (7/7/2011)
Crystal is not case sensitive.I checked with my manager as he is the one who does reporting.

If SQL is not CS, and you have altered the default behavior of Crystal to not be CS then you would not have this issue. Crystal is by default case sensitive and does cause many issues such as this.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 11

If you run this in SQL Server, what happens?

select distinct Region

from RegionTable

Use your own field/table names.