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.
| Author |
Topic |
|
Villanuev
Constraint Violating Yak Guru
478 Posts |
 Posted - 2011-10-26 : 22:59:49
|
| Hi Guys,I have this script from Crystal report. i have to use in SQL transact. but when i try to use SQL transact i got an error.From CR.Where not ({INVENTTABLE.ITEMID} like ["dir*", "tag*", "env*", "*esn*", "*ber*", "fru*", "rep*", "sal*", "x100*"])SQL Transact:Where not NVENTTABLE.ITEMID like ('dir%','tag%','env%','%esn%','%ber%','fru%','rep%','sal%','x100%')Here is the error:Msg 102, Level 15, State 1, Line 4Incorrect syntax near ','.Thanks,JOV |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-10-26 : 23:11:43
|
| where nventtable.itemid not like 'dir%'and nventtable.itemid not like 'tag%'and nventtable.itemid not like 'env%'and...Be One with the OptimizerTG |
 |
|
|
Villanuev
Constraint Violating Yak Guru
478 Posts |
Posted - 2011-10-26 : 23:22:40
|
| Thanks TG. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-27 : 02:34:10
|
it would be much better to store the exclusion list (dir,tag,env ,...) in a seperate table called say ExcludedItemList and using not exists or left join to it rather than using series of not like conditions like above. in that way the code will become scalable and you dont have to keep on adding new not like conditions in your query for each of new items to be excluded. you just need to add one more column to table and then NOT EXISTS will take care of it also. the query will be like....WHERE NOT EXISTS(SELECT 1 FROM ExclusionItemList WHERE NVENTTABLE.ITEMID LIKE Item + '%') ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Correct Syntax