help with syntax for SELECT

[Judi201]

Hi!

Using an example from here and a wonderful e-book I just downloaded, Taming VFP SQL, I have written this SELECT:

		SELECT scpjobs,scpdate, orders.jobsnumb, orders.ordrnumb, ;
			orders.partnumb,orders.custno, ;
			SUM(IIF(VAL(SCPTYPE)=1,SCPAMNT,0000)) AS type1   , ;
			SUM(IIF(VAL(SCPTYPE)=2,SCPAMNT,0000)) AS type2   , ;
			SUM(IIF(VAL(SCPTYPE)=3,SCPAMNT,0000)) AS type3   , ;
			SUM(IIF(VAL(SCPTYPE)=4,SCPAMNT,0000)) AS type4   , ;
			SUM(IIF(VAL(SCPTYPE)=5,SCPAMNT,0000)) AS type5   , ;
			SUM(IIF(VAL(SCPTYPE)=6,SCPAMNT,0000)) AS type6   , ;
			SUM(IIF(VAL(SCPTYPE)=7,SCPAMNT,0000)) AS type7   , ;
			SUM(IIF(VAL(SCPTYPE)=8,SCPAMNT,0000)) AS type8   , ;
			SUM(IIF(VAL(SCPTYPE)=9,SCPAMNT,0000)) AS type9   , ;
			SUM(IIF(VAL(SCPTYPE)=10,SCPAMNT,0000)) AS type10 , ;
			SUM(IIF(VAL(SCPTYPE)=11,SCPAMNT,0000)) AS type11 , ;
			SUM(IIF(VAL(SCPTYPE)=12,SCPAMNT,0000)) AS type12 , ;
			SUM(IIF(VAL(SCPTYPE)=13,SCPAMNT,0000)) AS type13 , ;
			SUM(IIF(VAL(SCPTYPE)=14,SCPAMNT,0000)) AS type14 , ;
			SUM(IIF(VAL(SCPTYPE)=15,SCPAMNT,0000)) AS type15 , ;
			SUM(IIF(VAL(SCPTYPE)=16,SCPAMNT,0000)) AS type16 , ;
			SUM(IIF(VAL(SCPTYPE)=17,SCPAMNT,0000)) AS type17 , ;
			SUM(IIF(VAL(SCPTYPE)=18,SCPAMNT,0000)) AS type18 , ;
			SUM(IIF(VAL(SCPTYPE)=19,SCPAMNT,0000)) AS type19 , ;
			SUM(IIF(VAL(SCPTYPE)=20,SCPAMNT,0000)) AS type20 , ;
			SUM(IIF(VAL(SCPTYPE)=21,SCPAMNT,0000)) AS type21 , ;
			SUM(IIF(VAL(SCPTYPE)=22,SCPAMNT,0000)) AS type22 , ;
			SUM(IIF(VAL(SCPTYPE)=23,SCPAMNT,0000)) AS type23 , ;
			SUM(IIF(VAL(SCPTYPE)=24,SCPAMNT,0000)) AS type24 , ;
			SUM(IIF(VAL(SCPTYPE)=25,SCPAMNT,0000)) AS type25 , ;
			SUM(IIF(VAL(SCPTYPE)=26,SCPAMNT,0000)) AS type26 , ;
			SUM(IIF(VAL(SCPTYPE)=27,SCPAMNT,0000)) AS type27 , ;
			SUM(IIF(VAL(SCPTYPE)=28,SCPAMNT,0000)) AS type28 , ;
			SUM(IIF(VAL(SCPTYPE)=29,SCPAMNT,0000)) AS type29 , ;
			SUM(IIF(VAL(SCPTYPE)=30,SCPAMNT,0000)) AS type30   ;
			FROM scrap ;
		    JOIN orders ;
		 ON orders.ordrnumb=scrap.scpords AND orders.jobsnumb = scrap.scpjobs ;
         *!*		WHERE scpdate BETWEEN **ldBegDate** **ldEndDate** ;
			GROUP BY 1 ;
			ORDER BY 1 ;
			INTO CURSOR csrProdScrap

It works great until I try to add the WHERE. Then no matter how I have tried to write it I get an error. Either missing ')' or syntax error, depending on the way I am trying it.
Would someone please show me how to write that line?
scpdate is a field in one table
date fields are caught from the user selection on form.

Many thanks for any help.

Judi

 

[JimWWinter]

Hi Judi,

How about
WHERE BETWEEN(Scrap.scpDate,ldBegDate,ldEndDate) ;

I assume you've verified that scpdate, ldBegDate, and ldEndDate are all date types?

Incidentally, that is an awesome book!

Regards,
Jim

 

[alvechurchdata]

Jim's example used the Fox BETWEEN function, I've used the SQL equivalent.

[TT]WHERE scpdate BETWEEN ldBegDate AND ldEndDate[/TT]

You could also try:

[TT]WHERE scpdate>ldBegDate AND scpdate<ldEndDate[/TT]

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Judi201]

Thanks Geoff and Jim,

I had tried Jim's method which I have used before.
I was not using the AND in the SQL statement (not used in my example in the book)

I had always used the >= but wanted to get comfortable with using between.
In any case using any one of these gives me "SQL column not found"

Now that I know I am writing the line correctly I look for other problems.
The scpdate is a DATE field from the table. The ldBegDate is from the statement

ldBegDate = THISFORM.txtBegDate.VALUE

txtBegDate is initalized with = DATE()
same for txtEndDate

Would this meet requirement for comparison?

Thanks for all help

Judi

 

[TamarGranor]

Judi - can you tell me which example is missing the AND, so I can add it to my list of errata to post on the Hentzenwerke site?

Using the SQL BETWEEN rather than the BETWEEN() was one of the things my tech editor really pushed (correctly), and I'm not surprised that somewhere I didn't quite get the change right.

Tamar

 

[Judi201]

Tamar,
Glad to, your book has really helped me so much. The example I was using was Listing 49 on page 61 in Chapter 4.

I must admit that I did not look at the code samples. I am a book person, I'm afraid.

Thanks so much for all of your help.

Judi

 

[Judi201]

Hi All,
I found the miss spell that caused the error. Thanks for clearing up the use of BETWEEN.

Tamar: If I miss read the example please let me know.

Judi

 

[TamarGranor]

Nope, it was my mistake. Thanks.

Tamar

 

[mikeopolo]

Not an answer to your question, but an alternative code you might like to consider which avoids long select statements (**untested**):


SELECT scpjobs,scpdate, scpamnt, orders.jobsnumb, orders.ordrnumb, ;
orders.partnumb,orders.custno ;
FROM scrap ;
JOIN orders ;
ON orders.ordrnumb=scrap.scpords AND orders.jobsnumb = scrap.scpjobs ;
*!* WHERE scpdate BETWEEN **ldBegDate** **ldEndDate** ;
INTO CURSOR csrProdScrap

* the Where clause above to be amended as others have said

For i = 1 to 30
Store "Type"+alltr(i) to lcNewField
Alter table csrProdScrap Add Column &lcNewField N(10,0)
Replace For csrProdScrap.scptype=&i ;
&lcNewField With &lcNewField + scpamnt IN csrProdScrap
EndFor

Select * From csrProdScrap ;
Order By scpjobs ;
Into Cursor scpProdScrp2


&&Regards
&&Mike

 

[Judi201]

Mike,

Thanks for the input. I have been looking for a solution of this nature, thinking that it would be easier to debug. ( a mistake in the code I am using just causes the marker in debugger to hit SELECT and 'sit there' ) Not much help when it comes to finding a typo or such.

I failed to say in my posing that I am using VFP 6.0 and I believe that the error I see when trying your code is caused by 6.0 not having the READWRITE feature. Someone please let me know if I am wrong about that.

I found a faq on making a writeable cursor (faq184-2800 by Mike Gagnon) and am going to see if I can work that in. For some reason I have a lot of problems trying to use & substitution and arrays in 6.0 and that was my bread & butter in FP 2.6. Just my mindset I am sure but I would like very much to make this approach work.

I might add that I am new back in developement after many years. Left when 3.0 was just coming out.

Thanks again.

Judi

 

[TamarGranor]

Yes, READWRITE was added in VFP 7.

Judi, I think you'll be able to make progress much faster if you upgrade to a more recent version. (Might as well go to 9 while you're at it.) The addition of IntelliSense in VFP 7 makes it so much easier to figure out what you're doing as you write code.

Tamar

 

[Judi201]

Tamar,
I know that you are right. As I have mentioned before, I have just gotten back into programming after years away. I am kind of testing the waters in retirement to see how much opportunity there is in my area and how much I really want to do this. So far both have been great. I am looking to upgrading both development machine and software (VFP 9, of course!) with income from this project. Just kind of feeling my way.

The answers might not have been the same without this forum and the books I have been able to find, including all of yours. Quite different from when I started and there was nothing like this!

You pros are wonderful to be so helpful and I believe that is what has kept 'Fox Rocking' !

Judi