If command

[sujaoschin]

I am getting error while writing the query in transformer.

The purpose is to mark the record as "D" which are obeying this condition

if Transform.AS_LOCATION <> "111"
if Transform.AS_LOCATION <> "112" OR
if Transform.AS_LOCATION <> "105" OR
if Transform.AS_LOCATION <> "104" OR
if Transform.AS_LOCATION <> "108" OR
if Transform.AS_LOCATION <> "109" OR
if Transform.AS_LOCATION <> "101" OR
if Transform.AS_LOCATION <> "102" OROR
if Transform.AS_LOCATION <> "103" OR
if Transform.AS_LOCATION <> "TELSTRA" OR
if Transform.AS_LOCATION <> "D" then "D"
else Transform.AS_LOCATION
endif
endif
endif
endif
endif
endif
endif
endif
endif
endif
endif

[ray.wurlod]

It is not an If command; it is an If expression. Therefore every part needs an Else part (since the expression must produce a result) and there is no EndIf.

Take some time to analyze your logic. In particular Not(A) Or Not(B) is always true. Write out your requirement in English (if you don't have it already in a design specification).

[sujaoschin]

I have tried the 2 options, but still it is throwing me error. Can you plz help me out. I don't know where I am doing the mistake.

option-1

if Reject_link.AS_LOCATION <> "111" else
if Reject_link.AS_LOCATION <> "112" else
if Reject_link.AS_LOCATION <> "105" else
if Reject_link.AS_LOCATION <> "104" else
if Reject_link.AS_LOCATION <> "108" else
if Reject_link.AS_LOCATION <> "109" else
if Reject_link.AS_LOCATION <> "101" else
if Reject_link.AS_LOCATION <> "102" else
if Reject_link.AS_LOCATION <> "103" else
if Reject_link.AS_LOCATION <> "TELSTRA" else
if Reject_link.AS_LOCATION <> "D" then "D"
else Reject_link.AS_LOCATION

option-2

if Reject_link.AS_LOCATION <> "111" then
if Reject_link.AS_LOCATION <> "112" then
if Reject_link.AS_LOCATION <> "105" then
if Reject_link.AS_LOCATION <> "104" then
if Reject_link.AS_LOCATION <> "108" then
if Reject_link.AS_LOCATION <> "109" then
if Reject_link.AS_LOCATION <> "101" then
if Reject_link.AS_LOCATION <> "102" then
if Reject_link.AS_LOCATION <> "103" then
if Reject_link.AS_LOCATION <> "TELSTRA" then
if Reject_link.AS_LOCATION <> "D" then "D"
else Reject_link.AS_LOCATION

[chulett]

Wow.

One 'if'. 'And' not 'Or'.

[ray.wurlod]

Every If needs its own Then and every If needs its own Else.

Write out your requirement in English (if you don't have it already in a design specification) and post that here.

There is a far more elegant solution available using Index() function, but you need to learn how to code the IF expression if you are going to have any career with this product.

[sujaoschin]

The requirement is this. I have 2 columns -AS_LOCATION & BISEPS_MI_AD_ID

1) IF the incoming records has the value of AS_LOCATION='FA' and BISEPS_MI_AD_ID=90002 then the output column AS_LOCATION should have the value of 101
2)IF the incoming records has the value of AS_LOCATION='FA' and BISEPS_MI_AD_ID=745282 then the output column AS_LOCATION should have the value of 102
Eg:-
-------------------------------------------------------------------------------------
I/P FIELD O/P FIELD
---------- ------------
AS_LOCATION BISEPS_MD_AD_ID AS_LOCATION
FA 90002 101
FA 745282 102
FA 45689 record to be dropped

FQ 90002 105
FQ 745282 106
FQ 98765 record to be dropped
-----------------------------------------------------------------------------------
(***)If the above condition fails like if AS_LOCATION <>FA and BISEPS_MI_AD_ID is other than 745282 & 90002 then that record should be dropped and should be not be loaded into the target.

Like above 'FA' condition, I have other condition for FE,FQ,etc.

I have written the logic for above 1 & 2 conditions and its working fine. But for dropping the record (***) the logic is not working properly. Please advise.

[sujaoschin]

The requirement is this. I have 2 columns -AS_LOCATION & BISEPS_MI_AD_ID

1) IF the incoming records has the value of AS_LOCATION='FA' and BISEPS_MI_AD_ID=90002 then the output column AS_LOCATION should have the value of 101
2)IF the incoming records has the value of AS_LOCATION='FA' and BISEPS_MI_AD_ID=745282 then the output column AS_LOCATION should have the value of 102
Eg:-
-------------------------------------------------------------------------------------
I/P FIELD O/P FIELD
---------- ------------
AS_LOCATION BISEPS_MD_AD_ID AS_LOCATION
FA 90002 101
FA 745282 102
FA 45689 record to be dropped

FQ 90002 105
FQ 745282 106
FQ 98765 record to be dropped
-----------------------------------------------------------------------------------
(***)If the above condition fails like if AS_LOCATION <>FA and BISEPS_MI_AD_ID is other than 745282 & 90002 then that record should be dropped and should be not be loaded into the target.

Like above 'FA' condition, I have other condition for FE,FQ,etc.

I have written the logic for above 1 & 2 conditions and its working fine. But for dropping the record (***) the logic is not working properly. Please advise.

[chulett]

Please post examples of both - that which works and that which does not. Another option for 'dropping' a record that doesn't match any other link is a Reject link, or a link with a simple constraint that equates to that to avoid the warning a Reject link logs.

[sujaoschin]

Thanks, Chulett.

Like you told,I used one 'IF' and 'AND' like below and it is working fine.

if Mark_D.AS_LOCATION <> "111" AND
Mark_D.AS_LOCATION <> "112" AND
Mark_D.AS_LOCATION <> "105" AND
Mark_D.AS_LOCATION <> "104" AND
Mark_D.AS_LOCATION <> "108" AND
Mark_D.AS_LOCATION <> "109" AND
Mark_D.AS_LOCATION <> "101" AND
Mark_D.AS_LOCATION <> "102" AND
Mark_D.AS_LOCATION <> "103" AND
Mark_D.AS_LOCATION <> "TELSTRA" AND
Mark_D.AS_LOCATION <> "D" then "D"
else Mark_D.AS_LOCATION
Mark_D.AS_LOCATION

The problem is resolved. Thank you very much