Automate

 View Only

  • 1.  Evolve Lookup element - syntax for raw where clause.

    Posted 03-05-2024 09:34
      |   view attached

    Raw "Where" Clause

    Allows you to specify a custom where clause for a SQL statement. If you are using Search Column, Search Operator, and Search Key Field XPath, it will result in a where clause that includes only a single condition. If you want more conditions, you must specify a custom where clause.

    I need to look up a field value based on more than one column / field.

    Kindly assist with syntax for custom where clause and lookup element settings.

    Datasource with columns:

    Category

    Role

    User

    Set form field user_default = User

    Where 

    Category = '[/my:myFields/my:Company]'  and Role = '[/my:myFields/my:role_V]'



    ------------------------------
    Renette Nienaber
    Data Management specialist
    VIVO ENERGY SOUTH AFRICA (PTY) LTD
    ------------------------------


  • 2.  RE: Evolve Lookup element - syntax for raw where clause.

    Employee
    Posted 03-05-2024 11:22

    Hi Renette

    You would use whatever you could use directly in SQL, minus the "WHERE", for example:

     [ClassName] = '[/my:myFields/my:SolutionFieldsGroup/my:ClassName]' and [CharacteristicNameDescription] = '[/my:myFields/my:SolutionFieldsGroup/my:CharacteristicNameDesc]'

     Lookup control shows:

    I had accidentally included the WHERE (it's been a while), but the Evolve logs caught it

    [ClassNameCharName], [ClassType] from [ClassCharPossibleValues_HF7] Where WHERE  [ClassName] = 'SK-MATCLASS' and [CharacteristicNameDescription] = 'Grade' order by [ClassName] asc,[CharacteristicNameDescription] asc,[CharacteristicValueDescription] asc|
    2024-03-05 08:13:10.6482|ERROR|77|SEA-EN-VSPEVS|Winshuttle.Finx.SharedInfra.SolutionConnectionSQLAdapter.GetData|Incorrect syntax near the keyword 'WHERE'.|System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'WHERE'.

    HTH

    Sigrid



    ------------------------------
    Sigrid Kok
    Precisely Software Inc.
    ------------------------------



  • 3.  RE: Evolve Lookup element - syntax for raw where clause.

    Posted 03-06-2024 03:21

    Hi Sigrid, I really appreciate all your support.

    I have now successfully accomplished by utilizing the Evolve lookup element as well as the Evolve query element. Different syntax but same desired results. Thank you!



    ------------------------------
    Renette Nienaber
    Data Management specialist
    VIVO ENERGY SOUTH AFRICA (PTY) LTD
    ------------------------------

    Original Message