no case sensitive

Hi,
Does anybody know how to let the database treat my data as no case
sensitive?
For example: I have a customer type field, when I retrieve it from the
mainframe, it has "retail" or "RETAIL" as differently typed by users. But
when I run a report for customer type "Retail", I want to them to be showing
as one type on the report.
How do I do this?
Thanks,
SarahUse the lower function to convert the column to lowercase.
i.e.
Select *
from Orders
where lower(OrderType) = 'retail'
"Sarah G." <sguo@.coopervision.com> wrote in message
news:uMv8Wv#GEHA.1076@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Does anybody know how to let the database treat my data as no case
> sensitive?
> For example: I have a customer type field, when I retrieve it from the
> mainframe, it has "retail" or "RETAIL" as differently typed by users. But
> when I run a report for customer type "Retail", I want to them to be
showing
> as one type on the report.
> How do I do this?
> Thanks,
> Sarah
>|||Are you using SQL Server 2000? If so, look up the COLLATE keyword in BOL.
Here's a very simple example:
create table #a(mychar varchar(20) collate sql_latin1_general_cp1_cs_as)
go
insert #a values('aBc')
insert #a values('abC')
go
select distinct mychar from #a
go
select distinct mychar collate sql_latin1_general_cp1_ci_as from #a
go
"Sarah G." <sguo@.coopervision.com> wrote in message
news:uMv8Wv#GEHA.1076@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Does anybody know how to let the database treat my data as no case
> sensitive?
> For example: I have a customer type field, when I retrieve it from the
> mainframe, it has "retail" or "RETAIL" as differently typed by users. But
> when I run a report for customer type "Retail", I want to them to be
showing
> as one type on the report.
> How do I do this?
> Thanks,
> Sarah
>sql

no case sensitive

Hi,
Does anybody know how to let the database treat my data as no case
sensitive?
For example: I have a customer type field, when I retrieve it from the
mainframe, it has "retail" or "RETAIL" as differently typed by users. But
when I run a report for customer type "Retail", I want to them to be showing
as one type on the report.
How do I do this?
Thanks,
Sarah
Use the lower function to convert the column to lowercase.
i.e.
Select *
from Orders
where lower(OrderType) = 'retail'
"Sarah G." <sguo@.coopervision.com> wrote in message
news:uMv8Wv#GEHA.1076@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Does anybody know how to let the database treat my data as no case
> sensitive?
> For example: I have a customer type field, when I retrieve it from the
> mainframe, it has "retail" or "RETAIL" as differently typed by users. But
> when I run a report for customer type "Retail", I want to them to be
showing
> as one type on the report.
> How do I do this?
> Thanks,
> Sarah
>

No "Existing" word... How?

I am rendering an Association Rules on Report Services. How do I make to filter the word "Existing", just like example below? I want to show attribute name only.

Pneu ML Road = Existing, Sport-100 = Existing -> Road Tubo de Pneu = Existing

to

Pneu ML Road, Sport-100 -> Road Tubo de Pneu

By the way I am using this code:

SELECT NODE_DESCRIPTION,ROUND(NODE_PROBABILITY,2)AS Probabilidade,ROUND(MSOLAP_NODE_SCORE,2)AS Importancia
from [Association].CONTENT
where NODE_TYPE=8

NODE_DESCRIPTION is a string field generated by the algorithm. It contains predicates having the form "Attribute = value" which, in the case of nested table keys, translate to "Road Tubo = Existing".

The "Existing" keyword cannot be removed from the NODE_DESCRIPTION. However, it is coming from the server's string resources. That means, if you install a localized version of the server, you will get a localized version of Existing. Furthermore, the server will attempt to "localize" the Existing string to the thread locales of the client application, as long as the respective resources are available on the server side.

Now, to get rid completely of the Existing keyword, there are a few ways:

- write a server side stored procedure which: traverses the rules in the model content, extracts the node unique names for the itemsets as well as the attribute states, then generates a string which contains the attribute name but not the value (pretty much what the stored procedures used by the built-in viewer do).

Then, invoke the stored procedure with a CALL statement.

OR:

- write a very simple C# class library, containing a single function, Replace, which takes 3 strings as arguments

string Replace(string source, string oldValue, string newValue)

{

return source.Replace(oldValue, newValue);

}

Then, deploy the assembly on the server and change your query like below:

SELECT MyAssembly.Replace(NODE_DESCRIPTION, '= Existing', ''),ROUND(NODE_PROBABILITY,2)AS Probabilidade,ROUND(MSOLAP_NODE_SCORE,2)AS Importancia
from [Association].CONTENT
where NODE_TYPE=8

While not as reliable as the first solution, this is much easier to implement and will generally work correctly, as long as the only attributes in your model are the nested table keys .

|||

You did inspired me, but I found a better solution:

Just click right button on the table, click in "Expression" and change the code Fields!Regra.Value to =Replace(Fields!Regra.Value," = Existing","")

Thank you very much!

next/previous record

Hi. Is it possible in SQL query to find record previous or next in comparison with record found with clause WHERE (example of query below)? I need to find record with ProblemID less than or greater than 10. Regards Pawelek.

SELECT ProblemID
FROM dbo.tblProblems
WHERE (ProblemID = 10)

See if this article helps you:

http://www.xaprb.com/blog/2006/04/28/how-to-find-next-and-previous-records-in-sql/

|||

SELECT ProblemID
FROM dbo.tblProblems
WHERE (ProblemID <> 10)

HTH

Next day occurance in a week

Hi
I have a table that stores a day number and a time and I need to be
able to get the next occurance of the wday.
For example in DB if I have:
4 as dayNo (Thursday) and 08:34 as Time and I want to return
18/Aug/2005 08:34
5 as dayNo (Friday) and 15:00 as Time I want to return 19/Aug/2005
15:00
5 as DayNo (Friday) and 23:45 as Time I want to return 12/Aug/2005
23:45 (as the current time is 19:30)
Can anyone help me with a UDF?
Thankshttp://www.aspfaq.com/show.asp?id=2519
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Rippo" <info@.rippo.co.uk> wrote in message
news:1123871644.325175.149000@.z14g2000cwz.googlegroups.com...
> Hi
> I have a table that stores a day number and a time and I need to be
> able to get the next occurance of the wday.
> For example in DB if I have:
> 4 as dayNo (Thursday) and 08:34 as Time and I want to return
> 18/Aug/2005 08:34
> 5 as dayNo (Friday) and 15:00 as Time I want to return 19/Aug/2005
> 15:00
> 5 as DayNo (Friday) and 23:45 as Time I want to return 12/Aug/2005
> 23:45 (as the current time is 19:30)
> Can anyone help me with a UDF?
> Thanks
>|||Perfect!
Thanks, Rippo