Hudi

Help with sql custom function

Discussion created by Hudi on Mar 8, 2013
Latest reply on Mar 11, 2013 by Hudi

I'm trying to write a function that will run an executeSQL function. Its pretty long and its being used alot therefore the need to write this function.

 

Here is the basic structure of it. Its much longer than this but if this part will work the rest is easy.

 

All the Let variables have been tested and are coming through correctly. The issue is in the SQL function.

Any help is extremely appreciated.

 

 

 

percentrank ( field ; where1p ; where2p ; where3p ) =

 

 

 

Let(

 

 

[ fieldname =Right ( field;Length(field) - Position ( field ; "::" ; 1 ; 1 )-1);

tablename = Left ( Field; Position ( Field ; "::" ; 1 ; 1 ) -1);

where1 = Right ( where1p;Length(where1p) - Position ( where1p ; "::" ; 1 ; 1 ) -1);

where2 = Right ( where2p;Length(where2p) - Position ( where2p ; "::" ; 1 ; 1 ) -1);

where3 = Right ( where3p;Length(where3p) - Position ( where3p ; "::" ; 1 ; 1 ) -1);

 

 

VarL = ExecuteSQL ("

 

 

SELECT COUNT( "fieldname" )

 

 

FROM "tablename"

 

 

WHERE "fieldname" < ?

and

"where1" = ?

and

"where2" = ?

and

"where3" = ?

"

  ; "" ; "" ;field; where1p; where2p; where3p)

];

 

VarL

 

)

Outcomes