4 Replies Latest reply: Jan 3, 2013 3:25 AM by Vikas Gupta RSS

    Join problem

    Vikas Gupta

      Hi every one

       

      I have two tables location master and Transaction table

       

       

      /************Location master ********/

      LocationMaster:

      LOAD

           LocCd,

           LocCd,

           LocCoCd,

           LocDesc,

           LocTyp

      FROM

      [..\QVD\LocationMaster.Qvd]

      (qvd);

       

      /************Transaction master ********/

      Txnd:

       

      LOAD

          TdRmSz,

          TdRmStkRt,

          TdRmQty,

          TdFrRmLoc,

          TdToRmLoc,

          TdFrRmDc

       

      FROM

      [..\QVD\Txnd.qvd]

      (qvd);

       

      here condition is that i want to join LocCd from location master to Transaction master on TdFrRmLoc and TdToRmLoc

       

      means condition is that join loc on (LocCd=TdFrRmLoc or LocCd=TdtoRmLoc) to access Loctyp .

       

      Thanks

      Vikas

        • Re: Join problem
          Kabilan Kumarasamy

          U can write a script like below.

           

          LocationMaster:

          LOAD

               LocCd as Key,

               LocCd,

               LocCoCd,

               LocDesc,

               LocTyp

          FROM

          [..\QVD\LocationMaster.Qvd]

          (qvd);

           

          Inner join

           

          LOAD

              TdRmSz,

              TdRmStkRt,

              TdRmQty,

              TdFrRmLoc as Key

              TdFrRmLoc,

              TdToRmLoc,

              TdFrRmDc

           

          FROM

          [..\QVD\Txnd.qvd]

          (qvd);

           

          Concatenate

          LocationMaster:

          LOAD

               LocCd as Key,

               LocCd,

               LocCoCd,

               LocDesc,

               LocTyp

          FROM

          [..\QVD\LocationMaster.Qvd]

          (qvd);

           

          Inner join

           

          LOAD

              TdRmSz,

              TdRmStkRt,

              TdRmQty,

              TdToRmLoc as Key

              TdFrRmLoc,

              TdToRmLoc,

              TdFrRmDc

           

          FROM

          [..\QVD\Txnd.qvd]

          (qvd);

           

          I hope , it helps u

           

          ~Kabilan K.

          • Re: Join problem
            Gysbert Wassenaar

            I assume that TdFrRmLoc and TdtoRmLoc are the From- and To- locations. So they are two different things. That means you can't join both fields to one LocationMaster table directly. You can do several things.

            1. Use a mapping table to add LocTyp (and perhaps LocDesc) to the Txnd table for both From- and To- locations.
            2. Create two LocationMaster tables, one for From- locations and one for To- locations
            3. Create a link table
            4. Use LocationMaster as a data island and don't associate it with Txnd. Use field triggers make selections.

             

            Option 2 is the easiest to do:

            LocationsFrom:

            LOAD

                 LocCd as TdFrRmLoc,

                 LocCoCd as LocFromCoCd,

                 LocDesc as LocFromDesc,

                 LocTyp as LocFromTyp

            FROM

            [..\QVD\LocationMaster.Qvd]

            (qvd);

             

            LocationsTo:

            LOAD

                 LocCd as TdToRmLoc ,

                 LocCoCd as LocToCoCd,

                 LocDesc as LocToDesc,

                 LocTyp as LocToType

            FROM

            [..\QVD\LocationMaster.Qvd]

            (qvd);

             

             

            Option3 is fairly easy too and gives you a bit more flexibility:

            LocationLink:

            TdRmSz,            //assuming this is the primary key of Txnd, if not replace it with the field that is

            TdFrRmLoc as LocCd,

            'From' as LocationGroup

            resident Txnd;

             

            Concatenate (LocationLink)

            TdTmSz,

            TdToRmLoc as LocCd,

            'To' as LocationGroup

            resident Txnd;


              • Re: Join problem
                Vikas Gupta

                Hi Gysbert

                 

                I used second option given by you and created two tables of location master i also tried third option but Txn table have millions of records so I did not use third option .

                 

                Now i am stuck with condition and dimension used for expression so i want to ask that which is more suitable for for following function .

                 

                My  (SQL) function is following which i want to calculate :-

                 

                ===============================================================

                select * from

                (select loccd,

                 

                sum(case when TdFrRmLoc=loccd and TdFrRmDc='c' and tdt between '01/06/05' and '30/08/05'  then TdRmWt else 0 end)+

                 

                sum(case when TdToRmLoc=loccd and TdToRmDc='C' and tdt between '01/06/05' and '30/08/05' then TdRmWt else 0 end))

                 

                 

                from  txn join txnd

                on ttc=tdtc and tyy=tdyy and tchr=tdchr and tno=tdno 

                join loc on (LocCd=TdFrRmLoc or LocCd=TdtoRmLoc) and LocTyp='S' and loccocd='DDL'

                join RmMst on RmCd=TdRmCd

                Where Rmctg IN ('d')

                group by loccd)

                 

                =================================================================================

                No. of table and its records

                Txn:64195 

                Txnd: 2659256 Rows

                Loc:64 Rows

                 

                Txn and Txnd already joind by Key . 

                 

                Thanks

                Vikas Gupta

                • Re: Join problem
                  Vikas Gupta

                  Hi Everyone

                   

                  Can anyone help me about how to apply field Triggers for above condition means join Location master to Txnd table .I wants to join TdFrRmLoc and  TdToRmLoc to location master for accessing LocTyp.

                   

                   

                  Thanks

                  Vikas