Case sensitive

Hello,
SQL server 2000, database Test1, Collation name Czech_CS_AS
I have this table:
aa F1 aa
bb f1 bb
cc F1 cc
***
SELECT TOP 8 Table1.*
FROM dbo.Table1 Table1
WHERE Table1.rozmer >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
Result:
aa F1 aa
cc F1 cc
OK
***
but next select - I need concate column WHERE Table1.rozmer+Table1.nazev
SELECT TOP 8 Table1.*
FROM dbo.Table1 Table1
WHERE Table1.rozmer+Table1.nazev >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
Return all rows - also row with rozmer = 'f1' :
bb f1 bb
aa F1 aa
cc F1 cc
Thank You for help
LudekHi Ludek
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data that would be useful in answering your question.
"Ludek" wrote:
> Hello,
> SQL server 2000, database Test1, Collation name Czech_CS_AS
> I have this table:
> aa F1 aa
> bb f1 bb
> cc F1 cc
> ***
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Result:
> aa F1 aa
> cc F1 cc
> OK
> ***
> but next select - I need concate column WHERE Table1.rozmer+Table1.nazev
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer+Table1.nazev >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Return all rows - also row with rozmer = 'f1' :
> bb f1 bb
> aa F1 aa
> cc F1 cc
> Thank You for help
> Ludek
>
For example:
USE TEMPDB
GO
CREATE TABLE Test1 ( nazev varchar(10) COLLATE Czech_CS_AS,
rozmer varchar(10) COLLATE Czech_CS_AS,
other varchar(10) COLLATE Czech_CS_AS )
GO
INSERT INTO Test1( nazev, rozmer, other )
SELECT 'aa', 'F1', 'aa'
UNION ALL SELECT 'bb', 'f1', 'bb'
UNION ALL SELECT 'cc', 'F1', 'cc'
Then your query:
SELECT TOP 8 Table1.*
FROM dbo.Test1 Table1
WHERE Table1.rozmer >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
/*
nazev rozmer other
-- -- --
aa F1 aa
cc F1 cc
(2 row(s) affected)
*/
As you expected:
And the second query:
SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
FROM dbo.Test1 Table1
WHERE Table1.rozmer+Table1.nazev >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
/*
nazev rozmer other Concatenation
-- -- -- --
bb f1 bb f1bb
aa F1 aa F1aa
cc F1 cc F1cc
(3 row(s) affected)
*/
If you only wanted those values where rozmer was 'F1' then you could have
written:
SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
FROM dbo.Test1 Table1
WHERE Table1.rozmer+Table1.nazev LIKE 'F1%'
ORDER BY Table1.rozmer, Table1.nazev
/*
nazev rozmer other Concatenation
-- -- -- --
aa F1 aa F1aa
cc F1 cc F1cc
(2 row(s) affected)
*/
If this is actually because you are not expecting f1bb, then you will need
to know that f1bb is greater than F1, to show this you may want to look at
their actual ordering e.g.
SELECT *
FROM (
SELECT rozmer
FROM dbo.Test1
UNION ALL
SELECT rozmer+nazev
FROM dbo.Test1
) A
ORDER BY rozmer
/*
rozmer
--
f1
F1
F1
F1aa
f1bb
F1cc
(6 row(s) affected)
*/
Which shows F1cc > f1bb > F1aa > F1 > f1
You may be confussion the binary ordering which would be
SELECT *
FROM (
SELECT rozmer COLLATE Czech_BIN AS rozmer
FROM dbo.Test1
UNION ALL
SELECT rozmer+nazev COLLATE Czech_BIN
FROM dbo.Test1
) A
ORDER BY rozmer
/*
rozmer
--
F1
F1
F1aa
F1cc
f1
f1bb
(6 row(s) affected)
*/
Which shows f1bb > f1 > F1cc > F1aa > F1
and so you could do a query such as
SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
FROM dbo.Test1 Table1
WHERE Table1.rozmer+Table1.nazev COLLATE Czech_BIN >= 'F1'
AND Table1.rozmer+Table1.nazev COLLATE Czech_BIN <= 'F1zz'
ORDER BY Table1.rozmer, Table1.nazev
John|||On May 22, 11:16 am, Ludek <L...@.discussions.microsoft.com> wrote:
> Hello,
> SQL server 2000, database Test1, Collation name Czech_CS_AS
> I have this table:
> aa F1 aa
> bb f1 bb
> cc F1 cc
> ***
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Result:
> aa F1 aa
> cc F1 cc
> OK
> ***
> but next select - I need concate column WHERE Table1.rozmer+Table1.nazev
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer+Table1.nazev >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Return all rows - also row with rozmer = 'f1' :
> bb f1 bb
> aa F1 aa
> cc F1 cc
> Thank You for help
> Ludek
You can use UPPER or LOWER functions|||Hello John,
thank You for help.
I must perhaps change application strategy. I have master form with grid
with recordsource view - this view have 8 records. Next 8 or previous 8
record I get from database with requery view. But in case-sensitive I can not
this use - F1cc > f1bb > F1aa > F1 > f1. I must concate 5 field for unique
key. Create any cascade request is complicated. I will create view with this
5 fields from all database + detailed view for one record. But database have
30000 rows.
Is this way?
Thank You Ludek
"John Bell" wrote:
> Hi Ludek
> See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
> sample data that would be useful in answering your question.
> "Ludek" wrote:
> > Hello,
> > SQL server 2000, database Test1, Collation name Czech_CS_AS
> > I have this table:
> > aa F1 aa
> > bb f1 bb
> > cc F1 cc
> > ***
> > SELECT TOP 8 Table1.*
> > FROM dbo.Table1 Table1
> > WHERE Table1.rozmer >= 'F1'
> > ORDER BY Table1.rozmer, Table1.nazev
> >
> > Result:
> > aa F1 aa
> > cc F1 cc
> > OK
> > ***
> > but next select - I need concate column WHERE Table1.rozmer+Table1.nazev
> > SELECT TOP 8 Table1.*
> > FROM dbo.Table1 Table1
> > WHERE Table1.rozmer+Table1.nazev >= 'F1'
> > ORDER BY Table1.rozmer, Table1.nazev
> >
> > Return all rows - also row with rozmer = 'f1' :
> > bb f1 bb
> > aa F1 aa
> > cc F1 cc
> >
> > Thank You for help
> > Ludek
> >
> For example:
> USE TEMPDB
> GO
> CREATE TABLE Test1 ( nazev varchar(10) COLLATE Czech_CS_AS,
> rozmer varchar(10) COLLATE Czech_CS_AS,
> other varchar(10) COLLATE Czech_CS_AS )
> GO
> INSERT INTO Test1( nazev, rozmer, other )
> SELECT 'aa', 'F1', 'aa'
> UNION ALL SELECT 'bb', 'f1', 'bb'
> UNION ALL SELECT 'cc', 'F1', 'cc'
> Then your query:
> SELECT TOP 8 Table1.*
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> /*
> nazev rozmer other
> -- -- --
> aa F1 aa
> cc F1 cc
> (2 row(s) affected)
> */
> As you expected:
> And the second query:
> SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer+Table1.nazev >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> /*
> nazev rozmer other Concatenation
> -- -- -- --
> bb f1 bb f1bb
> aa F1 aa F1aa
> cc F1 cc F1cc
> (3 row(s) affected)
> */
> If you only wanted those values where rozmer was 'F1' then you could have
> written:
> SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer+Table1.nazev LIKE 'F1%'
> ORDER BY Table1.rozmer, Table1.nazev
> /*
> nazev rozmer other Concatenation
> -- -- -- --
> aa F1 aa F1aa
> cc F1 cc F1cc
> (2 row(s) affected)
> */
> If this is actually because you are not expecting f1bb, then you will need
> to know that f1bb is greater than F1, to show this you may want to look at
> their actual ordering e.g.
> SELECT *
> FROM (
> SELECT rozmer
> FROM dbo.Test1
> UNION ALL
> SELECT rozmer+nazev
> FROM dbo.Test1
> ) A
> ORDER BY rozmer
> /*
> rozmer
> --
> f1
> F1
> F1
> F1aa
> f1bb
> F1cc
> (6 row(s) affected)
> */
> Which shows F1cc > f1bb > F1aa > F1 > f1
> You may be confussion the binary ordering which would be
> SELECT *
> FROM (
> SELECT rozmer COLLATE Czech_BIN AS rozmer
> FROM dbo.Test1
> UNION ALL
> SELECT rozmer+nazev COLLATE Czech_BIN
> FROM dbo.Test1
> ) A
> ORDER BY rozmer
> /*
> rozmer
> --
> F1
> F1
> F1aa
> F1cc
> f1
> f1bb
> (6 row(s) affected)
> */
> Which shows f1bb > f1 > F1cc > F1aa > F1
> and so you could do a query such as
> SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer+Table1.nazev COLLATE Czech_BIN >= 'F1'
> AND Table1.rozmer+Table1.nazev COLLATE Czech_BIN <= 'F1zz'
> ORDER BY Table1.rozmer, Table1.nazev
> John
>|||Hi Ludek
"Ludek" wrote:
> Hello John,
> thank You for help.
> I must perhaps change application strategy. I have master form with grid
> with recordsource view - this view have 8 records. Next 8 or previous 8
> record I get from database with requery view. But in case-sensitive I can not
> this use - F1cc > f1bb > F1aa > F1 > f1. I must concate 5 field for unique
> key. Create any cascade request is complicated. I will create view with this
> 5 fields from all database + detailed view for one record. But database have
> 30000 rows.
> Is this way?
> Thank You Ludek
The result you have are as expected, although it doesn't seem to be the one
you want! If you posted sample data and expected results it may be clearer
how you should write your query, for instance why you need to test the
concetenated string and not just your rozmer column as per your first example?
Johnsql

Case sensitive

Hello,
SQL server 2000, database Test1, Collation name Czech_CS_AS
I have this table:
aa F1 aa
bb f1 bb
cc F1 cc
***
SELECT TOP 8 Table1.*
FROM dbo.Table1 Table1
WHERE Table1.rozmer >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
Result:
aa F1 aa
cc F1 cc
OK
***
but next select - I need concate column WHERE Table1.rozmer+Table1.nazev
SELECT TOP 8 Table1.*
FROM dbo.Table1 Table1
WHERE Table1.rozmer+Table1.nazev >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
Return all rows - also row with rozmer = 'f1' :
bb f1 bb
aa F1 aa
cc F1 cc
Thank You for help
Ludek
Hi Ludek
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data that would be useful in answering your question.
"Ludek" wrote:

> Hello,
> SQL server 2000, database Test1, Collation name Czech_CS_AS
> I have this table:
> aa F1 aa
> bb f1 bb
> cc F1 cc
> ***
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Result:
> aa F1 aa
> cc F1 cc
> OK
> ***
> but next select - I need concate column WHERE Table1.rozmer+Table1.nazev
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer+Table1.nazev >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Return all rows - also row with rozmer = 'f1' :
> bb f1 bb
> aa F1 aa
> cc F1 cc
> Thank You for help
> Ludek
>
For example:
USE TEMPDB
GO
CREATE TABLE Test1 ( nazev varchar(10) COLLATE Czech_CS_AS,
rozmer varchar(10) COLLATE Czech_CS_AS,
other varchar(10) COLLATE Czech_CS_AS )
GO
INSERT INTO Test1( nazev, rozmer, other )
SELECT 'aa', 'F1', 'aa'
UNION ALL SELECT 'bb', 'f1', 'bb'
UNION ALL SELECT 'cc', 'F1', 'cc'
Then your query:
SELECT TOP 8 Table1.*
FROM dbo.Test1 Table1
WHERE Table1.rozmer >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
/*
nazev rozmer other
-- -- --
aa F1 aa
cc F1 cc
(2 row(s) affected)
*/
As you expected:
And the second query:
SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
FROM dbo.Test1 Table1
WHERE Table1.rozmer+Table1.nazev >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
/*
nazev rozmer other Concatenation
-- -- -- --
bb f1 bb f1bb
aa F1 aa F1aa
cc F1 cc F1cc
(3 row(s) affected)
*/
If you only wanted those values where rozmer was 'F1' then you could have
written:
SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
FROM dbo.Test1 Table1
WHERE Table1.rozmer+Table1.nazev LIKE 'F1%'
ORDER BY Table1.rozmer, Table1.nazev
/*
nazev rozmer other Concatenation
-- -- -- --
aa F1 aa F1aa
cc F1 cc F1cc
(2 row(s) affected)
*/
If this is actually because you are not expecting f1bb, then you will need
to know that f1bb is greater than F1, to show this you may want to look at
their actual ordering e.g.
SELECT *
FROM (
SELECT rozmer
FROM dbo.Test1
UNION ALL
SELECT rozmer+nazev
FROM dbo.Test1
) A
ORDER BY rozmer
/*
rozmer
f1
F1
F1
F1aa
f1bb
F1cc
(6 row(s) affected)
*/
Which shows F1cc > f1bb > F1aa > F1 > f1
You may be confussion the binary ordering which would be
SELECT *
FROM (
SELECT rozmer COLLATE Czech_BIN AS rozmer
FROM dbo.Test1
UNION ALL
SELECT rozmer+nazev COLLATE Czech_BIN
FROM dbo.Test1
) A
ORDER BY rozmer
/*
rozmer
F1
F1
F1aa
F1cc
f1
f1bb
(6 row(s) affected)
*/
Which shows f1bb > f1 > F1cc > F1aa > F1
and so you could do a query such as
SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
FROM dbo.Test1 Table1
WHERE Table1.rozmer+Table1.nazev COLLATE Czech_BIN >= 'F1'
AND Table1.rozmer+Table1.nazev COLLATE Czech_BIN <= 'F1zz'
ORDER BY Table1.rozmer, Table1.nazev
John
|||On May 22, 11:16 am, Ludek <L...@.discussions.microsoft.com> wrote:
> Hello,
> SQL server 2000, database Test1, Collation name Czech_CS_AS
> I have this table:
> aa F1 aa
> bb f1 bb
> cc F1 cc
> ***
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Result:
> aa F1 aa
> cc F1 cc
> OK
> ***
> but next select - I need concate column WHERE Table1.rozmer+Table1.nazev
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer+Table1.nazev >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Return all rows - also row with rozmer = 'f1' :
> bb f1 bb
> aa F1 aa
> cc F1 cc
> Thank You for help
> Ludek
You can use UPPER or LOWER functions
|||Hello John,
thank You for help.
I must perhaps change application strategy. I have master form with grid
with recordsource view - this view have 8 records. Next 8 or previous 8
record I get from database with requery view. But in case-sensitive I can not
this use - F1cc > f1bb > F1aa > F1 > f1. I must concate 5 field for unique
key. Create any cascade request is complicated. I will create view with this
5 fields from all database + detailed view for one record. But database have
30000 rows.
Is this way?
Thank You Ludek
"John Bell" wrote:

> Hi Ludek
> See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
> sample data that would be useful in answering your question.
> "Ludek" wrote:
> For example:
> USE TEMPDB
> GO
> CREATE TABLE Test1 ( nazev varchar(10) COLLATE Czech_CS_AS,
> rozmer varchar(10) COLLATE Czech_CS_AS,
> other varchar(10) COLLATE Czech_CS_AS )
> GO
> INSERT INTO Test1( nazev, rozmer, other )
> SELECT 'aa', 'F1', 'aa'
> UNION ALL SELECT 'bb', 'f1', 'bb'
> UNION ALL SELECT 'cc', 'F1', 'cc'
> Then your query:
> SELECT TOP 8 Table1.*
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> /*
> nazev rozmer other
> -- -- --
> aa F1 aa
> cc F1 cc
> (2 row(s) affected)
> */
> As you expected:
> And the second query:
> SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer+Table1.nazev >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> /*
> nazev rozmer other Concatenation
> -- -- -- --
> bb f1 bb f1bb
> aa F1 aa F1aa
> cc F1 cc F1cc
> (3 row(s) affected)
> */
> If you only wanted those values where rozmer was 'F1' then you could have
> written:
> SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer+Table1.nazev LIKE 'F1%'
> ORDER BY Table1.rozmer, Table1.nazev
> /*
> nazev rozmer other Concatenation
> -- -- -- --
> aa F1 aa F1aa
> cc F1 cc F1cc
> (2 row(s) affected)
> */
> If this is actually because you are not expecting f1bb, then you will need
> to know that f1bb is greater than F1, to show this you may want to look at
> their actual ordering e.g.
> SELECT *
> FROM (
> SELECT rozmer
> FROM dbo.Test1
> UNION ALL
> SELECT rozmer+nazev
> FROM dbo.Test1
> ) A
> ORDER BY rozmer
> /*
> rozmer
> --
> f1
> F1
> F1
> F1aa
> f1bb
> F1cc
> (6 row(s) affected)
> */
> Which shows F1cc > f1bb > F1aa > F1 > f1
> You may be confussion the binary ordering which would be
> SELECT *
> FROM (
> SELECT rozmer COLLATE Czech_BIN AS rozmer
> FROM dbo.Test1
> UNION ALL
> SELECT rozmer+nazev COLLATE Czech_BIN
> FROM dbo.Test1
> ) A
> ORDER BY rozmer
> /*
> rozmer
> --
> F1
> F1
> F1aa
> F1cc
> f1
> f1bb
> (6 row(s) affected)
> */
> Which shows f1bb > f1 > F1cc > F1aa > F1
> and so you could do a query such as
> SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer+Table1.nazev COLLATE Czech_BIN >= 'F1'
> AND Table1.rozmer+Table1.nazev COLLATE Czech_BIN <= 'F1zz'
> ORDER BY Table1.rozmer, Table1.nazev
> John
>
|||Hi Ludek
"Ludek" wrote:

> Hello John,
> thank You for help.
> I must perhaps change application strategy. I have master form with grid
> with recordsource view - this view have 8 records. Next 8 or previous 8
> record I get from database with requery view. But in case-sensitive I can not
> this use - F1cc > f1bb > F1aa > F1 > f1. I must concate 5 field for unique
> key. Create any cascade request is complicated. I will create view with this
> 5 fields from all database + detailed view for one record. But database have
> 30000 rows.
> Is this way?
> Thank You Ludek
The result you have are as expected, although it doesn't seem to be the one
you want! If you posted sample data and expected results it may be clearer
how you should write your query, for instance why you need to test the
concetenated string and not just your rozmer column as per your first example?
John

Case sensitive

Hello,
SQL server 2000, database Test1, Collation name Czech_CS_AS
I have this table:
aa F1 aa
bb f1 bb
cc F1 cc
***
SELECT TOP 8 Table1.*
FROM dbo.Table1 Table1
WHERE Table1.rozmer >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
Result:
aa F1 aa
cc F1 cc
OK
***
but next select - I need concate column WHERE Table1.rozmer+Table1.nazev
SELECT TOP 8 Table1.*
FROM dbo.Table1 Table1
WHERE Table1.rozmer+Table1.nazev >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
Return all rows - also row with rozmer = 'f1' :
bb f1 bb
aa F1 aa
cc F1 cc
Thank You for help
LudekHi Ludek
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data that would be useful in answering your question.
"Ludek" wrote:

> Hello,
> SQL server 2000, database Test1, Collation name Czech_CS_AS
> I have this table:
> aa F1 aa
> bb f1 bb
> cc F1 cc
> ***
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Result:
> aa F1 aa
> cc F1 cc
> OK
> ***
> but next select - I need concate column WHERE Table1.rozmer+Table1.nazev
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer+Table1.nazev >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Return all rows - also row with rozmer = 'f1' :
> bb f1 bb
> aa F1 aa
> cc F1 cc
> Thank You for help
> Ludek
>
For example:
USE TEMPDB
GO
CREATE TABLE Test1 ( nazev varchar(10) COLLATE Czech_CS_AS,
rozmer varchar(10) COLLATE Czech_CS_AS,
other varchar(10) COLLATE Czech_CS_AS )
GO
INSERT INTO Test1( nazev, rozmer, other )
SELECT 'aa', 'F1', 'aa'
UNION ALL SELECT 'bb', 'f1', 'bb'
UNION ALL SELECT 'cc', 'F1', 'cc'
Then your query:
SELECT TOP 8 Table1.*
FROM dbo.Test1 Table1
WHERE Table1.rozmer >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
/*
nazev rozmer other
-- -- --
aa F1 aa
cc F1 cc
(2 row(s) affected)
*/
As you expected:
And the second query:
SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
FROM dbo.Test1 Table1
WHERE Table1.rozmer+Table1.nazev >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
/*
nazev rozmer other Concatenation
-- -- -- --
bb f1 bb f1bb
aa F1 aa F1aa
cc F1 cc F1cc
(3 row(s) affected)
*/
If you only wanted those values where rozmer was 'F1' then you could have
written:
SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
FROM dbo.Test1 Table1
WHERE Table1.rozmer+Table1.nazev LIKE 'F1%'
ORDER BY Table1.rozmer, Table1.nazev
/*
nazev rozmer other Concatenation
-- -- -- --
aa F1 aa F1aa
cc F1 cc F1cc
(2 row(s) affected)
*/
If this is actually because you are not expecting f1bb, then you will need
to know that f1bb is greater than F1, to show this you may want to look at
their actual ordering e.g.
SELECT *
FROM (
SELECT rozmer
FROM dbo.Test1
UNION ALL
SELECT rozmer+nazev
FROM dbo.Test1
) A
ORDER BY rozmer
/*
rozmer
--
f1
F1
F1
F1aa
f1bb
F1cc
(6 row(s) affected)
*/
Which shows F1cc > f1bb > F1aa > F1 > f1
You may be confussion the binary ordering which would be
SELECT *
FROM (
SELECT rozmer COLLATE Czech_BIN AS rozmer
FROM dbo.Test1
UNION ALL
SELECT rozmer+nazev COLLATE Czech_BIN
FROM dbo.Test1
) A
ORDER BY rozmer
/*
rozmer
--
F1
F1
F1aa
F1cc
f1
f1bb
(6 row(s) affected)
*/
Which shows f1bb > f1 > F1cc > F1aa > F1
and so you could do a query such as
SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
FROM dbo.Test1 Table1
WHERE Table1.rozmer+Table1.nazev COLLATE Czech_BIN >= 'F1'
AND Table1.rozmer+Table1.nazev COLLATE Czech_BIN <= 'F1zz'
ORDER BY Table1.rozmer, Table1.nazev
John|||On May 22, 11:16 am, Ludek <L...@.discussions.microsoft.com> wrote:
> Hello,
> SQL server 2000, database Test1, Collation name Czech_CS_AS
> I have this table:
> aa F1 aa
> bb f1 bb
> cc F1 cc
> ***
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Result:
> aa F1 aa
> cc F1 cc
> OK
> ***
> but next select - I need concate column WHERE Table1.rozmer+Table1.nazev
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer+Table1.nazev >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Return all rows - also row with rozmer = 'f1' :
> bb f1 bb
> aa F1 aa
> cc F1 cc
> Thank You for help
> Ludek
You can use UPPER or LOWER functions|||Hello John,
thank You for help.
I must perhaps change application strategy. I have master form with grid
with recordsource view - this view have 8 records. Next 8 or previous 8
record I get from database with requery view. But in case-sensitive I can no
t
this use - F1cc > f1bb > F1aa > F1 > f1. I must concate 5 field for unique
key. Create any cascade request is complicated. I will create view with this
5 fields from all database + detailed view for one record. But database have
30000 rows.
Is this way?
Thank You Ludek
"John Bell" wrote:

> Hi Ludek
> See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
> sample data that would be useful in answering your question.
> "Ludek" wrote:
>
> For example:
> USE TEMPDB
> GO
> CREATE TABLE Test1 ( nazev varchar(10) COLLATE Czech_CS_AS,
> rozmer varchar(10) COLLATE Czech_CS_AS,
> other varchar(10) COLLATE Czech_CS_AS )
> GO
> INSERT INTO Test1( nazev, rozmer, other )
> SELECT 'aa', 'F1', 'aa'
> UNION ALL SELECT 'bb', 'f1', 'bb'
> UNION ALL SELECT 'cc', 'F1', 'cc'
> Then your query:
> SELECT TOP 8 Table1.*
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> /*
> nazev rozmer other
> -- -- --
> aa F1 aa
> cc F1 cc
> (2 row(s) affected)
> */
> As you expected:
> And the second query:
> SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer+Table1.nazev >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> /*
> nazev rozmer other Concatenation
> -- -- -- --
> bb f1 bb f1bb
> aa F1 aa F1aa
> cc F1 cc F1cc
> (3 row(s) affected)
> */
> If you only wanted those values where rozmer was 'F1' then you could have
> written:
> SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer+Table1.nazev LIKE 'F1%'
> ORDER BY Table1.rozmer, Table1.nazev
> /*
> nazev rozmer other Concatenation
> -- -- -- --
> aa F1 aa F1aa
> cc F1 cc F1cc
> (2 row(s) affected)
> */
> If this is actually because you are not expecting f1bb, then you will need
> to know that f1bb is greater than F1, to show this you may want to look at
> their actual ordering e.g.
> SELECT *
> FROM (
> SELECT rozmer
> FROM dbo.Test1
> UNION ALL
> SELECT rozmer+nazev
> FROM dbo.Test1
> ) A
> ORDER BY rozmer
> /*
> rozmer
> --
> f1
> F1
> F1
> F1aa
> f1bb
> F1cc
> (6 row(s) affected)
> */
> Which shows F1cc > f1bb > F1aa > F1 > f1
> You may be confussion the binary ordering which would be
> SELECT *
> FROM (
> SELECT rozmer COLLATE Czech_BIN AS rozmer
> FROM dbo.Test1
> UNION ALL
> SELECT rozmer+nazev COLLATE Czech_BIN
> FROM dbo.Test1
> ) A
> ORDER BY rozmer
> /*
> rozmer
> --
> F1
> F1
> F1aa
> F1cc
> f1
> f1bb
> (6 row(s) affected)
> */
> Which shows f1bb > f1 > F1cc > F1aa > F1
> and so you could do a query such as
> SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer+Table1.nazev COLLATE Czech_BIN >= 'F1'
> AND Table1.rozmer+Table1.nazev COLLATE Czech_BIN <= 'F1zz'
> ORDER BY Table1.rozmer, Table1.nazev
> John
>|||Hi Ludek
"Ludek" wrote:

> Hello John,
> thank You for help.
> I must perhaps change application strategy. I have master form with grid
> with recordsource view - this view have 8 records. Next 8 or previous 8
> record I get from database with requery view. But in case-sensitive I can
not
> this use - F1cc > f1bb > F1aa > F1 > f1. I must concate 5 field for unique
> key. Create any cascade request is complicated. I will create view with th
is
> 5 fields from all database + detailed view for one record. But database ha
ve
> 30000 rows.
> Is this way?
> Thank You Ludek
The result you have are as expected, although it doesn't seem to be the one
you want! If you posted sample data and expected results it may be clearer
how you should write your query, for instance why you need to test the
concetenated string and not just your rozmer column as per your first exampl
e?
John

CASE JOIN question

I woul like to join table with other table only in case when @.myParam is
null.
Example:
something like that:
...table1 T1
case when @.myParam is null then
INNER JOIN table2 T2 ON T1.zone=T2.zone
else end
.....
Is that possible?
Thnk you,
Simonsimon
Use pubs
SELECT a.au_lname, a.au_fname, a.address,
t.title, t.type
FROM authors a INNER JOIN
titleauthor ta ON ta.au_id = a.au_id INNER JOIN
titles t ON t.title_id = ta.title_id
INNER JOIN publishers p on t.pub_id =
CASE WHEN t.type = 'Business' THEN p.pub_id ELSE null END
INNER JOIN stores s on s.stor_id =
CASE WHEN t.type = 'Popular_comp' THEN t.title_id ELSE null END
"simon" <simon.zupan@.stud-moderna.si> wrote in message
news:OwhFXArOFHA.1476@.TK2MSFTNGP09.phx.gbl...
> I woul like to join table with other table only in case when @.myParam is
> null.
> Example:
> something like that:
> ...table1 T1
> case when @.myParam is null then
> INNER JOIN table2 T2 ON T1.zone=T2.zone
> else end
> .....
> Is that possible?
> Thnk you,
> Simon
>|||Hi Uri,
thank you for your answer.
I have little different example ( also instead of "=" I have like) .
If myParam is not null, my table2 is empty.
So inner join:
table1 T1 INNER JOIN table2 T2 ON T1.zone like
CASE when @.myParam is null then T2.zone else NULL END
will not work correctly.
I tried like this:
table1 T1 INNER JOIN table2 T2 ON 1=
(CASE when @.myParam is not null then 1 else case when T1.zone like
'%'+T2.zone+'%' then 1 else 0 end end )
It works but it's too slow.
Any idea?
Regards,
Simon
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e3FBGDrOFHA.244@.TK2MSFTNGP12.phx.gbl...
> simon
> Use pubs
> SELECT a.au_lname, a.au_fname, a.address,
> t.title, t.type
> FROM authors a INNER JOIN
> titleauthor ta ON ta.au_id = a.au_id INNER JOIN
> titles t ON t.title_id = ta.title_id
> INNER JOIN publishers p on t.pub_id =
> CASE WHEN t.type = 'Business' THEN p.pub_id ELSE null END
> INNER JOIN stores s on s.stor_id =
> CASE WHEN t.type = 'Popular_comp' THEN t.title_id ELSE null END
>
> "simon" <simon.zupan@.stud-moderna.si> wrote in message
> news:OwhFXArOFHA.1476@.TK2MSFTNGP09.phx.gbl...
>|||Simon
Try this one (untested)
table1 T1 INNER JOIN table2 T2 ON 1=
CASE
WHEN EXISTS (
SELECT * FROM Table2
WHERE T1.zone like '%'+T2.zone+'%')
THEN 1
ELSE 0
END
"simon" <simon.zupan@.stud-moderna.si> wrote in message
news:%23xd$3RrOFHA.2144@.TK2MSFTNGP09.phx.gbl...
> Hi Uri,
> thank you for your answer.
> I have little different example ( also instead of "=" I have like) .
> If myParam is not null, my table2 is empty.
> So inner join:
> table1 T1 INNER JOIN table2 T2 ON T1.zone like
> CASE when @.myParam is null then T2.zone else NULL END
> will not work correctly.
> I tried like this:
> table1 T1 INNER JOIN table2 T2 ON 1=
> (CASE when @.myParam is not null then 1 else case when T1.zone like
> '%'+T2.zone+'%' then 1 else 0 end end )
> It works but it's too slow.
> Any idea?
> Regards,
> Simon
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e3FBGDrOFHA.244@.TK2MSFTNGP12.phx.gbl...
is
>

Cascaded SELECT for multiple fields

Consider 2 tables Tabe1, Table2 which both have int fields fld1, fld2.
I can do a Cascaded Select on 1 field:
SELECT fld1 FROM Table1 WHERE fld1 IN (SELECT fld1 FROM Table2)
or
SELECT fld1 FROM Table1 WHERE fld1 NOT IN (SELECT fld1 FROM Table2)
and I can rewrite it as a INNER JOIN, no problem. So far so good.
But now what happens when it is _BOTH_ pair of fields I wish to check
whether they exist/not exist in Table 2?
For Cascaded Selects, I am stuffed. There is no (at least I am unaware)
SELECT fld1, fld2 FROM Table1 WHERE fld1,fld2 IN (SELECT fld1, fld2 FROM
Table2)
I have to write this is a INNER JOIN which will work. At least there is a
solution here.
But consider the 4th case. I am stuffed here regardless of doing Cascaded
Select or Join. You want to say
SELECT fld1, fld2 FROM Table1 WHERE fld1,fld2 NOT IN (SELECT fld1, fld2 FROM
Table2)
And I cannot rewrite this as an inner join because rows are only considered
when both fields, _jointly_ are absent from Table2.
There must be a solution here for multiple fields.
In real life I do have 2 tables. And I can gurantee that fld1 is present on
both tables. But I am interested in listing the pairs of fields (fld1, fld2)
present on one table but absent on the other.
Question1) Can it be done with Cascade Selects? How?
Question2) Can it be done with Joins? How?
Thanks
Stephen HoweThis should work:
SELECT Table1.fld1, Table1.fld2
FROM Table1 Table1
LEFT JOIN Table2 Table2
ON Table1.fld1 = Table2.fld1 AND Table1.fld2 = Table2.fld2
WHERE Table2.fld1 IS NULL
If the pair exists in table2, then Table2.fld1 won't be NULL.|||> This should work:
> SELECT Table1.fld1, Table1.fld2
> FROM Table1 Table1
> LEFT JOIN Table2 Table2
> ON Table1.fld1 = Table2.fld1 AND Table1.fld2 = Table2.fld2
> WHERE Table2.fld1 IS NULL
> If the pair exists in table2, then Table2.fld1 won't be NULL.
I was not clear in my labeling of cases. They are
Case 1: SELECTing on single fields present on Table 1 that should be
present on Table 2
Case 2: SELECTing on single fields present on Table 1 that should not be
present on Table 2
Case 3: SELECTing on multiple fields present on Table 1 that should be
present on Table 2
Case 4: SELECTing on multiple fields present on Table 1 that should not be
present on Table 2
As far as I know, JOINs can handle Cases 1-3 but not case 4
As far as I know, cascaded Selects can handle Cases 1-2 but not cases 3-4
Hrrrmmm after examination, you are right. Thanks
Stephen Howe|||Does this do what you want?:
select t1.fld1, t1.fld2
from Table1 t1
left outer join Table2 t2
on t1.fld1 = t2.fld1
and t1.fld2 = t2.fld2
where t2.fld1 is null;
HTH
Vern
"Stephen Howe" wrote:

> Consider 2 tables Tabe1, Table2 which both have int fields fld1, fld2.
> I can do a Cascaded Select on 1 field:
> SELECT fld1 FROM Table1 WHERE fld1 IN (SELECT fld1 FROM Table2)
> or
> SELECT fld1 FROM Table1 WHERE fld1 NOT IN (SELECT fld1 FROM Table2)
> and I can rewrite it as a INNER JOIN, no problem. So far so good.
> But now what happens when it is _BOTH_ pair of fields I wish to check
> whether they exist/not exist in Table 2?
> For Cascaded Selects, I am stuffed. There is no (at least I am unaware)
> SELECT fld1, fld2 FROM Table1 WHERE fld1,fld2 IN (SELECT fld1, fld2 FROM
> Table2)
> I have to write this is a INNER JOIN which will work. At least there is a
> solution here.
> But consider the 4th case. I am stuffed here regardless of doing Cascaded
> Select or Join. You want to say
> SELECT fld1, fld2 FROM Table1 WHERE fld1,fld2 NOT IN (SELECT fld1, fld2 FR
OM
> Table2)
> And I cannot rewrite this as an inner join because rows are only considere
d
> when both fields, _jointly_ are absent from Table2.
> There must be a solution here for multiple fields.
> In real life I do have 2 tables. And I can gurantee that fld1 is present o
n
> both tables. But I am interested in listing the pairs of fields (fld1, fld
2)
> present on one table but absent on the other.
> Question1) Can it be done with Cascade Selects? How?
> Question2) Can it be done with Joins? How?
> Thanks
> Stephen Howe
>
>
>|||Stephen Howe (stephenPOINThoweATtns-globalPOINTcom) writes:
> I can do a Cascaded Select on 1 field:
> SELECT fld1 FROM Table1 WHERE fld1 IN (SELECT fld1 FROM Table2)
> or
> SELECT fld1 FROM Table1 WHERE fld1 NOT IN (SELECT fld1 FROM Table2)
> and I can rewrite it as a INNER JOIN, no problem. So far so good.
> But now what happens when it is _BOTH_ pair of fields I wish to check
> whether they exist/not exist in Table 2?
> For Cascaded Selects, I am stuffed. There is no (at least I am unaware)
> SELECT fld1, fld2 FROM Table1 WHERE fld1,fld2 IN (SELECT fld1, fld2 FROM
> Table2)
There is in ANSI SQL I believe, but ont implemented in SQL Server.
Anyway, all you need to learn is the EXISTS clause:
SELECT fld1, fld2
FROM Table1 a
WHERE EXISTS (SELECT *
FROM Table2 b
WHERE a.fld1 = b.fld1
AND a.fld2 = b.fld2)
For the corresponding to NOT IN, use NOT EXISTS.
EXISTS/NOT EXISTS is in my opinion superior to IN/NOT IN. There is a
trap with nullable columns that you easily can fall into with NOT IN.
Does not happen with NOT EXISTS.
Performancewise, IN/NOT IN queries are usually rewritten internally
to EXISTS /NOT EXISTS, so there is no difference.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> Performancewise, IN/NOT IN queries are usually rewritten internally
> to EXISTS /NOT EXISTS, so there is no difference.
Thanks Erland and everyone else.
My mind has been opened.
Stephen Howe

CAPTURE DML FROM TRIGGER

Hi All!

SQL Server 2000

I've situation where I've to capture a DML executing against let say Table1 and later may at the end of the day or week I want to be able to extract all the DML executed against Table1 and execute them against similar tables on different sql server to synchronize the data. I don't want to use profiler as this is quite expensive resource for my problem niether can use any third party tool.

Is it possible to capture sql statement in the trigger?

I hope I made my question clear. Urgent help will be highly appreciated.

--Sohail.

There is no way to obtain this information accurately in SQL Server right now. So you will have to run a Profiler trace on the desired table and filter the statements. We are considering adding support for obtaining the DML statement that caused a trigger to fire in a future release.|||

If you're just capturing the DML to replicate it on a different server then do you need the actual DML?

Why not just a trigger to log which rows have been updated then when you run your replication batch you can easily copy the relavent data over (or delete where appropriate)