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:
Sent: 03-05-2024 11:22
From: Sigrid Kok
Subject: Evolve Lookup element - syntax for raw where clause.
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.
Original Message:
Sent: 03-05-2024 09:34
From: Renette Nienaber
Subject: Evolve Lookup element - syntax for raw where clause.
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
------------------------------