4 Replies Latest reply: Mar 15, 2012 12:28 PM by lollo figo RSS

    Adding constant in loaded table with left join

      Hi all,

       

      I have a MAIN table in my script from which I want to extract only 2 fields ([cod_gruppo], [polizza_short]) that satisfy 2 criterias ([cod_gruppo]='222' and [polizza_short]='609') grouped by the 2 fields and add to this COMPUTED table a field with a constant value ([constant]='def').

       

      Once I have the MAIN table and the COMPUTED table (I guess they have to be separate table in order to make the next step) , I want to make a left join between these to tables on the 2 fields ([cod_gruppo], [polizza_short]) so that I can add the field with the constant to the MAIN table on all the fields that appear in che COMPUTED table.

       

      Not sure I make myself clear, but especially not sure how to do it.

       

      All suggestions are appreciated. Thanks

        • Adding constant in loaded table with left join
          j i

          I'm not sure if I understood but your COMPUTED table will have only 1 record?

          [cod_grouppo], [polizza_short], [constante]

          222,                      609                     def

           

           

          Is this what you want to accomplish? If this is the case do:

           

          MAIN:

          LOAD * FROM MAINtable;

           

          COMPUTED:

          LOAD

          [cod_gruppo], [polizza_short], 'def' as constant

          RESIDENT MAIN

          where [cod_gruppo] = '222' and [polizza_short]='609';

           

          Here you will have the 2 tables. then you can left join them

            • Adding constant in loaded table with left join

              Hi, thanks for your reply.

              Sorry, my mistake. The COMPUTED table will have many records (many  [contracts] related to [clients]) that have to be grouped, but only where the fields  [cod_gruppo] and [polizza_short] satisfy the conditions.

              For all the records resulting from this "filtering" I want to add the computed item.

               

              Having the COMPUTED table, I then want to join it with the MAIN table so that i can "mark" the all the [clients] in the main table where they appear in the COMPUTED table. Forget about the [contract] field in the join.

               

              Would be easy in MSaccess....

               

              What would the code be in order to make a left join between the 2 tables that are now resident in QV?