Assign SQL Result to Parameter

1

I would like to assign the result of a SQL query to a parameter.
I’m currently using the “Import from Database” action to retrieve a one row and column table based upon a filter. However, I would like to perform different actions based upon whether a value is found or not.

If the lookup returns a value, then assign the primary key to a parameter. If it’s not found, then I would need an action that would insert a row into the same table, and then assign the key for the new row to the parameter.

I would like to use this key in other actions on other tables in the database to insert values.

I have created a “Database command” action that uses custom SQL to perform the above, but I would still need to retrieve the primary key as a parameter. (This was working previously, but now I’m getting a ‘Parameter index is out of range’ warning/error)

I suppose I could run the custom SQL action first, then perform the lookup?
However, I would still need to assign the key to a parameter.

2

You can use conditional branching with derived tables to have two branches of logic depending on a value is found or not. See the tutorial article on conditional branching here: EasyMorph | Conditional workflows

The "Export to database" action can insert a row into the table. You can use it in the workflow branch that should be executed when no value was found.

Note that besides parameters, you can use first column values to define action properties. See Announcement: Using first column value in action properties

I don't see why you would need custom SQL here. You can do all of that with EasyMorph actions.

3

The reason I need to use the custom query is quite simple, it creates new dates for the new entry in the table based upon the most recent entry already in the table. The starting date for the new entry is based upon a calculated parameter, by passing that into the custom query I can create the new entry if it doesn’t already exist.

This also allows me to use the same logic in multiple places without having to maintain it in different forms.

I believe I’m close to having a solution, I’m going to continue reading about how to use the first column value in actions, though I would prefer to assign the result (primary key) to a parameter.
For example, how could I add this value as a new column in an EasyMorph table?

4

Assuming that you have returned the key from the query in one table you can append it to another EasyMorph table as a column using, for instance, the “Peek” action.

Parameters are constant (immutable) within a module, they are not variables. You can assign a parameter with a column value when you call or iterate one module from another module. The “Call” and “Iterate” actions allow assigning parameters of the called module.

5

Thanks for the clarification!

I had just stumbled upon the idea of using an action to append the table as a column, and then just added another to fill it down. So I should be able to use that approach.