The point is: what "unique" date do you have to retrieve from table A? Where is this "unique" date to be found? Can you please explain the business rule how this date can be identified? Then we will find a mapping solution.
So Table A is truncate and load by SAS team where the date field is populated based on specific criteria. However the date in the table for all the records is same, for example 07/04. If the table has 120 records it has 07/04 date for all the records. What we are doing is trying to join Table A with Table B where we have a complete history and when we don't find a record in Table A for the join condition then we want to update the date for that particular record in Table B by using the date(07/04) from Table A. So apparently if the join condition doesn't exist in Table A but exists in Table B, get the date and update Table B for all the records that doesn't exist.
ID Name date
1 Robert 07/03
2 Dawn 07/03
ID Name Updated date
1 Robert 06/23
2 Dawn 06/23
3 Philip 06/23 (This date needs to be updated to 07/03 as this id is not in Table A and hence get the 07/03 date from Table A and update in Table B)
Sorry I had used the same criteria because I don't want to confuse. I am assuming the join here is on ID. Since ID 1 and 2 exists in both tables we don't care about them but ID 3 doesn't exist in Table A and hence we need to update that record (ID 3) with the unique date(07/03) from Table A.
I've created a test case (syntax is from Vertica).
drop table if exists table_a;
create table table_a(id number, name varchar(100), date date);
insert into table_a (id, name, date) values (1, 'Robert', to_date('2021.07.03', 'yyyy.mm.dd'));
insert into table_a (id, name, date) values (2, 'Dawn', to_date('2021.07.03', 'yyyy.mm.dd'));
drop table if exists table_b;
create table table_b(id number, name varchar(100), date date);
insert into table_b (id, name, date) values (1, 'Robert', to_date('2021.06.23', 'yyyy.mm.dd'));
insert into table_b (id, name, date) values (2, 'Dawn', to_date('2021.06.23', 'yyyy.mm.dd'));
insert into table_b (id, name, date) values (3, 'Dawn', to_date('2021.06.23', 'yyyy.mm.dd'));
date = (select max(date) from table_a)
not exists (select 1 from table_a a where a.id = table_b.id);
select * from table_b order by id;
id name date
-- ------ ----------
1 Robert 2021-06-23
2 Dawn 2021-06-23
3 Dawn 2021-07-03
Sorry to say that, but the original poster has clearly stated that tables A and B reside in different databases with no DB links between or anything similar. So this approach can't work.
What I would do is this:
First I would retrieve the date from table A in session 1. This session would create a parameter file for session 2 holding the date/time value from table A in appropriate format.
Session 2 (which essentially runs your current mapping) reads the parameter from the parameter file created by session 1 and uses this parameter in the Source Filter condition. That should do fine, I've seen many processes work that way.
Pull both the tables A & B, do a full outer join, filter the records where table A ID or Name or Date is null which in turn pull only the records that exist only in B.
Pull another source referring same DB connection as A and on SQ Override try,
SELECT DISTINCT 1, DATE
(Hope, it returns only one)
Join both the pipes by creating dummy port with the value of 1 in Pipe M to join with Pipe S.
Thank you very much. All these days I was trying not to use parameter file but appears that is the best solution. Thank you very much Nico, you are the best.
Interesting. I will try this and keep you updated how it goes. Thank you very much for guiding me through this.
One additional note about a mapping parameter for this task:
According to documentation it should work to set up a mapping with two separate target load paths; the first target load path would extract the date from table A and save it to some mapping variable; this mapping variable could then be used in the second target load path in the Source Filter.
Unfortunately that doesn't work as documented. In fact the mapping variable is saved only when the mapping finishes, not when the first target load path finishes. The documentation tells the other way round but is wrong.
There's a Change Request pending to get this behaviour changed according to documentation, but for now it's a fact that this feature cannot be used here; you have to use separate mappings.
Having written that, there is a way to avoid the parameter file. You simply have to replace the parameter file by a workflow variable. This works as follows:
Define a workflow variable of type String. It need not be persistent (although it won't hurt much).
Retrieve the date in the first mapping and save that to a mapping variable of type String.
Edit the session and go to the tab Components. In a Post-Session Success Variable Assignment, hand over the mapping variable to the workflow variable.
Extend the current mapping by defining a mapping parameter of type String.
In a Pre-Session Variable Assignment of the session for this second mapping, hand over the workflow variable to the mapping parameter of the second mapping.
Now you can use this mapping parameter in the Source Filter of the Source Qualifier transformation for table B.
Just one note of caution: make sure that the workflow variable, the mapping variable in the first mapping, and the mapping parameter in the second mapping have different names. Otherwise you will get very interesting runtime errors where the sessions will fail, but the error messages do not indicate the real cause of trouble.
For example, if the mapping variable in mapping 1 would be named $$STARTDATE and the workflow variable would also be named $$STARTDATE, the workflow would be valid, but it won't run because the first session will fail with some completely misleading error message.
Mapping variable has two values - Start and Current.
When you use mapping variable for any calculation you use Start value. It is constant for whole mapping including it's pipelines. It is very correct approach.
All functions like SetVariables change only Current value. This value is invisible in any transformations in mapping.
So all works according manuals:
"When you use a mapping variable ('$$MAPVAR') in an expression, the expression always returns the start value of the mapping variable."
In other words, mappping variable is a mapping parameter that can be changed (and stored in the repository) automatically upon successful completion of the session history.
lets say that the documentation is a just little bit confusing. The $$MAPVAR will return the start value, if you want the current value than you have to use SETVARIABLE($$MAPVAR,NULL) which will return the current value.The Integration Service holds two different values for a mapping variable during a session run:Start value of a mapping variableCurrent value of a mapping variableThe current value of a mapping variable changes as the session progresses. To use the current value of a mapping variable within the mapping or in another transformation, create the following expression with the SETVARIABLE function:
SETVARIABLE($$MAPVAR,NULL)At the end of a successful session, the Integration Service saves the final current value of a mapping variable to the repository.
I agree, I got excited)).
But $$Mapvar is a constant for the mapping and has Start value.
There is also a way to build this in one simple mapping
First you read and join table A and B to get the minus set and this result set you join (full outer join) with a source qualifier on table A (only the date field is read and check the select distinct as well)