So, I have import the data, do any shaping, and finally I want to update the status into the database. I need to reference some value from one of the column. Is it possible to do it?
A column may contain multiple values, but a parameter has only 1 value so actions that use parameters expect only 1 value – that’s why they don’t allow using column names.
To deal with case when you need a column value to be used as a parameter, you can create a 1-row table with that value, and the use the “Iterate” action to assign it to a parameter of another project, and run that project.
See below example that takes a date from a column, and then runs a PowerShell command where that date is provided using a parameter.
parent.morph (2.5 KB)
child.morph (1.1 KB)
parent.morph
child.morph
Sorry I did not make it clear enough. What I want to do is to keep several sql in one transaction.
Basically, say if the current table has a name called ‘current_table’, what I want to do is something like this:
begin
insert into transaction_table select col1, col2, {current_user} user, {current_time} timestamp… from current_table;
delete from target_table t inner join current_table r on t.key = r.key
where t.value = r.value and …;
insert into target_table select * from current_table;
…
insert into log_table values({username} , cast(count(*) as varchar(10)) + ’ records have been updated’);
end;
The main idea is to put all the sql into one transaction.
I am kind of guess the answer is NO.
The reason is ODBC connection is sending SQL command to server and then get result back. So there is no way to reference a local table from server side. This includes a join command in between server and local. So If I have complicate operation in between two or more tables, I will have to either bring all of them to local (EasyMorph), or send them all to server.
Is that correct?
Most probably you will need to construct this SQL query using expressions (and aggregations) with column values, then pass it to the Custom SQL command via a parameter using the method I described above (with example).
For instance, you create a new column with expression such as below:
[sql clause] = "insert into log_table values(" & [username] & " , cast(count(*) as varchar(10)) + ’ records have been updated’);"
Then use the "Aggregate" action in the concatenation mode to concatenate everything into one list of statements. Finally, append opening and closing statements for a transaction. As a result you should have one cell that contains entire query. Then pass it via parameter to Custom SQL command as described above.
If by "local table" you mean an EasyMorph table then no, cross-system join is not possible. Joining two tables can only be done when both tables are either in the database (in this case the database does the join), or in EasyMorph (in this case EasyMorph does the join).
That is a good idea. I may think about it if I have to do so.
Just to confirm, say I have a table with 2 columns: key and value and 2 rows. what I need to do is to aggregate all the values and hence I build a sql like so
insert into target values(key1,value1),(key2,value2);
Then pass this value to a new project which will accept this value. So in the new project I will run a sql command with that para.
Begin
{MultipleSqlCmdsReceived};
End;
That really does the job!
Question: Is there a length limit on the parameter?
A cell/parameter text value can be at least a few million characters.
Cool! That should be way more than enough!
Thanks for help!
You’re welcome 