How to set relation on multiple key using SQL Select

[alisaif]

Hi

I have four tables which are as follows:

A. SIMAIN.DBF
-------------
1. Sino c(8)
2. pcode c(10)
3. Invdate d
4. Location c(40)
5. Amount
6. ..
7. ..

B. SISUB.DBF
------------
1. Sino c(8)
2. Icode c(10)
3. QntyMin N(10,3)
4. RateMin N(10,4)
5. ...
6. ...

C. PCODE.DBF
------------
1. Pcode c(8)
2. CustName c(53)
3. ..
4. ..

D. LOCATION.DBF
---------------
1. Pcode c(8)
2. Location c(40)
3. Address c(60)
4. Tel c(10)
5. Fax c(10)
6. ..
7. ..

Now, what I want is to make a relation on multiple field (pcode+location) in order to obtain the sales transaction based on City, Country by using the SQL Select, something like this

SELECT ;
 FROM ;
     village!simain ;
    INNER JOIN village!sisub ;
   ON  Simain.sino = Sisub.sino ;
    INNER JOIN village!customer ;
   ON  Simain.pcode = Customer.pcode ;
    INNER JOIN village!location ;
   ON  Simain.location = Location.location;
 GROUP BY Simain.sino, Simain.invdate, Simain.pcode, Simain.location,;
  Sisub.icode, Sisub.qntymin, Sisub.ratemin, Customer.full_name,;
  Location.address, Location.city, Location.country;
 INTO CURSOR tmpSales READWRITE

Kindly guide me how can I do so?

Thanks

Saif

 

[alisaif]

Thanks for the reply! Sometime users open locations without entering city or country name which is not mandatory. In that case, there is sure a lot of country name is blank in custloca. In that case what join is suitable and how can I replace "Not Mentioned" in Country field.

Thanks

Saif

 

[alisaif]

Thank Mr.Mjcmkrsr

I applied inner join as you mentioned, but not replacing field with 'Not Mentioned' and query is also very slow

Select Simain.Invdate, Sisub.qntymaj,;
   Sisub.qntymin, Sisub.ratemaj, Sisub.ratemin, ;
   NVL(location.country,PADR("Not mentioned",LEN(location.country)) AS COUNTRY;
   FROM ;
   village!Simain ;
   inner Join village!Sisub ;
   ON  Simain.Sino = Sisub.Sino;
   left Join location ON simain.pcode = location.pcode AND simain.location = location.location
   WHERE Between(Invdate,dt1,dt2) And Simain.pcode # 'CP011' ;
   INTO Cursor Sijunk Readwrite nofilter

Saif

 

[Olaf Doschke]

If if no record in custloca matches the join confition simain.pcode = custloca.pcode AND simain.location custloca.country, then you don't know a country for that simain record. Fix your data, or tell us how it should be joined instead. We can't tell you how to. You must know how your data is related to each other.

Bye, Olaf.

 

[mjcmkrsr]

Hi

Sometime users open locations without entering city or country name which is not mandatory. In that case, there is sure a lot of country name is blank in custloca.

If that's the case what sense does it make to query on country and city?
You may want to reconsider your table structures and/or force the users to enter the requested values.
hth
MK

 

[alisaif]

Hi

what sense does it make to query on country and city?

We treat blank record as local customer.

Saif

 

[Olaf Doschke]

>We treat blank record as local customer.

Well, but NULL is not the same as blank.

If NVL(location.country,PADR("Not mentioned",LEN(location.country)) AS COUNTRY results in "Not mentioned", then you didn't have a location matching, not evene a location with a blank county.

Bye, Olaf.

 

[Olaf Doschke]

Make it this way to see the difference of blank vs not found locations:

Select Simain.Invdate, Sisub.qntymaj,;
   Sisub.qntymin, Sisub.ratemaj, Sisub.ratemin, ;
   PADR(EVL(NVL(location.country,"No location found"),"not mentioned (local)"),LEN(location.country)) AS COUNTRY;
   FROM ;
   village!Simain ;
   inner Join village!Sisub ;
   ON  Simain.Sino = Sisub.Sino;
   left Join location ON simain.pcode = location.pcode AND simain.location = location.location
   WHERE Between(Invdate,dt1,dt2) And Simain.pcode # 'CP011' ;
   INTO Cursor Sijunk Readwrite nofilter

Bye, Olaf.

 

[alisaif]

It is confirmed that the following is query is correct which I checked through my old style described above.

Select Simain.Sino, Simain.Invdate, Simain.pcode, Simain.discrem, Simain.Zone,;
   Simain.discamount, Simain.merchand, Simain.salesman,;
   Simain.location, Sisub.sino, Sisub.icode, Sisub.qntymaj,;
   Sisub.qntymin, Sisub.ratemaj, Sisub.ratemin, ;
   custloca.country,;
   custloca.city;
   FROM ;
   village!Simain ;
   inner Join village!Sisub ;
   ON  Simain.Sino = Sisub.Sino;
   left Join custloca ON simain.pcode = custloca.pcode AND simain.location = custloca.location ;
   WHERE Between(Invdate,dt1,dt2) And Simain.pcode # 'CP011' ;
   INTO Cursor Sijunk Readwrite nofilter

I just want to fill the blank with 'Not Mentioned'
"NVL(location.country,PADR("Not mentioned",LEN(location.country))" is not working.

Thanks

Saif

 

[mjcmkrsr]

Hi

I just want to fill the blank with 'Not Mentioned'
"NVL(location.country,PADR("Not mentioned",LEN(location.country))" is not working.

Of course not - either copy the whole string which is

PADR(EVL(NVL(location.country,"No location found"),"not mentioned (local)"),LEN(location.country)) AS COUNTRY;

or balance the parenthesis

NVL(location.country,PADR("Not mentioned",LEN(location.country)))

hth

MK

 

[Olaf Doschke]

>I just want to fill the blank with 'Not Mentioned'
>"NVL(location.country,PADR("Not mentioned",LEN(location.country))" is not working.

Besides what MK say, you still haven't understood the difference, you should use the full expression to see, whether you have blank locations or no location, that would be a big difference and point out problems of the data integrity.

Bye, Olaf.

 

[alisaif]

I got it !!

Thanks

Saif