Issue with logic

[times29]

Hi,
i am getting below results while doing

IF TRIM(((to_transf4.ORDER_TCD[1,1]) <>'S' AND trim(to_transf4.ORDER_TCD<>'')) and (TRIM(to_transf4.COST_CNTR_TCD='') or IsNull(to_transf4.COST_CNTR_TCD))) then 'Y' ELSE 'N'

Peek_186,0: COST_CNTR_TCD:NULL POST_STAT:N DOC_NUM:5001307000 LINE_ITEM: 001. ORDER_TCD:60386276
Peek_186,0: COST_CNTR_TCD:177428 POST_STAT:N DOC_NUM:5001323601 LINE_ITEM: 001. ORDER_TCD:
Peek_186,0: COST_CNTR_TCD:NULL POST_STAT:N DOC_NUM:5001307000 LINE_ITEM: 002. ORDER_TCD:

I except the results to be as below i mean PST_STAY Y for first row

Peek_186,0: COST_CNTR_TCD:NULL POST_STAT:Y DOC_NUM:5001307000 LINE_ITEM: 001. ORDER_TCD:60386276
Peek_186,0: COST_CNTR_TCD:177428 POST_STAT:N DOC_NUM:5001323601 LINE_ITEM: 001. ORDER_TCD:
Peek_186,0: COST_CNTR_TCD:NULL POST_STAT:N DOC_NUM:5001307000 LINE_ITEM: 002. ORDER_TCD:

[ArndW]

Although I find your problem description rather confusing, I can see that COST_CNTR_TCD is a nullable field but you perform no null handling (i.e. NullToEmpty() or NullToValue() ) and your row will be dropped.

[times29]

Should not IsNull(to_transf4.COST_CNTR_TCD) take care of NULL

[ArndW]

It doesn't take care of the first occurrence, though.

[times29]

Right in some cases it don't so how to proceed then

[ArndW]

Since the same action is performed twice, it is more efficient to use a stage variable, but in this case

Code: Select all

(TRIM(to_transf4.COST_CNTR_TCD='')

becomes

Code: Select all

(TRIM(NullToEmpty(to_transf4.COST_CNTR_TCD)='')

That assumes that you want NULL values to equate to an empty string, otherwise the function would be

Code: Select all

(TRIM(NullToValue(to_transf4.COST_CNTR_TCD,'Dummy')='')

[chulett]

FYI - Your first TRIM makes no sense as it trims the result of the entire expression, meaning it resolves to a 0 or a 1 and then you trim that.