SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Case sensive search using IN in a case statement.


Case sensive search using IN in a case statement.

Author
Message
bswhipp
bswhipp
SSChasing Mays
SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)

Group: General Forum Members
Points: 638 Visits: 83
Below is my code. I need to search on 5 terms. The example givin, it is pulling rcords with 'APPROVED' and 'NOT APPROVED' I need it to only pull 'APPROVED' and so forth. I think i need another statement such as COLLATE SQL_Latin1_General_CP1_CS but not sure where to put it. have had no luck.
Any help would be great.

Select apvend.fvendno, fcompany, fvtype, fiso9000, fcstatus, flimit, fdsince,
Cast(fmuser1 as char(60)) as fusermemo,
Case when dtqonfile = Cast('19000101' as datetime) or DTQONFILE is null then 0 else 1 end as QonFileOK,
Case when dtisoexp > Current_timestamp then 1 else 0 end as ISOExpOK,
Case when dtqman = Cast('19000101' as datetime) or dtqman is null then 0 else 1 end as QualManOK,
Case when dtinsex is null then Cast('19000101' as datetime) else dtinsex end as InsurExp,
Case when dtinsex > Current_timestamp then 1 else 0 end as InsurExpOK,
Case when chkliabins is null then 0 else chkliabins end as PLiability,
Case when chkdatash is null then 0 else chkdatash end as DataSheet ,
Case when Vendstatus.fcpoptext IN ('APPROVED', 'ACHEIVER', 'RECOGNITION',
'IMPROVEMENT', 'PROBATION') then 1 else 0 end
as IsApproved,
flimit, fdsince,
Case when Vendstatus.fcpoptext IN ('PENDING') then 1 else 0 end as IsPending,
Case when dtqonfile is null then Cast('19000101' as datetime) else DTQONFILE end as QonFile,
Case when dtIsoExp is null then Cast('19000101' as datetime) else dtIsoExp end as ISOExpire,
Case when dtqman is null then Cast('19000101' as datetime) else dtqman end as QManual,
Case when Vendtype.fcPoptext is null then '' else vendtype.fcpoptext end as VendType,
Case when Vendstatus.fcpoptext is null then '' else vendstatus.fcpoptext end as VendRate
from apvend
left outer join apvend_ext on apvend_ext.fkey_id = apvend.identity_column
left outer join cspopup VendStatus on vendstatus.fcpopval = apvend.fcstatus and
vendstatus.fcpopkey = 'APVEND.FCSTATUS'
left outer join cspopup VendType on vendtype.fcpopval = apvend.fvtype and
vendstatus.fcpopkey = 'APVEND.FVTYPE'
where apvend.fvtype = 'DR' -- Direct Suppliers
order by 1
GilaMonster
GilaMonster
SSC Guru
SSC Guru (903K reputation)SSC Guru (903K reputation)SSC Guru (903K reputation)SSC Guru (903K reputation)SSC Guru (903K reputation)SSC Guru (903K reputation)SSC Guru (903K reputation)SSC Guru (903K reputation)

Group: General Forum Members
Points: 903778 Visits: 48757
Collation won't allow SQL to return NOT ALLOWED if you filter for ALLOWED.
There's no filter for 'APPROVED' vs 'NOT APPROVED' in the WHERE clause, so SQL will return both.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


bswhipp
bswhipp
SSChasing Mays
SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)

Group: General Forum Members
Points: 638 Visits: 83
Thanks, but I was a bit out of what I wanted to do. I figured it out. Thanks got the quick reply though.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search