Join problem

[vikasgupta]

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

[#2]

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.

[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;

[vikasgupta]

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

[vikasgupta]

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