3 Replies Latest reply on May 1, 2016 11:17 PM by user19752

    Best way to deal with ExecuteSQL empty return values?

    bigtom

      When I have a I have a query like:

      "SELECT a, b, c, d FROM Table WHERE IDpk = ?"; "¶"; ""; $id)

       

      This gives me a return delimited list of the selection. I want this list to work with GetValue predictably but when one or more of the return values is empty it causes a problem. What is the best way to deal with this? Replacing "" with a known value for an empty field is not really an option in this case.

        • 1. Re: Best way to deal with ExecuteSQL empty return values?
          user19752

          If you want to remove empty line

          Evaluate ( "List( \"" & Substitute ( resultOfSQL ; [ "\"" ; "\\\"" ] ; [ ¶ ; "\";\"" ] ) & "\")" )

           

          Or, you use pk in WHERE then it may be fast enough querying 4 times.

          SELECT a FROM Table WHERE IDpk = ? AND a IS NOT NULL

          UNION ALL

          SELECT b FROM Table WHERE IDpk = ? AND b IS NOT NULL

          UNION ALL

          SELECT c FROM Table WHERE IDpk = ? AND c IS NOT NULL

          UNION ALL

          SELECT d FROM Table WHERE IDpk = ? AND d IS NOT NULL

           

          //FMSQL doesn't change the order of unionized result.

          hoo? it can be List() ed without UNION more stablly?

          • 2. Re: Best way to deal with ExecuteSQL empty return values?
            bigtom

            I think you have missed the thing I am looking for.

            I need the values in variables separately. Currently this is done with four separate queries into separate variables. This is slower than I would like and I would like to get it into one query to cut down on the query time. However sometimes there is a null value in the data.

             

            So instead of getting

            $sqlResult = "a¶b¶c¶d"

            and then using

            $a=GetValue(sqlResult; 1),

            $b=GetValue(sqlResult; 2),

            $c=GetValue(sqlResult; 3),

            $d=GetValue(sqlResult; 4)

             

            There is an issue when one or more of the result parameters are missing. For example, when b is null/empty:

            $sqlResult = "a¶c¶d"

            • 3. Re: Best way to deal with ExecuteSQL empty return values?
              user19752

              So your example SQL is something different.

               

              SELECT a,b,c,d

              returns always 4 values regardless of NULL or not, using column delimiter as ¶ you can get 4 lines

              "a¶¶c¶d"