Case sensive search using IN in a case statement.

Question

Case sensive search using IN in a case statement.

bswhipp

Ten Centuries

Points: 1117
More actions
January 2, 2018 at 8:28 am

#334318

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

Viewing 3 posts - 1 through 2 (of 2 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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 Ten Centuries Points: 1117 More actions · Answer 2

Thanks, but I was a bit out of what I wanted to do. I figured it out. Thanks got the quick reply though.