Equivalent of sql's IN statement for a Resident load

[#1]

I have a sql table which has 50 columns labelled DX1, DX2.. etc as well as other columns.

I'm loading this table in, and now want to create a new table, which is a filtered version of the SQL Load.

The filter is that one of the 50 DX columns must be one of 6 possible values ('J45','J450','J451','J458','J459','J46')

In SQL i would do this using the IN statement as follows

WHERE (DX1 IN ('J45','J450','J451','J458','J459','J46')

OR DX2 IN ('J45','J450','J451','J458','J459','J46')

OR DX3 IN ('J45','J450','J451','J458','J459','J46')

OR DX4 IN ('J45','J450','J451','J458','J459','J46')

etc

This doesn't work for a Resident load though. I've also tried using MATCH, but this didn't seem to work.

Any ideas?

[#2]

I think U may create new table (Table1 e.g.) , fill her with your values manually, and then load * from ..... where exists(Table1)

[Miguel_Angel_Baeyens]

Hello Matt,

The following code will work in any LOAD statement:

Table:LOAD *WHERE MATCH(DX1,'J45','J450','J451','J458','J459','J46')OR MATCH(DX2,'J45','J450','J451','J458','J459','J46')OR MATCH(DX3,'J45','J450','J451','J458','J459','J46')OR MATCH(DX4,'J45','J450','J451','J458','J459','J46');SQL SELECT *FROM Database.Table;

When there are a lot of values you want to compare with and they are stored in any datasource, a mapping table/applymap will work in the where clause as well:

LoadTableMap: // This map is only used while the script is being excecutedMAPPING LOAD DX1 1 AS LOADWHERE LEFT(DX1, 3) = 'J45';SQL SELECT DX1FROM Database.Table; Table:LOAD *WHERE ApplyMap('LoadTableMap', DX1, '$') <> '$'; // It will load all existing values in previously loaded DX1SQL SELECT *FROM Database.Table;

Hope this helps

[#4 · Accepted solution]

I've found the problem.. I think the match was having issues when one of the DX values was null.

I AND'ed each MATCH with an ISNULL and it worked fine.

Cheers for your help guys