Expression Evaluation

[gsherry1]

Hello All,

I have a column which contains a mathematical predicate expression. The possible operaters are +, -, *, /, =, >, < .
For example '3 + 2 = 7 - 5' (a false expression, not a typo).
There is no specified limit on the number of operands and operators in the expression.

I was wondering what is easiest way to evaluate the expression as true or false in DataStage. Is there something like an eval command?

Thanks

[ray.wurlod]

The easiest - and correct - way in server jobs is to do nothing. :D

Because there are no data types, context governs the use of the result of an expression. Your expression '3 + 2 = 7 - 5' (whether quoted or not) returns 0 which, in a Boolean context, is interpreted as "false" - for example if it were the test expression in an IF statement, that statement would take its ELSE branch.

In BASIC, an expression that returns 0, " " or "" are interpreted as false in a Boolean context, while any other non-null value is interpreted as true.

[patonp]

I've tried this out, but it doesn't seem to be working for me.

In my example, I have an inbound column named Lnk_From_Src.ExpEval. The column is defined as a varchar and contains numeric expressions that need to be evaluated at run time(i.e. '2+3=4+3', '1+1=2-0'). Within DataStage, I'd like to evaluate whether the contents of the column are true or false for each row. Is there any way to do this?

[chulett]

What about it "isn't working"? How are you using your expression field and what kind of output are you seeing?

Curious what happens when you try something simple like:

Code: Select all

If Lnk_FromSrc.ExpEval Then 'True' Else 'False'

in a derivation for various values?

[ArndW]

Dynamically parsing this string at runtime is difficult. The PERFORM engine has a keyword EVAL which does this, but it is not present in BASIC, and to use the ITYPE() function would also be a bit complex and quite slow.

It might be worth your time to write a dict entry I-Type that uses EVAL and then reading that in a routine using ITYPE. You would need to write the string in question to a hashed file, but I think it would work.

Apart from that I can only think of writing your own parser for this - not a huge effort, but certainly not trivial either.

[kduke]

ITYPE brilliant answer. Does EVAL work in BASIC? Got to try it.

[ArndW]

Kim,

no, I checked and couldn't find an EVAL or similar in BASIC - but somehow I think that I've done something like this before, I just can't remember how. The ITYPE() call will work on a dummy file, but it is rather slow. I wonder if there is a FMT() alternative to get this done...

[ray.wurlod]

Assertion: DataStage BASIC does not have an expression evaluating function.

Now that I understand the requirement, I'd suggest going out to the UNIX expr command to evaluate these expressions. Beware, however, that the expression elements have to be separated by spaces, and any operator meaningful to the shell needs to be escaped, for example \>.

To use Arnd's suggestion you are going to have to write an arbritrary expression evaluator using DataStage BASIC. An I-type can only evaluate its own expression, not an arbitrary one.

Yours does seem to be an odd requirement in an ETL task, however.

[kduke]

Ray

I am not sure that was the requirement but still a very cool idea by Arnd.

[ArndW]

I did a quick and dirty test that did work - in a routine do an

Code: Select all

MathExpression= '1+2+3+4=9'
EXECUTE "LIST VOC FIRST 1 ID.SUP COL.SUP HDR.SUP  EVAL '":MathExpression:"'" CAPTURING ScreenIO RETURNING ERRORCODE

And it will work when you parse the resultant ScreenIO string. Not pretty or fast or efficient or recommended, but it will work.

[ray.wurlod]

May I suggest SQL instaad? RetrieVe has to write a temporary entry to the file dictionary (DICT VOC in your case). UniVerse SQL does not require write permission to the dictionary because it doesn't write anything there.

Code: Select all

MathExpression= '1+2+3+4=9' 
Command = "SELECT EVAL " : DQuote(MathExpression) : " FROM VOC FIRST 1;"
EXECUTE Command CAPTURING ScreenIO RETURNING ERRORCODE

[kduke]

Need to create table dual.

[ray.wurlod]

Rubbish

You can use any extant "UniVerse" file that has a file dictionary defined.

[kduke]

I noticed we have one on Sql Server. Think he was an Oracle fanatic?

[gsherry1]

Assertion: DataStage BASIC does not have an expression evaluating function.

Now that I understand the requirement, I'd suggest going out to the UNIX expr command to evaluate these expressions. Beware, however, that the expression elements have to be separated by spaces, and any operator meaningful to the shell needs to be escaped, for example \>.

To use Arnd's suggestion you are going to have to write an arbritrary expression evaluator using DataStage BASIC. An I-type can only evaluate its own expression, not an arbitrary one.

Yours does seem to be an odd requirement in an ETL task, however.

I was going to try the expr command, but I get an error that it is not a valid OS command. How do I check to determine if MKS package is installed? I could not find any references to this anywhere in the pdfs that shipped with DataStage 7.5.