AnsweredAssumed Answered

ExecuteSQL  number of search fields?

Question asked by BruceHerbach on Oct 25, 2017
Latest reply on Oct 25, 2017 by BruceHerbach

I'm trying to set up an ExecuteSQL statement that will have 10 fields used as search parameters.  This was started with the help of seedcodes SQL explorer.  A version of the SQL is below.  In this version is works correctly.  In the Where portion of the statement,  I am using LIKE and for initial testing the variables all have an entry of %.  So it should pull all values from the table. 

 

It seems to work correctly as long as I have 6 or fewer fields to search.  As soon as I add a 7th,  the found set drops to only a record or 2.

 

Is there a limit to the number of fields that can be searched using ExecuteSQL?

 

I have tried putting the code into the dataviewer while the script is running.  Adding and removing the next field and can see where it works with 6 fields and fails when the 7th is added.

 

Any thoughts on how to get this to work would be greatly appreciated.

 

ExecuteSQL code:

Let ( [

// Define Carriage Return Substitution Character

ReturnSub = "\n" ;

// Enable the second line here if you want the header in your results

header = "";

//header = "a.ClientName    a.Lead_Description    a.ID    a.ID_Client    a.ID_Contact    a.ID_Display";

 

 

// Define Table variables

aLEADS = Quote ( GetValue ( Substitute ( GetFieldName ( Leads::ID ) ; "::" ; ¶ ) ; 1 ) ) & " a" ;

 

// Define Field Variables

aID = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Leads::ID ) ; "::" ; ¶ ) ; 2 ) ) ;

aID_Client = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Leads::ID_Client ) ; "::" ; ¶ ) ; 2 ) ) ;

aID_Contact = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Leads::ID_Contact ) ; "::" ; ¶ ) ; 2 ) ) ;

aLead_Description = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Leads::Lead_Description ) ; "::" ; ¶ ) ; 2 ) ) ;

aStatus = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Leads::Status ) ; "::" ; ¶ ) ; 2 ) ) ;

aID_Display = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Leads::ID_Display ) ; "::" ; ¶ ) ; 2 ) ) ;

aClientName = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Leads::ClientName ) ; "::" ; ¶ ) ; 2 ) ) ;

 

aType = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Leads::Type ) ; "::" ; ¶ ) ; 2 ) ) ;

aBuildingType = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Leads::BuildingType ) ; "::" ; ¶ ) ; 2 ) ) ;

aConstructionType = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Leads::ConstructionType ) ; "::" ; ¶ ) ; 2 ) ) ;

 

aWhere = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Leads::Where ) ; "::" ; ¶ ) ; 2 ) ) ;

aStage = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Leads::Stage ) ; "::" ; ¶ ) ; 2 ) ) ;

 

aOurRep = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Leads::OurRep ) ; "::" ; ¶ ) ; 2 ) ) ;

aRating = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Leads::Rating ) ; "::" ; ¶ ) ; 2 ) ) ;

aRegion = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Leads::Region ) ; "::" ; ¶ ) ; 2 ) ) ;

 

 

 

 

// Build SQL Query

q =

"SELECT " & aClientName & " , " & aLead_Description & " , " & aID & " , " & aID_Client & " , " & aID_Contact & " , " & aID_Display & "

FROM " & aLEADS & "

WHERE UPPER( " & aLead_Description & " ) LIKE UPPER( ? ) AND " & aStatus & " LIKE ? AND " & aType & " LIKE ? AND " & aBuildingType & " LIKE ? AND " & aConstructionType & " LIKE ? AND " & aWhere & " LIKE ?

ORDER BY " & aClientName & " ASC, " & aID_Display & " ASC " ;

 

// Run SQL Query

result = ExecuteSQL ( q ; "|||" ; "|*|" ; $srch ; $status ; $type; $btype; $ctype; $where  ) ] ; 

 

// Clean up carriage returns

List ( header ; Substitute ( result ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ]  ) ) & "¶" & $srch & " 2" & $status & " 3" & $type & " 4" & $btype & " 5" & $ctype & " 6" & $where & " 7" & $rating & " 10" & $stage & " 11" & $region & " 12" & $orep  )

Outcomes