Problem in constraint

gpbarsky · Post by **gpbarsky** » Thu Sep 25, 2003 11:12 am

Hi.

Can anybody tell me where is the problem in the next constraint ?

(Trim(DatosAFiltrar.SOCIEDAD) = pmSociedad Or pmSociedad = "*") And (Trim(DatosAFiltrar.FILIAL) = pmFilial Or pmFilial = "*") And (Trim(DatosAFiltrar.VIAPAGO) = pmViapago Or pmViapago = "*") And (pmTarjeta1 = "*" Or pmTarjeta1 = Trim(DatosAFiltrar.CLASETAR)) And (pmTarjeta2 = "*" Or pmTarjeta2 = Trim(DatosAFiltrar.CLASETAR)) And (pmTarjeta3 = "*" Or pmTarjeta3 = Trim(DatosAFiltrar.CLASETAR)) And (pmTarjeta4 = "*" Or pmTarjeta4 = Trim(DatosAFiltrar.CLASETAR)) And (Trim(DatosAFiltrar.BANCOPROPIO) = pmBanco Or pmBanco = "*")

This expression is within a Transformer stage, and all I wanted to do is to filter some records, according to several parameters received. Some of these parameters may be informed as an asterisc: in this case, the expresion should be true for this parameter.

The expression works until I enter pmTarjeta1 or pmTarjeta2 or pmTarjeta3 or pmTarjeta4 mixed. I mean, two of these values are informed: in this case nothing is selected.

So, the question is, is it related the fact that the same field is compared (DatosAFiltrar.CLASETAR) ? How can I enter the expression to make it work ?

Thanks in advance.

Guillermo P. Barsky
Buenos Aires - Argentina

kduke · Post by **kduke** » Thu Sep 25, 2003 11:58 am

Guillermo

Looks like a good solution. Your syntax looks correct. Try outputing all to a sequential file and run in debug mode one record at a time. I expect that pmTarjeta1 may have spaces around it or need a trim as well. The trim() command works differently than most people expect. It trims leading, trailing and double spaces down to single spaces.

Kim.

Kim Duke
DsWebMon - Monitor DataStage over the web
www.Duke-Consulting.com

gpbarsky · Post by **gpbarsky** » Thu Sep 25, 2003 12:12 pm

Kim:

Thanks. I tried the solution of the Trim() function, but it didn't work.

Any other idea of what's happening ?

Thanks again.

Guillermo P. Barsky
Buenos Aires - Argentina

kcbland · Post by **kcbland** » Thu Sep 25, 2003 12:14 pm

What I'd like to do is introduce a programming technique that will make your job design a little easier to follow.

Setup a series of stage variables in your transformer:

StageVariable|Derivation
-------------|-----------------------------------------------
FredIsTrue |(Trim(DatosAFiltrar.SOCIEDAD) = pmSociedad Or pmSociedad = "*")
BarneyIsTrue |(Trim(DatosAFiltrar.FILIAL) = pmFilial Or pmFilial = "*")
WilmaIsTrue |(pmTarjeta1 = "*" Or pmTarjeta1 = Trim(DatosAFiltrar.CLASETAR))

Now, what you can do is build a constraint that reads like:

FredIsTrue Or BarneyIsTrue or WilmaIsTrue

In addition, you'll be able to troubleshoot using either the DEBUGGER or by spooling to a file the stage variables with the data so that you can debug easier. Your logic looks okay, just messy.

Kenneth Bland

kduke · Post by **kduke** » Thu Sep 25, 2003 12:55 pm

Guillermo

I was wondering if you are running this from a shell script? If so then unless you quote the asterics correctly then that may be the problem.

Kenneth nice trick with the stage variables.

You need to output the values of all the parameters and variables to a sequential file. You are missing something.

Let me get this correct. You tried:

pmTarjeta1 = "*" Or
Trim(pmTarjeta1) = Trim(DatosAFiltrar.CLASETAR))

I am assuming pmTarjeta1 is a parameter.

Kim.

Kim Duke
DsWebMon - Monitor DataStage over the web
www.Duke-Consulting.com