13 Replies Latest reply on Jul 7, 2021 2:13 AM by JanLeendert Wijkhuijs

    How to get date from Table A when we do Table B minus Table A

    Arya Nymeria Active Member

      Hello, I am stuck at a point where I am not able to move forward. I am trying to find records that exists in Table B but not in Table A which is easy but the caveat is that I need a column in Table A that I need to use to update Table B.

       

      Table A               

      ID        Name               date

      1         Robert              07/03

      2         Dawn                07/03

       

      Table B

      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)

       

      When i do the join i was able to get the key from the Table B that doesn't exist in Table A but I need to get the date which is unique in the Table A to update in Table B as it will be null upon the join. Any other suggestions to bring the date? Table A and Table B are in two different databases(sql server) with no DB links. Any help on this is greatly appreciated. Thank you very much.

        • 1. Re: How to get date from Table A when we do Table B minus Table A
          Nico Heinze Guru

          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.

           

          Regards,

          Nico

          • 2. Re: How to get date from Table A when we do Table B minus Table A
            Arya Nymeria Active Member

            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.

             

             

             

            Table A              

            ID        Name               date

            1         Robert              07/03

            2         Dawn                07/03

             

            Table B

            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)

            • 3. Re: How to get date from Table A when we do Table B minus Table A
              Arya Nymeria Active Member

              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.

              • 4. Re: How to get date from Table A when we do Table B minus Table A
                Alexandru Stan New Member

                Hi,

                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'));

                 

                commit;

                 

                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'));

                 

                commit;

                 

                update table_b

                set

                  date = (select max(date) from table_a)

                where

                  not exists (select 1 from table_a a where a.id = table_b.id);

                 

                commit; 

                 

                 

                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

                 

                Regards,

                Alexandru Stan

                • 5. Re: How to get date from Table A when we do Table B minus Table A
                  Nico Heinze Guru

                  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.

                   

                  Regards,

                  Nico

                  • 6. Re: How to get date from Table A when we do Table B minus Table A
                    Akilan Chandrasekaran Active Member

                    Pipe M:

                    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.

                     

                    Pipe S:

                    Pull another source referring same DB connection as A and on SQ Override try,

                     

                    SELECT DISTINCT 1, DATE

                    FROM TABLE_A

                    (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.

                    • 7. Re: How to get date from Table A when we do Table B minus Table A
                      Arya Nymeria Active Member

                      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.

                      • 8. Re: How to get date from Table A when we do Table B minus Table A
                        Arya Nymeria Active Member

                        Interesting. I will try this and keep you updated how it goes. Thank you very much for guiding me through this.

                        • 9. Re: How to get date from Table A when we do Table B minus Table A
                          Nico Heinze Guru

                          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.

                           

                          Regards,

                          Nico

                          • 10. Re: How to get date from Table A when we do Table B minus Table A
                            Vlad Ponomarenko Guru

                            Hi all,

                            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.

                             

                            Regards, Vlad

                            • 11. Re: How to get date from Table A when we do Table B minus Table A
                              Alexandru Stan New Member

                              Hi Vlad,

                              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.

                               

                              Variable Values

                               

                              The Integration Service holds two different values for a mapping variable during a session run:
                              Start value of a mapping variable
                              Current value of a mapping variable

                               

                               

                              The 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.

                               

                              Regards,

                              Alexandru Stan

                              • 12. Re: How to get date from Table A when we do Table B minus Table A
                                Vlad Ponomarenko Guru

                                Hi Alexandru,

                                I agree, I got excited)).

                                But $$Mapvar is a constant for the mapping and has Start value.

                                 

                                Regards Vlad

                                • 13. Re: How to get date from Table A when we do Table B minus Table A
                                  JanLeendert Wijkhuijs Guru

                                  Hi,

                                   

                                  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)
                                  Regards,
                                  JanLeendert