How to combine table/cursor into one using SQL-Select

[alisaif]

Hi

Input
-----
myur1 = '2012'
myur2 = '2013'

dt1 = {01/01/&myur1}
dt2 = {31/12/&myur2}
mGrp = "CARREFOUR"

OutPut
------
Month-wise breakup comparison as follows:

Month 2013 2012 Change %age
-------------------------------------
Jan
Feb
Mar
..
..
Dec

Now I have four tables as follows:

1. Group
2. Simain
3. Sisub
4. Pcode

1. ( Group File contain multiple party code relating to the same group )
pcode c(10)
cGroup c(52)

2. ( Sales Invoice Master File contains date in which I want to filter records )
sino c(8)
pcode c(10)
invdate d && Filter = From Jan 1, 2012 to Dec 31, 2013
location c(52)

3. ( Sales Invoice Transaction File contain Quantity and Rate )
sino c(8)
icode c(10)
QntyMaj n(10,3)
QntyMin n(10,3)
RateMaj n(10,3)
Ratemin n(10,3)

4. Customer Master File contains all party information
pcode c(10)
full_name c(52)

I am using the following at the moment to get the result

Select talias1.sino, talias1.invdate, talias1.pcode, talias1.location ,talias1.zone, talias1.salesman, talias1.merchand, ;
   talias2.icode, talias2.qntymaj, talias2.qntymin, talias2.ratemaj, talias2.ratemin ;
   from simain talias1, sisub talias2 ;
   where talias1.sino = talias2.sino And Between(invdate,dt1,dt2) AND pcode # 'CP011' ;
   into Cursor sijunk Readwrite

and then

Sele Year(invdate) As theyear, ;
   MONTH(invdate) as themonth,;
   pcode As pcode, ;
   sum(qntymaj) As cartons, ;
   sum(qntymin) As units, ;
   sum(Iif(ratemin#0,qntymin*ratemin,qntymaj*ratemaj)) As mamount, ;
   CAST('' As c(52)) As cGroup ;
   from sijunk ;
   group By theyear, themonth, pcode ;
   into Curs tempsi

Then I run do whil ... enddo to get the group name with Group.dbf which is a time consuming job and I want to only CARREFOUR Group Data.

Can I combine all to get the result in one syntax?

Thanks

Saif

 

[TamarGranor]

First, lose the macros in putting the date together. Go with the DATE() function instead:

myur1 = '2012'
myur2 = '2013'

dt1 = DATE(val(myur1), 1, 1}
dt2 = DATE(val(myur2), 12, 31)

Of course, even better if you just use numbers for year instead of character.

On to your real problem, why aren't you just selecting only the records you want with a condition in the WHERE clause?

Tamar

 

[alisaif]

Thanks for the tip!

I want the following fields as an output:

1. sino (Simain.dbf)
2. invdate (simain.dbf)
3. pcode (simain.dbf)
4. location (simain.dbf)
5. icode (Sisub.dbf)
6. full_desc (icode.dbf) &&I forgot to mention
7. QntyMaj (Sisub.dbf)
8. QntyMin (Sisub.dbf)
9. RateMaj (Sisub.dbf)
10. RateMin (Sisub.dbf)
11. Full_name (Pcode.dbf)
12. cGroup (Group.dbf)

How can I get it? How Can I use select/subselect?

Thanks

Saif

 

[alisaif]

I tried something like this but giving some errors:

SELECT Customer.pcode, Customer.Full_Name, ; 
 (SELECT sum(Iif(ratemin#0,qntymin*ratemin,qntymaj*ratemaj)) ; 
 FROM sisub ; 
 JOIN icode ; 
 ON Sisub.icode = ; 
 icode.icode ; 
 WHERE Between(Simain.invdate,dt1,dt2) ; 
 AND Customer.pcode = ; 
 Simain.pcode ) AS yTotal ; 
 FROM Customer ; 
INTO CURSOR CustomerTotal

Original Code which I took from your post on website is as follows:

*!*	SELECT Customer.Customer_ID, Customer.Company_Name, ; 
*!*	 Customer.Address, Customer.City, ; 
*!*	 Customer.Region, Customer.Postal_Code, ; 
*!*	 Customer.Phone, Customer.Fax, ; 
*!*	 (SELECT SUM(quantity*unit_price) ; 
*!*	 FROM Orders ; 
*!*	 JOIN Order_Line_Items; 
*!*	 ON Orders.Order_ID = ; 
*!*	 Order_Line_Items.Order_ID ; 
*!*	 WHERE BETWEEN(Order_Date, DATE(m.nYear,1,1), ; 
*!*	 DATE(m.nYear,12,31)) ; 
*!*	 AND Customer.Customer_ID = ; 
*!*	 Orders.Customer_ID ) AS yTotal ; 
*!*	 FROM Customer ; 
*!*	INTO CURSOR CustomerTotal

Kindly correct my code and include group (cGroup) also.

Thanks

Saif

 

[Mike Lewis]

Saif,

A couple of points:

1. In your sub-query, you have [tt]WHERE Between(Simain.invdate,dt1,dt2)[/tt]. But Simain is not one of the tables referenced within the sub-query (it is not specified in a FROM or JOIN clause). That itself won't give rise to an error, but it will probably not give the result you want. How would the query know which record of Simain to use?

2. The same applies to [tt]AND Customer.pcode = Simain.pcode[/tt]. Again, the Customer table is not specified in the sub-query. The fact that it is referenced from the main (outer) query does not change that. There is no correlation between the sub-query and the required Customer record.

You say your code is giving "some errors" Can you tell us what those errors are.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[alisaif]

Thanks

The error is:

SQL: Error Correlating fields.

Saif

 

[Mike Lewis]

Saif, judging from the wording of the error message, I think the problem is what I suggested, that is, the use of the Simain and Customer tables in the sub-query. In order to fix that, we need to know what the relationship is between the Customer table and Sisub/Icode, and between Customer and Simain.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[alisaif]

Hi,

Let me try to explain the relation:

sele 5
use Group orde grcode alia cGroup && Contains fields like pcode, grcode 
sele 4
use icode orde icode alia item && Contains fields like icode,full_desc, .. .... ..
sele 3
use pcode orde pcode alia party && Contains fields like pcode,full_name
set rela to pcode into cGroup
sele 2
use sisub orde sino alia sisub  && Contains fields like Sino, icode, ... ..
set rela to icode into item 
sele 1
use simain orde sino alia simain && Contains fields like sino,pcode, .. ..
set rela to sino into sisub
set rela to pcode into party addi

And I want the following fields as an output using SQL-Select so that I can filter through date, group, pcode etc...

1. sino (Simain.dbf)
2. invdate (simain.dbf)
3. pcode (simain.dbf)
4. location (simain.dbf)
5. icode (Sisub.dbf)
6. full_desc (icode.dbf) &&I forgot to mention
7. QntyMaj (Sisub.dbf)
8. QntyMin (Sisub.dbf)
9. RateMaj (Sisub.dbf)
10. RateMin (Sisub.dbf)
11. Full_name (Pcode.dbf)
12. cGroup (Group.dbf)

I hope I tried to explain as required!

Saif

 

[Mike Lewis]

Nearly.

You've mentioned Group, but (as far as I can see) that doesn't appear in your query.

And you have Customer in your query, but that doesn't appear in your list.

Also, the best way to present this information is in a simple table, like this:

[b]Table        Links to       Linking field         Target field[/b]

Customer     Simain         Pcode                 Pcode

etc. etc

For this purpose, we're not interested in the indexes, SET RELATION, aliases, USE statements, etc.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[TamarGranor]

Even better than just showing us the table structures, if you could write a little code, using CREATE CURSOR (or TABLE) and INSERT INTO, to give us samples of the whole thing, then we can do a much better job.

Tamar

 

[alisaif]

Hi,

Thanks for the reply, kindly look at the beginning of this thread for the code which I am currently using for the said purpose.

Saif

 

[Olaf Doschke]

It's hard to help you, as you don't give the code for the most time consuming step regarding to your first post, after you do the two given queries:

Then I run do whil ... enddo to get the group name with Group.dbf which is a time consuming job and I want to only CARREFOUR Group Data.

From the descritopion of the groupd. table you have a pcode in it, so it looks as if you can limit the data of the first query to the pcode related to a certain group already, so why don't you do that?

Select talias1.sino, talias1.invdate, talias1.pcode, talias1.location ,talias1.zone, talias1.salesman, talias1.merchand, ;
   talias2.icode, talias2.qntymaj, talias2.qntymin, talias2.ratemaj, talias2.ratemin ;
   from simain talias1, sisub talias2, group talias3;
   where talias1.sino = talias2.sino And Between(invdate,dt1,dt2) AND talias1.pcode # 'CP011' AND talias3.pcode = talias1.pcode and talias3.cgroup = 'CARREFOUR';
   into Cursor sijunk Readwrite

Remember a rule of thumb: You always join all tables containing data you want to select AND/OR data you can filter by, so even if the group only is used for filtering and you don't want any value from that table, you bring it into the query as soon as possible, as filtering data always accelerates queries.

As a side note I'd rather not misuse the where clause for join conditions. Use joins in a query to denot you're joining the tables on certain field matches:

Select talias1.sino, talias1.invdate, talias1.pcode, talias1.location ,talias1.zone, talias1.salesman, talias1.merchand, ;
   talias2.icode, talias2.qntymaj, talias2.qntymin, talias2.ratemaj, talias2.ratemin ;
   from simain talias1;
   inner join sisub talias2 On talias1.sino = talias2.sino;
   inner join group talias3 On talias3.pcode = talias1.pcode;
   where  Between(invdate,dt1,dt2) AND talias1.pcode # 'CP011' AND  and talias3.cgroup = 'CARREFOUR';
   into Cursor sijunk Readwrite

Bye, Olaf.

 

[alisaif]

Thanks Olaf I will make a try on it.

Saif