"Lookup" in Decision Stream Script is not working

[Xantix]

I tried this script to create a derivation.
The script is very simple; what I want to do is far more complicated.
<------------
$SQLString := 'SELECT distinct &quot;SPEC4&quot; FROM &quot;S_SPEC&quot; WHERE &quot;SPEC1&quot; = 1 and &quot;SPEC2&quot; is null and &quot;SPEC3&quot; is null';

return(lookup('TEMPSTORE', $SQLString));
------------>
When I test it in the derivations properties it works fine but when I do try to create my dimension table with it, I got this error message:
<------------
DS-EXP-E001: Expression parse error at position 135;
unrecognised function

$SQLString := 'SELECT distinct &quot;SPEC4&quot; FROM &quot;S_SPEC&quot; WHERE &quot;SPEC1&quot; =
1 and &quot;SPEC2&quot; is null and &quot;SPEC3&quot; is null';



return(>>>lookup('TEMPSTORE', $SQLString));

DS-STREAM-E260: invalid script specified for Derived DataSource Item 'Spec4'
of DataSource 'Prof' of DataStream 'TM_PROF_H'
------------>
What is the problem?
For information, I use Desicion Stream 7.0 MR2 and Oracle 8.1.7

 

[Xantix]

There is nothing wrong with my script and I don't know why it gives me an error message but Cognos gave me a way to make it work.

To actually work, you have to put the lookup function in a user defined function and then call it from your derivation, it sounds stupid but it works.

So you create a UDF called LKUP. The script for that UDF is simply: lookup(var1, var2);
And then instead of calling directly the lookup function in your script you use LKUP(v1,v2);