Appending to an existing cursor 2

[audiopro]

Is it possible to select SQL into a cursor and then append the results of a susequent select SQL into the same cursor?

Keith

 

[JimWWinter]

Keith,

Sure, as long as both cursors have the same structure and are created with the READWRITE clause and a name.

Regards,
Jim

 

[Olaf Doschke]

or you make on single SQL by using the UNION or UNION ALL clause. Both selects must have the exact same result structure.

UNION ALL does append results from SQL2 to SQL1, UNION without ALL does also remove duplicate result rows.

Bye, Olaf.

 

[Mike Lewis]

Keith,

Just to add a couple of notes to the good answers you've already received.

With a UNION, the cursors don't need to have the same structure. They just need to match on number of columns and data types. The field names can be different. In VFP 9.0, the requirement to match on data types has been relaxed slightly.

If you use an APPEND FROM (which is what I think you had in mind originally), the structures don't have to match exactly. Any unmatched field will be ignored.

Finally, if you want to APPEND FROM a cursor, as opposed to a physical table, you will have to use the DBF() function to get the required filename. For example, if the cursor's alias is Cursor1, do this:

APPEND FROM DBF("Cursor1")

Hope this helps.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[audiopro]

I don't understand how a union can give me the results I seek, maybe I am being a bit numb.
I have written two queries to obtain the results I require and individually they work.
The code for 1 query is below, the second is identical structure but with C_DAY instead of D_DAY as the table name.

	SELECT D_DAY.Code,SUM(D_DAY.Qty) AS SHOP1,ASTOCK.Product;
		FROM Z:\ASTOCK.DBF ASTOCK INNER JOIN Z:\D_DAY.DBF D_DAY ON ASTOCK.CODE = D_DAY.CODE;
		WHERE D_DAY.Qty>0;
		AND D_DAY.DATE>=DATE()-DAYZ;
		ORDER BY D_DAY.Code;
		GROUP BY D_DAY.CODE;
		INTO CURSOR MYANALY

If I combine the two results I am going to end up with a record for each product, if there is a total from both tables with the same code number, there will be two records for the same code. I need to display the two seperate totals on the same line of the report.
I assume that I will also have to create and additional column in query 1 which will be populated in query 2.

Keith

 

[JimWWinter]

Keith,

OK, that's a little different. Your append command would do the same thing as the UNION ALL clause - give you two records per product. Try this instead.

SELECT Stk.Code, ;
       Stk.Product, ;
       NVL(0,SUM(Dda.Qty)) AS Shop1, ;
       NVL(0,SUM(Cda.Qty)) AS Shop2 ;
  FROM AStock Stk 
  JOIN D_Day Dda ON Stk.Code = Dda.Code AND ;
       Dda.Date >= DATE() - Dayz ;
  JOIN C_Day Cda ON Stk.Code - Cda.Code AND ;
       Cda.Date >= DATE() - Dayz ;
 GROUP BY Stk.Code, Stk.Product '
 ORDER BY Stk.Code ;
  INTO CURSOR MyAnalY

Not tested but it should get you started.

Regards,
Jim

 

[audiopro]

Thanks Jim
I'll check that out when I get my desk cleared
Not sure I understand what the following refers to.
Is that just the astock table?

  FROM AStock Stk

Keith

 

[audiopro]

I need to get my headround what is going on there.
Can the 'where' part be omitted?

Keith

 

[JimWWinter]

Keith,

  FROM AStock Stk

refers to the AStock table and assigns a 3 character alias to it.

The WHERE is added to the JOIN condition so it is no longer needed as a separate clause.

You may find you want to do a RIGHT JOIN instead just the JOIN if you have a lot of products that arn't sold by either shop in the period you're reporting on.

Regards,
Jim

 

[audiopro]

refers to the AStock table and assigns a 3 character alias to it

Thought it was an alias but couldn't understand how it could be assigned after an earlier reference.
That's a good point about the non sold items, there are a lot of specialist lines in the stock list which are not sold very often.


Keith

 

[audiopro]

I still don't fully understand the way the alias part of that works but I will do some reading.
Running the query tells me that the where clause is invalid but that is possibly down to the table names being wrong.


Keith

 

[Mike Lewis]

Keith,

I hope this doesn't confuse the issue even more, but I think you'll find the UNION does work. I haven't tested this, but it should give you the result you want:

 SELECT D_DAY.Code,SUM(D_DAY.Qty) AS SHOP1,ASTOCK.Product;
   FROM Z:\ASTOCK.DBF ASTOCK INNER JOIN Z:\D_DAY.DBF D_DAY   
   ON ASTOCK.CODE = D_DAY.CODE;
   WHERE D_DAY.Qty>0;
   AND D_DAY.DATE>=DATE()-DAYZ;
   GROUP BY D_DAY.CODE;
 UNION ;
SELECT C_DAY.Code,SUM(C_DAY.Qty) AS SHOP1,ASTOCK.Product;
   FROM Z:\ASTOCK.DBF ASTOCK INNER JOIN Z:\C_DAY.DBF C_DAY   
   ON ASTOCK.CODE = C_DAY.CODE;
   WHERE C_DAY.Qty>0;
   AND C_DAY.DATE>=DATE()-DAYZ;
   GROUP BY C_DAY.CODE;
ORDER BY 1;
INTO CURSOR MYANALY

Give it a try.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[audiopro]

Thanks Mike
I will give that a try and report back.

Keith

 

[audiopro]

I have done some testing and the code above throws a 'selects are not union compatible' error.
I have never touched UNION before so I have been doing a few tests to get my head round it.
I am more confused now than when I started.
Tables C_DAY and D_DAY contain a number of fields but the ones I am interested in are CODE and METH.
This Query works fine.

	SELECT METH;
	FROM  D_DAY;
	UNION ;
	SELECT METH;
	FROM C_DAY;
	ORDER BY 1

This query gives me the 'selects are not union compatible' error.

	SELECT CODE;
	FROM  D_DAY;
	UNION ;
	SELECT CODE;
	FROM C_DAY;
	ORDER BY 1

Conclusion..........
I think I'll take up knitting as the worst that can happen is the wool tangles up!
Before I start to make more socks than I can cope with I would like to solve this mystery.
It seems to be the word 'code' that is tripping it up could this be possible or is there some other explanation?

Keith

 

[audiopro]

Solved the current problem, D_DAY.code is 10 chars and C_DAY.code is only 8 chars. The 2 files are created remotely and uploaded so may be opening a can of worms changing them.
The following revised query creates the results I require but not in an ideal format.

SELECT D_DAY.Code AS DCODE, space(8) as CCODE, SUM(D_DAY.Qty) AS SHOP1, 0 as SHOP2, ASTOCK.Product;
	FROM Z:\ASTOCK.DBF ASTOCK INNER JOIN Z:\D_DAY.DBF D_DAY;
	ON ASTOCK.CODE = D_DAY.CODE;
	WHERE D_DAY.Qty>0 AND D_DAY.DATE>=DATE()-DAYZ;
	GROUP BY D_DAY.CODE;
	UNION ;
	SELECT SPACE(10)AS DCODE, C_DAY.Code AS CCODE, 0 AS SHOP1, SUM(C_DAY.Qty) AS SHOP2,ASTOCK.Product;
	FROM Z:\ASTOCK.DBF ASTOCK INNER JOIN Z:\C_DAY.DBF C_DAY;
	ON ASTOCK.CODE = C_DAY.CODE;
	WHERE C_DAY.Qty>0 AND C_DAY.DATE>=DATE()-DAYZ;
	GROUP BY C_DAY.CODE;
	ORDER BY 1

I now have the results in the following format

DCODE   CCODE   SHOP1   SHOP2   PRODUCT
A123              5             SMALL ROUND THINGY
A124              2             MEDIUM ROUND THINGY
A125              4             LARGE ROUND THINGY
        A123              1     SMALL ROUND THINGY
        A124              9     MEDIUM ROUND THINGY
        A125              2     LARGE ROUND THINGY

The next task is to create

CODE   SHOP1   SHOP2   PRODUCT
A123     5       1     SMALL ROUND THINGY
A124     2       9     MEDIUM ROUND THINGY
A125     4       2     LARGE ROUND THINGY

How hard can it be?

Keith

 

[Mike Lewis]

Keith,

As you've found, when you do a UNION, the fields have to match exactly in data type and size. That's why you were getting an error.

Anyway, glad to see you're on your way to the solution.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[TamarGranor]

For the results you want, I think Jim Winter gave you the right query back on 1-Sept.

Tamar

 

[Olaf Doschke]

Hi audiopro,

If two fields are not union compatible you can always use some padding to have one fieldwidth in each single select. You could eg do:

Select Padr(C_DAY.Code,20) As Code
...
Union
...
Padr(D_Day.Code,20) As Code

That would make these two differing fields union compatible and even if the field width will grow, you can take up to 20 char long codes.

And do you really want a column for each shop? That will be a bad idea, if you will some day have more than 254 shops and sum up data. Well, maybe by then that will have no impact anymore, just a thought.

Bye, Olaf.

 

[audiopro]

Thanks Olaf

I think space 10 did the same thing unless there is a problem doing it that way.

Having a single column for the total sales in both shops would make my life easier but this will be a re-ordering system within the existing EPOS program. It is not a live stock system but we think it is the next best thing.
There is a main branch and a satelite branch with no plans to expand but the whole program is designed to work with a few more branches, should the need arise. All ordering is done from the main branch and this addition to the program is intended for analysis of sales trends and as a means of 'topping up' the satelite shop.

I am still working out how to combine the item totals for each shop onto the same line within a report.

Keith

 

[audiopro]

TamarGranor
Thanks for pointing that out

JimWWinter
Please accept my apologies for not responding to your post.
There are a couple of typos but once sorted the query looks to be along the right lines but does not create totals in the shop columns, I have some time at last to get my head round rhe problem.


Keith