Massive??

[sculptorlv]

Hello again!

I have another "situation for a junior". Hope for help and advises.

I have a sql table, like:

SQL SELECT

ID

Name

Age

etc...

FROM Table

WHERE

Name <> Name1

Name <> Name2

..

Name <> Name50

What is the best practice to define these WHERE expressions? Is it possible to make a massive of all possible "Names", which I will not take in my SQL Select? Also, it might be more comfortable to change and massive rather WHERE expressions..

[tresesco]

May be like:

SQL SELECT

ID

Name

Age

etc...

FROM Table

WHERE Name Not In ( 'Name1', 'Name2', .... 'Name50');

[sculptorlv]

ok, this might work.

But, if I use such LIST for many different SQL SELECT. Then, I will have to make changes in each of it (if I will increase List + 1 Name). Thus, I am looking for more universal solution.

[tresesco]

Try using variable, like:

Set vNameList= 'Name1','Name2';

Then

...

Where Name Not In ($(vNameList));

Now use this variable multiple times, and change whene need at one place.

[sculptorlv]

hmm... I guess, not working.

[tresesco]

It should. Could you share your script section and error message/output you are getting?

[sculptorlv]

Sure!

SET ThousandSep=' ';

SET DecimalSep='.';

SET MoneyThousandSep=' ';

SET MoneyDecimalSep='.';

SET MoneyFormat='€ # ##0.00;-€ # ##0.00';

SET TimeFormat='h:mm:ss';

SET DateFormat='DD.MM.YYYY';

SET TimestampFormat='DD.MM.YYYY h:mm:ss[.fff]';

SET MonthNames='jan;feb;mar;apr;mai;jūn;jūl;aug;sep;okt;nov;dec';

SET DayNames='pr;ot;tr;ce;pk;se;sv';

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=False;User ID=ruskli;Initial Catalog=TS_Live;Data Source=vendb\sql2008;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=RKLIMOV-MOB;Use Encryption for Data=False;Tag with column collation when possible=False];

Set vFriendCompnayList =

'VENDEN MINERAL WATER SIA',

'VENDEN KAFIJAS TIRDZNIECĪBA',

'VENDEN IZBRAUKUMU TIRDZNIECĪBA VENTSPILS FILIĀLE',

'VENDEN GROUP SIA';

SQL SELECT

Tbl_Contact.ID AS ID_Contact,

Tbl_Contact.Name AS Agent_Name,

Tbl_Contact.DepartmentID AS "Contact__Agent_Department",

Tbl_Contract.ContractNumber AS Contract__Number,

Tbl_Contract.Title AS Customer_Title

FROM "TS_Live".dbo."tbl_Contact" AS Tbl_Contact

LEFT JOIN "TS_Live".dbo."tbl_Contract" AS Tbl_Contract

  ON Tbl_Contact.ID = Tbl_Contract.AgentID

WHERE

Tbl_Contract.Title Not In ($(vFriendCompnayList))

;

The result:

[tresesco]

It could be beacuse of extra spaces. Check that in the db and use trim() if needed.

[sculptorlv]

I have exactly such values in database.

How can I use trim() ?

[tresesco]

Try like:

WHERE

Trim(Tbl_Contract.Title) Not In ($(vFriendCompnayList));