If you want to append something to the SQL being sent to the database, orto manipulate that SQL in any way, I would do so in the SQLPREVIEW event of the datawindow.

Hi Brian,

To get the string to your retrieve function you'll need to add some arguments to your controller method. Here is a basic example.  I'm not sure if you need more than one argument sent to your retrieve function so in my example I've included 2 arguments, you can extend it if you need more arguments or remove the second one if you don't need it.  The arguments are passed as query parameters to the controller and then passed onto the retrieve function of the service which will then dynamically modify the sql before retrieving.  I don't know what your def_lname column is, I'm assuming last name, so I've added an additional argument called firstName to demonstrate the technique. You will just need to update it to the appropriate column names.  To call the api you would append the arguments, so these calls would all be valid:

gshost + "/api/d_caseload_main/retrieve"

gshost + "/api/d_caseload_main/retrieve?lastName=J%"

gshost + "/api/d_caseload_main/retrieve?lastName=J%&firstName=A%"

The examples below aren't real code from a project so they may not compile, but hopefully they give you a good enough idea of the technique.

In the Controller Class you would need to accept the arguments from the query parameters. An example would be:

// GET api/d_caseload_main/retrieve?lastName=J%&firstName=A%
[HttpGet()]
[ProducesResponseType(typeof(D_Caseload_Main), StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status404NotFound)]
[ProducesResponseType(StatusCodes.Status500InternalServerError)]
public ActionResult<IDataStore> Retrieve([FromQuery] string lastName, [FromQuery] string firstName)
{
	try
	{
		var caseLoad = _caseLoadService.RetrieveDynamicAsync(lastName, firstName, CancellationToken.None);

		if (caseLoad.RowCount == 0)
		{
			return NotFound();
		}
		else 
		{
			return Ok(caseLoad);
		}
	}
	catch (Exception ex)
	{
		return StatusCode(StatusCodes.Status500InternalServerError, ex.Message);
	}
}

Then in your service class you would need to implement the RetrieveDynamicAsync method, for example:

public async Task<IDataStore<D_Caseload_Main>> RetrieveDynamicAsync(string lastName, string firstName, CancellationToken cancellationToken)
{
	var dataStore = new DataStore<D_Caseload_Main>(_dataContext);

	string whereSql = "";

	// Note, the input arguments should probably be sanitised, or sanity checked to prevent sql injection type issues

	if (!String.IsNullOrEmpty(lastName)) 
	{
		whereSql = whereSql + String.Format(" and def_lname like '{0}' ", lastName);
	}

	if (!String.IsNullOrEmpty(firstName)) 
	{ 
		whereSql = whereSql + String.Format(" and def_fname like '{0}' ", firstName);
	}

	if (!String.IsNullOrEmpty(whereSql)) 
	{ 
		string sql = dataStore.GetSqlSelect();

 		// this is just appending your dynamic where to the existing sql statement
		sql += whereSql

		// or alternatively you could have a placeholder in your sql that you 
		// replace instead of just appending
		//sql = sql.Replace("--{DYNAMIC-WHERE}--", whereSql);
 
		// success should be checked and an exception raised if false
		bool success = dataStore.SetSqlSelect(sql);
	}

	await dataStore.RetrieveAsync(new object[] { }, cancellationToken);

	return dataStore;
}

Update...

I realized why the code that was inserted into the Service did not work.  I was using a datawindow object that had retrieval arguments already, and that doesn't work with SetSQLSelect in regular PowerBuilder either.  I tested on a different datawindow which did not have any retrieval arguments and it seems like it works when injecting a hard-coded string to the end of my SQL. 

Now all I need to figure out is how to get the string value sent to the retrieve function in the first place, so it can be appended.