Need help with SELECT statement please.

13 May 2005 3:20 PM

Lam Nguyen

Hi all,

How can I get the following results. Please look at the rules below. The
trick part is ChoicePointSuffix_cd = 'AAA' is valid data
but = aaa is invalid data because the third party product we using at our
company looking for AAA values not aaa. Any help would greatly appreciate.
Note: SQL Server installed is not case sensitive.

drop table #Temp
go
CREATE TABLE #Temp
(
   EmpNumb                 CHAR(8) NULL,
   ChoicePointAccount_cd   CHAR(7) NULL,
   ChoicePointSuffix_cd    CHAR(3) NULL
)
GO

INSERT #Temp VALUES ('E642462', '360277', 'AAA')
INSERT #Temp VALUES ('E822423', '360277', 'AAA')
INSERT #Temp VALUES ('E102464', '360277', 'aaa')
INSERT #Temp VALUES ('E103647', '360277', 'Aaa')
INSERT #Temp VALUES ('E136481', '360277', 'aaa')
INSERT #Temp VALUES ('E634020', '360277', 'GTS')
INSERT #Temp VALUES ('E722157', '330335', 'ASV')
INSERT #Temp VALUES ('E108641', '630277', '888')
INSERT #Temp VALUES ('e109480', '360277', '')
INSERT #Temp VALUES ('e106485', '', 'AAA')
INSERT #Temp VALUES ('E118405', '', '')
go

   SELECT *
     FROM #Temp
   go

Rules: -- Return all these rows that DO NOT have ChoicePointAccount_cd =
360277 and ChoicePointSuffix_cd = AAA
       -- Records that have ChoicePointAccount_cd = 360277 and
ChoicePointSuffix_cd = AAA are valid data.

Result want:

--Bad data.
EmpNumb ChoicePointAccount_cd ChoicePointSuffix_cd
-------- --------------------- --------------------
E102464 360277                aaa
E103647 360277                Aaa
E136481 360277                aaa
E634020 360277                GTS
E722157 330335                ASV
E108641 630277                888
e109480 360277
e106485                        AAA
E118405

13 May 2005 3:33 PM

Anith Sen

You can use the COLLATE clause with a case sensitive collation like:

SELECT *
FROM #Temp
WHERE NOT ( ChoicePointAccount_cd = 360277
AND ChoicePointSuffix_cd COLLATE
SQL_Latin1_General_CP1_CS_AS = 'AAA' ) ;

--
Anith

13 May 2005 3:36 PM

David Portas

Can't you make the suffix_cd column case sensitive? For example:

CREATE TABLE #Temp
(
   EmpNumb CHAR(8) NULL,
   ChoicePointAccount_cd CHAR(7) NULL,
   ChoicePointSuffix_cd CHAR(3) COLLATE Latin1_General_CS_AS NULL
   /* ??? PRIMARY KEY NOT SPECIFIED */
)

If not, you can specify a case-sensitive collation in the query. This
means that the server may not be able to take full advantage of any
index on the column.

SELECT empnumb, choicepointaccount_cd, choicepointsuffix_cd
FROM #Temp
WHERE choicepointaccount_cd <> '360277'
    OR choicepointsuffix_cd <> 'AAA'
      COLLATE Latin1_General_CS_AS

Note that I've ignored NULLs here. I'm not sure if that's what you
intended but as your table doesn't even have a key I guessed that you
might have been careless about the nullability and maybe these columns
are non-null anyway (?).

--
David Portas
SQL Server MVP
--

13 May 2005 3:38 PM

Alejandro Mesa

Find the collation being used by that column using information_schema.columns
and use COLLATE with the _CS_ (case sentity) version. In the example, I am
using the case sensity version of the collation used by database tempdb
because the table is temporal and we did not use COLLATE in the table
definition. In my tempdb I have SQL_Latin1_General_CP1_CI_AS, so I will use
SQL_Latin1_General_CP1_CS_AS.

use northwind
go

CREATE TABLE #Temp
(
   EmpNumb                 CHAR(8) NULL,
   ChoicePointAccount_cd   CHAR(7) NULL,
   ChoicePointSuffix_cd    CHAR(3) NULL
)
GO

INSERT #Temp VALUES ('E642462', '360277', 'AAA')
INSERT #Temp VALUES ('E822423', '360277', 'AAA')
INSERT #Temp VALUES ('E102464', '360277', 'aaa')
INSERT #Temp VALUES ('E103647', '360277', 'Aaa')
INSERT #Temp VALUES ('E136481', '360277', 'aaa')
INSERT #Temp VALUES ('E634020', '360277', 'GTS')
INSERT #Temp VALUES ('E722157', '330335', 'ASV')
INSERT #Temp VALUES ('E108641', '630277', '888')
INSERT #Temp VALUES ('e109480', '360277', '')
INSERT #Temp VALUES ('e106485', '', 'AAA')
INSERT #Temp VALUES ('E118405', '', '')
go

SELECT *
FROM #Temp
where
    ChoicePointAccount_cd != '360277' or ChoicePointSuffix_cd collate
SQL_Latin1_General_CP1_CS_AS != 'AAA'
go

drop table #Temp
go

You can use these statements to help you find the collation.

select column_name, collation_name
from information_schema.columns
where table_schema = 'dbo' and table_name = 'your_table_name' and
column_name = 'the_column_name'
go

select serverproperty('collation'), databasepropertyex('tempdb' , 'collation')
go

select
    *
from

::fn_helpcollations()
go

AMB

Show quote

"Lam Nguyen" wrote:

> Hi all,
>
> How can I get the following results. Please look at the rules below. The
> trick part is ChoicePointSuffix_cd = 'AAA' is valid data
> but = aaa is invalid data because the third party product we using at our
> company looking for AAA values not aaa. Any help would greatly appreciate.
> Note: SQL Server installed is not case sensitive.
>
> drop table #Temp
> go
> CREATE TABLE #Temp
> (
>    EmpNumb                 CHAR(8) NULL,
>    ChoicePointAccount_cd   CHAR(7) NULL,
>    ChoicePointSuffix_cd    CHAR(3) NULL
> )
> GO
>
> INSERT #Temp VALUES ('E642462', '360277', 'AAA')
> INSERT #Temp VALUES ('E822423', '360277', 'AAA')
> INSERT #Temp VALUES ('E102464', '360277', 'aaa')
> INSERT #Temp VALUES ('E103647', '360277', 'Aaa')
> INSERT #Temp VALUES ('E136481', '360277', 'aaa')
> INSERT #Temp VALUES ('E634020', '360277', 'GTS')
> INSERT #Temp VALUES ('E722157', '330335', 'ASV')
> INSERT #Temp VALUES ('E108641', '630277', '888')
> INSERT #Temp VALUES ('e109480', '360277', '')
> INSERT #Temp VALUES ('e106485', '', 'AAA')
> INSERT #Temp VALUES ('E118405', '', '')
> go
>
>    SELECT *
>      FROM #Temp
>    go
>
>
> Rules: -- Return all these rows that DO NOT have ChoicePointAccount_cd =
> 360277 and ChoicePointSuffix_cd = AAA
>        -- Records that have ChoicePointAccount_cd = 360277 and
> ChoicePointSuffix_cd = AAA are valid data.
>
> Result want:
>
> --Bad data.
> EmpNumb ChoicePointAccount_cd ChoicePointSuffix_cd
> -------- --------------------- --------------------
> E102464 360277                aaa
> E103647 360277                Aaa
> E136481 360277                aaa
> E634020 360277                GTS
> E722157 330335                ASV
> E108641 630277                888
> e109480 360277
> e106485                        AAA
> E118405

13 May 2005 3:47 PM

Sarav

RULE1: Query

   SELECT *
     FROM #Temp
where (convert(binary,ChoicePointSuffix_cd) <> convert(binary,'AAA')
OR ChoicePointAccount_cd <> 360277)

RULE2: Query

   SELECT *
     FROM #Temp
where convert(binary,ChoicePointSuffix_cd) = convert(binary,'AAA')
and ChoicePointAccount_cd = 360277

Regards,
Sarav...

Show quote

"Lam Nguyen" <LamNgu***@discussions.microsoft.com> wrote in message
news:CC14ABEC-1D4F-4FDA-8253-25133298C7C8@microsoft.com...
> Hi all,
>
> How can I get the following results. Please look at the rules below. The
> trick part is ChoicePointSuffix_cd = 'AAA' is valid data
> but = aaa is invalid data because the third party product we using at our
> company looking for AAA values not aaa. Any help would greatly
> appreciate.
> Note: SQL Server installed is not case sensitive.
>
> drop table #Temp
> go
> CREATE TABLE #Temp
> (
>   EmpNumb                 CHAR(8) NULL,
>   ChoicePointAccount_cd   CHAR(7) NULL,
>   ChoicePointSuffix_cd    CHAR(3) NULL
> )
> GO
>
> INSERT #Temp VALUES ('E642462', '360277', 'AAA')
> INSERT #Temp VALUES ('E822423', '360277', 'AAA')
> INSERT #Temp VALUES ('E102464', '360277', 'aaa')
> INSERT #Temp VALUES ('E103647', '360277', 'Aaa')
> INSERT #Temp VALUES ('E136481', '360277', 'aaa')
> INSERT #Temp VALUES ('E634020', '360277', 'GTS')
> INSERT #Temp VALUES ('E722157', '330335', 'ASV')
> INSERT #Temp VALUES ('E108641', '630277', '888')
> INSERT #Temp VALUES ('e109480', '360277', '')
> INSERT #Temp VALUES ('e106485', '', 'AAA')
> INSERT #Temp VALUES ('E118405', '', '')
> go
>
>   SELECT *
>     FROM #Temp
>   go
>
>
> Rules: -- Return all these rows that DO NOT have ChoicePointAccount_cd =
> 360277 and ChoicePointSuffix_cd = AAA
>       -- Records that have ChoicePointAccount_cd = 360277 and
> ChoicePointSuffix_cd = AAA are valid data.
>
> Result want:
>
> --Bad data.
> EmpNumb ChoicePointAccount_cd ChoicePointSuffix_cd
> -------- --------------------- --------------------
> E102464 360277                aaa
> E103647 360277                Aaa
> E136481 360277                aaa
> E634020 360277                GTS
> E722157 330335                ASV
> E108641 630277                888
> e109480 360277
> e106485                        AAA
> E118405

13 May 2005 3:51 PM

Lam Nguyen

As always, thank you very much for all your help.

Lam

Show quote

"Lam Nguyen" wrote:

> Hi all,
>
> How can I get the following results. Please look at the rules below. The
> trick part is ChoicePointSuffix_cd = 'AAA' is valid data
> but = aaa is invalid data because the third party product we using at our
> company looking for AAA values not aaa. Any help would greatly appreciate.
> Note: SQL Server installed is not case sensitive.
>
> drop table #Temp
> go
> CREATE TABLE #Temp
> (
>    EmpNumb                 CHAR(8) NULL,
>    ChoicePointAccount_cd   CHAR(7) NULL,
>    ChoicePointSuffix_cd    CHAR(3) NULL
> )
> GO
>
> INSERT #Temp VALUES ('E642462', '360277', 'AAA')
> INSERT #Temp VALUES ('E822423', '360277', 'AAA')
> INSERT #Temp VALUES ('E102464', '360277', 'aaa')
> INSERT #Temp VALUES ('E103647', '360277', 'Aaa')
> INSERT #Temp VALUES ('E136481', '360277', 'aaa')
> INSERT #Temp VALUES ('E634020', '360277', 'GTS')
> INSERT #Temp VALUES ('E722157', '330335', 'ASV')
> INSERT #Temp VALUES ('E108641', '630277', '888')
> INSERT #Temp VALUES ('e109480', '360277', '')
> INSERT #Temp VALUES ('e106485', '', 'AAA')
> INSERT #Temp VALUES ('E118405', '', '')
> go
>
>    SELECT *
>      FROM #Temp
>    go
>
>
> Rules: -- Return all these rows that DO NOT have ChoicePointAccount_cd =
> 360277 and ChoicePointSuffix_cd = AAA
>        -- Records that have ChoicePointAccount_cd = 360277 and
> ChoicePointSuffix_cd = AAA are valid data.
>
> Result want:
>
> --Bad data.
> EmpNumb ChoicePointAccount_cd ChoicePointSuffix_cd
> -------- --------------------- --------------------
> E102464 360277                aaa
> E103647 360277                Aaa
> E136481 360277                aaa
> E634020 360277                GTS
> E722157 330335                ASV
> E108641 630277                888
> e109480 360277
> e106485                        AAA
> E118405