Limited load with where statement

[carolin01]

Hi,

I´m trying to do a limited resident load from another table with the following statemtent:

where mid(BaseItem, 1, 2) = 'EH' and (BaseItemTextLenght <=20);

But for some reason it only reduces the data to Base-Item EH, it does not fullfull the second condition that the BaseItemTextLength should be <= 20. What am I doing wrong?

Best regards

Carolin

[jonathandienst]

Carolin

I don't understand what the problem is. The script looks OK to me. The upper part of the load uses the bottom part as the source, so it will only act on the data that has already passed your filter.

In what way is it not working? Remember that unless you drop table dProductsLud, all the BaseItems will still exist in the model, but only those that passed the filter will have values in DESIGN_OP or any of the other derived fields in either part of the load statement.

Jonathan

[#3]

Hi,

Instead of len (BaseItem) <=20 use len (trim(BaseItem)) <=20 in where clause

[its_anandrjs]

There is no error in your load script try to make another resident load and make and instead of using this script and len (BaseItem) <=20; make a field len (BaseItem) as Len_BaseItem

Temp:

Load*,
if(DESIGN_OP = 'M', 'flat face M18x1,5 rod 10 mm',
      if(DESIGN_OP = 'S', 'flat face 3/4-16 rod 10 mm',
           if(DESIGN_OP = 'L', 'flat face 3/4-16 rod 7 mm',
                if(DESIGN_OP = 'K', 'flat face M18x1,5 rod 7mm',
                     if(DESIGN_OP = 'W', 'flat face M18x1,5, 316L, 10mm',
                          'New Design or Invalid Design Option'))))) as DESIGN_OP_DESC,
if(Connection_Type = 'D84','8-pin M12 connector',
      if(Connection_Type = 'D34','5-pin M12 connector',
           if(Connection_Type = 'M01' or 'M00','Molex connector',
                if(Connection_Type = 'D60','6-pin DIN connector',
                     if(Connection_Type = 'E62','2x6 DIN Connector CAN + pwr',
                          if(Connection_Type = 'D62','2x6 pin DIN CAN',
                               'Invalid Connection Type')))))) as Connection_Type_Desc,
if(Options = '1','24 V Standard',
      if(Options = 'A','24 V HVO',
           'Invalid Option')) as Options_DESC;
Load
      BaseItem,

     len(trim(BaseItem)) AS Len_BaseItem, ///New field add here
      mid(BaseItem, 1, 2) as Design,
      'Standard rod version' as HousingType,
      mid(BaseItem, 3, 1) as DESIGN_OP,
      '' as MAGNET_TYPE,
      '' as MAGNET_TYPE_DESC,
      mid(BaseItem, 4, 4) as LENGTH,
      mid(BaseItem, 8, 1) as LENGTH_DESC,
      mid(BaseItem, 9, 3) as Connection_Type,
      '' as CableLenght,
      mid(BaseItem, 12, 1) as Options,
      mid(BaseItem, 13, 6) as POS_OUT
Resident dProductsLud;

Drop table dProductsLud;

Noconcatenate

New:

Load

DESIGN_OP_DESC,Connection_Type_Desc

BaseItem,

Len_BaseItem,

Design,

HousingType,

DESIGN_OP,

MAGNET_TYPE,

MAGNET_TYPE_DESC,

LENGTH,

LENGTH_DESC,

Connection_Type,

CableLenght,

Options,

POS_OUT

Resident Temp

Where  Design = 'EH' and Len_BaseItem <=20;

New update please check

[carolin01]

This helped me to see that most of my values only hat 12 positions. Also I had to change from < to >. Poor me, I´m always confused by the direction of that sign

mid(BaseItem, 1, 2) = 'EH' and len (trim(BaseItem)) >=12;

[carolin01]

By the way, can anybody explain what 'trim' in that statement means? I use it now but I don´t know if it is important.

[its_anandrjs]

Trim function removes Blank space from the field suppose field contains spaces see the example

Eg:-

trim ( ' abc' ) returns 'abc'

trim ( 'abc ' ) returns 'abc'

trim ( ' abc ' ) returns 'abc'

[carolin01]

Very useful then. I also feared that this could be an option.