IN and LIKE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

IN and LIKE

Author

Topic

nhaas
Yak Posting Veteran

90 Posts

Posted - 2009-08-19 : 20:20:35

is use "AND CC.COSTCENTRE IN (select deptid from WebDepartAllowed where AllowedUser = @UserId)"
My return is a 4 digit number say 8900.

I would like to use the returned value 8900 and do a LIKE in the SQL for the IN statement so that I will get the results stored as 89000001,89001234, 89002435 and so forth. is it possible to do "AND CC.COSTCENTRE LIKE IN (select deptid from WebDepartAllowed where AllowedUser = @UserId)"

if I do the code above I get "Incorrect syntax near the keyword 'IN'. "

Basically I would like to do a like on the returned values of "8900". I also get other values from the select query like 8901, 8902 etc that is why I do the IN statement.

Thank you for any pointers!!!!

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2009-08-19 : 22:16:42

Post sample data..

AND CC.COSTCENTRE LIKE IN (select deptid from WebDepartAllowed where AllowedUser = @UserId)"

Like may not work..

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-20 : 04:43:40

Not sure what you mean. Maybe this:
AND left(convert(varchar(20),CC.COSTCENTRE),4) IN (select deptid from WebDepartAllowed where AllowedUser = @UserId)

No, you're never too old to Yak'n'Roll if you're too young to die.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-08-20 : 09:15:27

Yes it is possible. You will need to
select * from
WebDepartAllowed
inner join COSTCENTRE ON '%'+CC.COSTCENTRE+'%' LIKE WebDepartAllowed.deptID

or which ever way around it is. You will probably need a distinct in case of dupes. A better way may be to select the distinct PK pk then join that back to the main table to avoid any duplicates.

Really, really bad data model by the way.

nhaas
Yak Posting Veteran

90 Posts

Posted - 2009-08-21 : 17:09:32

Sorry been away from the office for a couple of Days:
SQL query:SELECT S.SERVE_NO, CC.COSTCENTRE, S.SERVICE_USER_NAME AS NAME, MOB_PLAN,mob_phonetype FROM Services S INNER JOIN COSTCENTRES CC ON S.SERVE_NO = CC.SERVE_NO AND CC.STARTDATE IN (SELECT DISTINCT MAX(STARTDATE) FROM COSTCENTRES CC2 WHERE CC2.SERVE_NO = CC.SERVE_NO) WHERE LINE_STATUS = 'ACTIVE' AND (SERV_CLASS = 'WIRELESS') AND left(convert(varchar(20),CC.COSTCENTRE),4) IN (select deptid from WebDepartAllowed where AllowedUser = @UserId) ORDER BY CC.COSTCENTRE, S.SERVE_NO

When a user logs in I pull there AD ID and look in a database to see what they have a prevaledge to see either a 4 digit Cost Center or 8.
so if a user has 4 digits (and can have multiple costcenters) so lets say they have 8900 they can see everything in that cost center. or if they have an 8 digit Org they can only see the specific costcenter and division say 89001234. Again the can see multiple - thats I use "CC.Costcentre IN (select deptid from WebDepartAllowed where AllowedUser = @UserId)"

Data in the CC.Costcentre looks like 89000000,8901000,89001234,89120000,89121234.
So me as the end user will have 8900,8901,8912 orgs that I will be allowed to see.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources