2 Replies Latest reply on Mar 11, 2013 6:37 AM by Hudi

    Help with sql custom function

    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

       

      )

        • 1. Re: Help with sql custom function
          greglane

          You have the variable names inside the quoted string, so they aren't being evaluated. Also, it looks like the where1p, where2p, and where3p parameters contain field names, so you'll want to use the Evaluate function to get the values from those fields to pass to the ExecuteSQL function.

           

          Try something like this:

           

          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; evaluate(where1p); evaluate(where2p); evaluate(where3p))

          ];

           

          VarL

           

          )

          • 2. Re: Help with sql custom function
            Hudi

            Greg,

            Thanks for your reply.

             

            I tried your method and it is still not working. I forgot to mention that I put the parameters in quotes; that likely changes things.

             

            percentrank ( "Table::fieldranked" ; "where1p" ; "where2p" ; "where3p" )

             

            I understand the evaluate for the parameters but not the syntax of the WHERE statements.

             

            Why the "" after the first \, and the "\" after the second. I tried  "\" & where3 & "\" = ? because that seemed more logical to me but that threw me an error.

             

            thanks