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

 

[audiopro]

The following query runs but the shop totals are all '0'.
Is the syntax correct around the NVL statements.
I have seen examples of NVL useage where the '0' is after the variable.

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

Keith

 

[Olaf Doschke]

Hi Keith,

no, with NVL you should first provide the variable or field that could be NULL, then the value to use if it is NULL. The way you did it it will always return 0.

Also in general you want to have the sum of those values, so you should put that first:

...
SUM(NVL(DDA.Qty),0) As Shop1,;
SUM(NVL(CDA.Qty),0) As Shop2,;
...

And I think you didn't get me, when I was talking about the Code Columns. By padding them each to some equal length and omitting the SPACE(8) as ccode and the SPACE(10) as dcode column respectively you'd combine these two to one Column "Code", no more CCode and DCode, but one Code column.

Bye, Olaf.

 

[audiopro]

Too few arguments but I got the syntax from another example.
I am getting results in the shop columns but neither column is ever assigned to '0' and there should be '0's showing, any clue?

SELECT STK.CODE, STK.PRODUCT, SUM(NVL(DDA.Qty, 0)) As Shop1, SUM(NVL(CDA.Qty, 0)) 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

Keith

 

[audiopro]

Googling on VFP6 SUM NVL shows a few examples but I am somewhat confused about the correct syntax.
The following have all appeared as examples but which is correct?

SUM(NVL(DDA.Qty),0) As Shop1,;
SUM(NVL(CDA.Qty),0) As Shop2,;

NVL(SUM(DDA.QTY), 0) AS SHOP1,;
NVL(SUM(CDA.QTY), 0) AS SHOP2;

Keith

 

[JimWWinter]

Keith,

Your first example is almost correct. But, assuming your sum could be 9 digits, you should probably do it like this,

SUM(NVL(DDA.Qty),000000000) as Shop1, ;
SUM(NVL(CDA.Qty),000000000) as Shop2, ;

because the length of the field will be determined by the first value returned. So, if your first value returned is .NULL. and you convert it 0, you will have a N(1) field for your sum.

Regards,
Jim

 

[audiopro]

I have worked out what the problem is by manipulating the data in the tables.
The query is only pulling out records where both tables contain sales of that particular item. I need the total even if sales of a particular itemhave only taken place in 1 shop, the other shop would be '0' in that record.

Keith

 

[Olaf Doschke]

Hi Keith,

perhaps this?

SELECT STK.CODE, STK.PRODUCT, SUM(NVL(DDA.Qty, 000000000)) As Shop1, SUM(NVL(CDA.Qty, 000000000)) As Shop2;
    FROM ASTOCK STK;
    LEFT JOIN D_DAY DDA ON STK.CODE = DDA.CODE AND DDA.DATE >= DATE() - DAYZ ;
    LEFT JOIN C_DAY CDA ON STK.CODE = CDA.CODE AND CDA.DATE >= DATE() - DAYZ ;
    GROUP BY STK.CODE, STK.PRODUCT;
    ORDER BY STK.CODE ;
    HAVINNG Shop1>0 Or Shop2>0;
    INTO CURSOR MYANALY

Bye, Olaf.

 

[audiopro]

That appears to do the job but I will test it tomorrow when I have more time.
Thanks very much.

Keith

 

[JimWWinter]

Keith,

OK, I did a little testing and their are some problems with the approach I was suggesting. But, this should work for you. Unfortunately, it takes 2 queries.

SELECT Stk.Code, ;
       Stk.Product, ;
       SUM(Dda.Qty) as Shop1, ;
       000000000 as Shop2 ;
  FROM AStock Stk ;
 RIGHT JOIN D_Day Dda ;
    ON Stk.Code = Dda.Code AND ;
       Dda.Date >= DATE() - Dayz ;
 GROUP BY Stk.Code, Stk.Product ;
 UNION ALL ;
SELECT Stk.Code, ;
       Stk.Product, ;
       000000000 as Shop1, ;
       SUM(Cda.Qty) as Shop2 ;
  FROM AStock Stk ;
 RIGHT JOIN C_Day Cda ;
    ON Stk.Code = Cda.Code AND ;
       Cda.Date >= DATE() - Dayz ;  
 group by Stk.Code, Stk.Product ;
  into cursor Temp readwrite

SELECT Code, ;
       Product, ;
       MAX(Shop1) as Shop1, ;
       MAX(Shop2) as Shop2 ;
  FROM Temp ;
 GROUP BY Code, Product ;
 ORDER BY Code ;
  INTO CURSOR MyAnalY

(Gotta try and earn at least one of those stars you've been passing out) ;-)

Regards,
Jim

 

[audiopro]

Thanks Jim
I have encountered those problems and the results were confusing to say the least.
Olaf has hit the nail on the head by including the 'HAVING' clause(although it works much better with only 1 'N' lol).
The other suggestions have increased my knowledge, which is never a bad thing. I am always amazed at just what can be achieved using queries once you get used to the syntax. The language reference offers little in the way of help although I suspect this has been remedied in versions later than my tired old VFP6. It stlill does what I want it to do though and I have a number of apps. out there still delivering the goods day after day.
The alias thing had me stumped at first but I think I understand it now.

Keith