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

 

[mjcmkrsr]

Hi,

Reading the names and structure of your tables it would be something like follows

SELECT ... FROM SiMain ; 
JOIN SiSub on Simain.SiNo = SiSub.SiNo ;
JOIN PCode on Simain.SiNo = PCode.PCode ;
JOIN Location on Simain.SiNo = Location.PCode ;
ORDER BY ... ;
GROUP BY ... ;
INTO ....

hth
MK

 

[alisaif]

Thanks for the reply, I am facing an error "Pcode is not unique and must be qualified".

(Note I changed the pcode.dbf with customer.dbf)

SELECT Simain.sino, Simain.invdate, Simain.pcode, Simain.location,;
Sisub.icode, Sisub.qntymin, Sisub.ratemin, Customer.full_name,;
Location.address, Location.city, Location.country FROM SiMain ;
JOIN SiSub on Simain.SiNo = SiSub.SiNo ;
JOIN Customer on Simain.pcode = Customer.PCode ;
JOIN Location on Simain.location = Location.PCode ;
WHERE Between(invdate,dt1,dt2) And pcode # 'CP011' ;
INTO CURSOR SiJunk READWRITE NOFILTER

Thanks

Saif

 

[mjcmkrsr]

Hi,
You might want to correct the code below as follows

SELECT Simain.sino, Simain.invdate, Simain.pcode, Simain.location,;
Sisub.icode, Sisub.qntymin, Sisub.ratemin, Customer.full_name,;
Location.address, Location.city, Location.country FROM SiMain ;
JOIN SiSub on Simain.SiNo = SiSub.SiNo ;
JOIN Customer on Simain.pcode = Customer.PCode ;
JOIN Location on Simain.location =  [i][b][u]Location.Location [/b][/i][/u] ;
WHERE Between(invdate,dt1,dt2) And [i][b][u]simain.pcode[/b][/i][/u] # 'CP011' ;
INTO CURSOR SiJunk READWRITE NOFILTER

hth
MK

 

[mjcmkrsr]

...
please be aware that Simain.PCode = C(10) whereas Customer.PCode = C(8)and Location.PCode = C(8) - results from tables joined on PCode with different lenghts may not be accurate
hth
MK

 

[Olaf Doschke]

Was your original question also about the error "...is not unique and must be qualified"
This is simply stating you have several tables in your query which have a pcode field, therefore just putting And pcode # 'CP011' in your where clause is too unspecific about which pcode you mean. Even though you join Simain.pcode = Customer.PCode and so it won't matter what pcode field is taken, SQL is no artificial intelligence seeing that. If there are two fields with same names, as you have many, you need to use mo9re qualified names, meaning be more specific about what exact field you want. Especially what MK says is important, but the error would also occur if you compare with a Code of length 10, that could only mean simain.pcode.

Overall I see the need to change you data design, but if we go into detail about that we'd start a discussion about natural vs surrogate keys.

In regard to the question you put in the thread title, the term for SQL is not relation but join,m and you join by a join condition, that can be anything which results in a boolean value (true or false), so a join can be anything from ON TABLE1.FIELDX = TABLE2.FIELDY to a condition combining several simple conditions with AND and/or OR, or ,aking use of functions returning booleans. As much as you want, and as few as you want and need. So if you need two fields matching, nothing and nobody hinders you to expand the join condition to as many comparisons of as many fields you want to match. You could also make a join only work on christmas via ON MONTH(DATE())=12 AND DAY(DATE())=25. It doesn't matter, you can do what you want, not only in the WHERE clause. If you want to have the answer about what fields to compare, that solely depends on the meaning of the data and simply listing the tables and field names doesn't explain what is related with what other data. You should know that yourself.

Bye, Olaf.

 

[alisaif]

Thanks for the reply and suggestions.

I did the following in order to get my result:

Select tpcode.pcode, tpcode.full_name, tpcode.cgroup, tpcode.cr_limit, ;
   tpcode.cdate, tpcode.op_bal, tpcode.lok, tpcode.prtdsp, tpcode.pterms,;
   tpcode.hold, tpcode.email, tpcode.website, tloca.location, tloca.manager,;
   tloca.salesman, tloca.merchand, tloca.zone, tloca.ctitle, tloca.person,;
   tloca.address, tloca.area, tloca.city, tloca.country, tloca.ccode, tloca.tel1, tloca.tel2,;
   tloca.tel3, tloca.fax, tloca.mobile, tloca.localok ;
   from customer tpcode, location tloca ;
   where tpcode.pcode = tloca.pcode ;
   into Cursor custloca Readwrite
Index On pcode+location Tag ploc For ! localok

Then I generate

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 ;
   FROM ;
   village!Simain ;
   inner Join village!Sisub ;
   ON  Simain.Sino = Sisub.Sino;
   WHERE Between(Invdate,dt1,dt2) And Simain.pcode # 'CP011' ;
   INTO Cursor Sijunk Readwrite nofilter

Then I created two fields

   ALTER table SiJunk ADD COLUMN country c(52)
   ALTER table SiJunk ADD COLUMN city c(52)

I set relation to custloca and replace all city,country from custloca to SiJunk

Then I did like this

Sele Year(invdate) As theyear, ;
   Country As Country,;
   sum(qntymaj) As cartons, ;
   sum(qntymin) As units, ;
   sum(Iif(ratemin#0,qntymin*ratemin,qntymaj*ratemaj)) As mamount ;
   from sijunk ;
   group By theyear, country ;
   into Curs tempsi Readwrite

So, the main target is "Tempsi" and I was seeking SQL Command to get the result directly from the tables mentioned above.

Thanks

Saif

 

[Olaf Doschke]

Well, I don't think I'll dive into this too deep to put all of this into a single query, but some suggestions:

If you do things like ALTER TABLE, REPLACE, you can process many fields at once, so don't do two ALTER TABLEs, add both columns in one go. EACH alter table is rewriting the whole dbf and cdx and fpt so you save half the time. Even if it's just a small amount of data, it even saves you some typing. ALTER TABLE tablename ADD COLUMN country c(52) ADD COLUMN city C(52)

You can add fields in a query, so you don't need ALTER TABLE at all:

Select Simain.Sino, Simain...
..., ;
..., ;
..., Sisub.ratemin ;
,SPACE(52) as country;
,SPACE(52) as city;
FROM....

And you can even add the values you want there from custloca by joining it, why don't you simply join it?

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;
   [b]left Join custloca ON simain.pcode = custloca.pcode [u]AND[/u] simain.location = custloca.location[/b]
   WHERE Between(Invdate,dt1,dt2) And Simain.pcode # 'CP011' ;
   INTO Cursor Sijunk Readwrite nofilter

Bye, Olaf.

 

[mjcmkrsr]

Hi,

I set relation to custloca and replace all city,country from custloca to SiJunk

How did you do that? If there is/were a relation between custloca (result of joined cursors on customer.pcode=location.pcode) and sijunk (result of joined cursors on simain.sino = sisub.sino) you could write a single SQL statement to have the desired result cursor (tempsi).

hth
MK

 

[Olaf Doschke]

In the last query I gave you a comma is missing after Sisub.ratemin or before custloca.country. Sorry.

Bye, Olaf.

 

[alisaif]

Thanks a lot! Mr.Olaf it works fine

Saif

 

[alisaif]

Can I sum Qntymaj,qntymin,iif(ratemin#0,qntymin*ratemin,qntymaj*ratemaj) group by country

Thanks

 

[Olaf Doschke]

How is the performance? Because if it's allright, don't waste your time in merging several queries into one more complicated. You don't gain performance and the price to save a few lines is to have a harder to understand and maintain query.

What to do varies from case to case. In this case I'd not merge the queries, as you have to remove many fields of your intermediate results to be able to GROUP BY. So in this case I'd leave it as a separate last step to GROUP BY.

If you don't need the intermediate result anyway, I wonder why you put so much effort in listing all the fields you don't need in your final result. Your final result just has year (the year part of invdate), country and the sums, your intermediate result pulls in many detail informations you can't aggregate into one record per year and country anyway, so doing all in one query you wouldn't put in all the other fields.

Bye, Olaf.

 

[alisaif]

Thanks for the reply

If you don't need the intermediate result anyway, I wonder why you put so much effort in listing all the fields you don't need in your final result. Your final result just has year (the year part of invdate)

Actually I am generating a lots of reports like on salesman, merchandiser, date wise, zone wise, city wise, country wise,customer wise etc etc.
So I copied all these and in summary I just change the group name like (from salesman to country) and so on.

As far as performance is concerned it is taking at least 7-10 seconds to fetch approx. 130,000 records:

Thanks

Saif

 

[alisaif]

Relating to this query, I am facing an error "Field Country does not accept Null Values". If the Country name is not mentioned I want to replace country field with 'Not Mentioned'. Any idea?

Saif

 

[Olaf Doschke]

I was doing a left join, this leaves country=NULL for cases there is no matching record in custloca.

I can't decide what is right, but either you simply make it an INNER join, too, like the others, or you use NVL() to replace NULLS with a text like "Not mentioned".
That means you stay with a LEFT join and replace custloca.country with NVL(custloca.country,PADR("Not mentioned",LEN(custloca.country)) AS COUNTRY. Similarly with City.

Bye, Olaf.

 

[Olaf Doschke]

>As far as performance is concerned it is taking at least 7-10 seconds to fetch approx. 130,000 records
Well, this can have two reasons: Reading lots of data takes it's time, and the query might not be optimized best. An INDEX ON pcode+location does not help to optimize the join.

In regard of your copy and change strategy, I'd rather prepare a template only having the joins set up correctly and then specialise the field list needed for any report. In case you group by country, you only need:

Select tpcode.pcode, tloca.location, tloca.country ;
   from customer tpcode, location tloca ;
   where tpcode.pcode = tloca.pcode ;
   into Cursor custloca Readwrite

and

Select Simain.Invdate, Sisub.qntymaj,;
   Sisub.qntymin, Sisub.ratemaj, Sisub.ratemin, ;
   NVL(custloca.country,PADR("Not mentioned",LEN(custloca.country)) AS COUNTRY;
   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

Even with optimizatiion a very simple rule always applies, fetch only the data you need, and it'll be fetched faster. There is a simple linear dependency between number of bytes loaded and time needed aside of the optimization logic, which can only optimize finding the right records, but not to read them.

And now the queries might be short enough to be able to read, understand and merge them.

Bye, Olaf.

 

[mjcmkrsr]

Hi,

As far as I understand you don't have any field from table "Customer". Hence the whole statement might look as follows (unless I missed something)

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

hth
MK

 

[mjcmkrsr]

...
of course

Select Simain.Invdate, Sisub.qntymaj,;
   Sisub.qntymin, Sisub.ratemaj, Sisub.ratemin, ;
   NVL([u][b][i]location[/i][/b][/u].country,PADR("Not mentioned",LEN([b][i][u]location[/u][/i][/b].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

hth

MK

 

[Olaf Doschke]

Hi MK,

indeed a good observation, but you missone thing. Even just selecting customers joined with locations in the first SQL limits the resulting locations to those, where customers exist. So removing the custumers from the query can give too many records as a result.

Bye, Olaf.