SELECT SQL clause missing just in VFP 9 1

[Cadeira]

Hello !

These commands used to work in VFP 6, but it is not working in VFP 9:
It breaks in the last line (INTO TABLE TEMPRENT) and presents the error: "Command is missing required clause"


(Exactly the same commands copied from program

SELECT ;
BILHETES.TKCCLI AS XCODCLI, ;
BILHETES.TKNPOP AS XNOMCLI, ;
BILHETES.TKCIDA AS XCIDCLI, ;
BILHETES.TKSIGL AS XSIGCIA, ;
SUM(BILHETES.TKTRBR) AS XVRTARI, ;
SUM(BILHETES.TKGCBR) AS XVRGCBR, ;
SUM(BILHETES.TKG1BR) AS XVRG1BR, ;
SUM(BILHETES.TKG2BR) AS XVRG2BR, ;
SUM(BILHETES.TKG3BR) AS XVRG3BR ;
SUM(BILHETES.TKGUBR) AS XVRGUBR ;
FROM CLIENTES, BILHETES ;
WHERE (BILHETES.TKSTAT = "1" OR BILHETES.TKSTAT = "5") ;
AND CLIENTES.CCODI = BILHETES.TKCCLI ;
AND CLIENTES.CNPOP = BILHETES.TKNPOP ;
AND BETWEEN (BILHETES.TKDEMI, YDATAPRI, YDATAULT) ;
ORDER BY BILHETES.TKCCLI ;
GROUP BY BILHETES.TKCCLI, BILHETES.TKSIGL ;
INTO TABLE TEMPRENT


Thank you,
Cadeira

 

[Dan Freeman]

Change your group to GROUP BY 1, 2, 3, 4

-or-

See help for SET ENGINEBEHAVIOR

 

[Cadeira]

Hi Danfreeman:

I used the command SET ENGINEBEHAVIOR 70 in the program, but now other error appeared in the same last line:

"Command contains unrecognized phrase/keyword "

Thank you.

Cadeira

 

[StewartUK]

Hi Cadeira,

Having read your SQL statement it looks like you are missing a comma after the 2nd to last line in your list of fields selected:

....SUM(BILHETES.TKG2BR) AS XVRG2BR, ;
    SUM(BILHETES.TKG3BR) AS XVRG3BR ; [b][COLOR=green]&& no comma![/color][/b]
    SUM(BILHETES.TKGUBR) AS XVRGUBR ;
    FROM CLIENTES, BILHETES ;.....

I would recommend that you don't use SET ENGINEBEHAVIOR 70 because you could end up with using different settings in different places in your application (I speak from experience) and also the changes made in VFP9 are designed to make results more predictable.


Hope that helps,

Stewart
PS If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Cadeira]

Dear Stewart:
I inserted the missing comma you pointed above, and also removed SET ENGINEBEHAVIOR 70.
So these lines are from the program:

SELECT ;
BILHETES.TKCCLI AS XCODCLI, ;
BILHETES.TKNPOP AS XNOMCLI, ;
BILHETES.TKCIDA AS XCIDCLI, ;
BILHETES.TKSIGL AS XSIGCIA, ;
SUM(BILHETES.TKTRBR) AS XVRTARI, ;
SUM(BILHETES.TKGCBR) AS XVRGCBR, ;
SUM(BILHETES.TKG1BR) AS XVRG1BR, ;
SUM(BILHETES.TKG2BR) AS XVRG2BR, ;
SUM(BILHETES.TKG3BR) AS XVRG3BR, ;
SUM(BILHETES.TKGUBR) AS XVRGUBR, ;
FROM CLIENTES, BILHETES ;
WHERE (BILHETES.TKSTAT = "1" OR BILHETES.TKSTAT = "5") ;
AND CLIENTES.CCODI = BILHETES.TKCCLI ;
AND CLIENTES.CNPOP = BILHETES.TKNPOP ;
AND BETWEEN (BILHETES.TKDEMI, YDATAPRI, YDATAULT) ;
GROUP BY BILHETES.TKCCLI, BILHETES.TKSIGL ;
ORDER BY BILHETES.TKCCLI, ;
INTO TABLE TEMPRENT

I executed the program and it was halted and "INTO TABLE TEMPRENT" above was highlighted and the following error was showed:
"Command contains unrecognized phrase/keyword"

Thank you.
Cadeira

 

[SnyAc]

Remove the comma before 'INTO TABLE TEMPRENT'

Andy Snyder
SnyAc Software Services

http://www.snysoft.com

Hyperware Inc.
AmTech Software Inc.

 

[Cadeira]

Dear Andy:

I removed the comma you pointed, so these are the lines copied from the program:

SELECT ;
BILHETES.TKCCLI AS XCODCLI, ;
BILHETES.TKNPOP AS XNOMCLI, ;
BILHETES.TKCIDA AS XCIDCLI, ;
BILHETES.TKSIGL AS XSIGCIA, ;
SUM(BILHETES.TKTRBR) AS XVRTARI, ;
SUM(BILHETES.TKGCBR) AS XVRGCBR, ;
SUM(BILHETES.TKG1BR) AS XVRG1BR, ;
SUM(BILHETES.TKG2BR) AS XVRG2BR, ;
SUM(BILHETES.TKG3BR) AS XVRG3BR, ;
SUM(BILHETES.TKGUBR) AS XVRGUBR, ;
FROM CLIENTES, BILHETES ;
WHERE (BILHETES.TKSTAT = "1" OR BILHETES.TKSTAT = "5") ;
AND CLIENTES.CCODI = BILHETES.TKCCLI ;
AND CLIENTES.CNPOP = BILHETES.TKNPOP ;
AND BETWEEN (BILHETES.TKDEMI, YDATAPRI, YDATAULT) ;
GROUP BY BILHETES.TKCCLI, BILHETES.TKSIGL ;
ORDER BY BILHETES.TKCCLI ;
INTO TABLE TEMPRENT


I executed it and the program broke and highlighed the line "INTO TABLE TEMPRENT" with a different error at this time:
"Command is missing required clause "

Thank you,
Cadeira

 

[Cadeira]

Please note that in the lines:

WHERE (BILHETES.TKSTAT = "1" OR BILHETES.TKSTAT = "5") ;
AND CLIENTES.CCODI = BILHETES.TKCCLI ;
AND CLIENTES.CNPOP = BILHETES.TKNPOP ;
AND BETWEEN (BILHETES.TKDEMI, YDATAPRI, YDATAULT) ;

the terms 'AND' above do not is exhibited in BLUE color !(as it does in the 'WHERE', 'BETWEEN' and every SQL terms)

Cadeira

 

[SnyAc]

remove the comma before FROM CLIENTES,

Andy Snyder
SnyAc Software Services

http://www.snysoft.com

Hyperware Inc.
AmTech Software Inc.

 

[Cadeira]

Good morning !

I did it Andy, so the commands in the programs are exactly:

SELECT ;
BILHETES.TKCCLI AS XCODCLI, ;
BILHETES.TKNPOP AS XNOMCLI, ;
BILHETES.TKCIDA AS XCIDCLI, ;
BILHETES.TKSIGL AS XSIGCIA, ;
SUM(BILHETES.TKTRBR) AS XVRTARI, ;
SUM(BILHETES.TKGCBR) AS XVRGCBR, ;
SUM(BILHETES.TKG1BR) AS XVRG1BR, ;
SUM(BILHETES.TKG2BR) AS XVRG2BR, ;
SUM(BILHETES.TKG3BR) AS XVRG3BR, ;
SUM(BILHETES.TKGUBR) AS XVRGUBR, ;
FROM CLIENTES BILHETES ;
WHERE (BILHETES.TKSTAT = "1" OR BILHETES.TKSTAT = "5") ;
AND CLIENTES.CCODI = BILHETES.TKCCLI ;
AND CLIENTES.CNPOP = BILHETES.TKNPOP ;
AND BETWEEN (BILHETES.TKDEMI, YDATAPRI, YDATAULT) ;
GROUP BY BILHETES.TKCCLI, BILHETES.TKSIGL ;
ORDER BY BILHETES.TKCCLI ;
INTO TABLE TEMPRENT

This time program also stopped and "FROM CLIENTES BILHETES" was highlighted and the following error showed up:

"Command contains unrecognized phrase/keyword"

Please note that 'CLIENTES' is a DBF file, and 'BILHETES' is also a DBF file.

Thank you.
Cadeira

 

[Cadeira]

Andy:

I think I removed the comma from wrong place. Now I did what you advised me. These are the commands now:

SELECT ;
BILHETES.TKCCLI AS XCODCLI, ;
BILHETES.TKNPOP AS XNOMCLI, ;
BILHETES.TKCIDA AS XCIDCLI, ;
BILHETES.TKSIGL AS XSIGCIA, ;
SUM(BILHETES.TKTRBR) AS XVRTARI, ;
SUM(BILHETES.TKGCBR) AS XVRGCBR, ;
SUM(BILHETES.TKG1BR) AS XVRG1BR, ;
SUM(BILHETES.TKG2BR) AS XVRG2BR, ;
SUM(BILHETES.TKG3BR) AS XVRG3BR, ;
SUM(BILHETES.TKGUBR) AS XVRGUBR ;
FROM CLIENTES, BILHETES ;
WHERE (BILHETES.TKSTAT = "1" OR BILHETES.TKSTAT = "5") ;
AND CLIENTES.CCODI = BILHETES.TKCCLI ;
AND CLIENTES.CNPOP = BILHETES.TKNPOP ;
AND BETWEEN (BILHETES.TKDEMI, YDATAPRI, YDATAULT) ;
GROUP BY BILHETES.TKCCLI, BILHETES.TKSIGL ;
ORDER BY BILHETES.TKCCLI ;
INTO TABLE TEMPRENT

The error now is:
"SQL: GROUP BY clause is invalid"

Thank you,
Cadeira

 

[SnyAc]

you didn't remove the comma....

SELECT ;
     BILHETES.TKCCLI AS XCODCLI, ;
     BILHETES.TKNPOP AS XNOMCLI, ;
     BILHETES.TKCIDA AS XCIDCLI, ;
     BILHETES.TKSIGL AS XSIGCIA, ;
     SUM(BILHETES.TKTRBR) AS XVRTARI, ;
     SUM(BILHETES.TKGCBR) AS XVRGCBR, ;
     SUM(BILHETES.TKG1BR) AS XVRG1BR, ;
     SUM(BILHETES.TKG2BR) AS XVRG2BR, ;
     SUM(BILHETES.TKG3BR) AS XVRG3BR, ;
     SUM(BILHETES.TKGUBR) AS XVRGUBR ; && comma removed here
     FROM CLIENTES BILHETES ;
     WHERE (BILHETES.TKSTAT = "1" OR BILHETES.TKSTAT = "5") ;
     AND CLIENTES.CCODI = BILHETES.TKCCLI ;
     AND CLIENTES.CNPOP = BILHETES.TKNPOP ;
     AND BETWEEN (BILHETES.TKDEMI, YDATAPRI, YDATAULT) ;
     GROUP BY BILHETES.TKCCLI, BILHETES.TKSIGL ;
     ORDER BY BILHETES.TKCCLI ;
     INTO TABLE TEMPRENT

Andy Snyder
SnyAc Software Services

http://www.snysoft.com

Hyperware Inc.
AmTech Software Inc.

 

[SnyAc]

yep... i was only looking at the syntax of the command....

Andy Snyder
SnyAc Software Services

http://www.snysoft.com

Hyperware Inc.
AmTech Software Inc.

 

[Cadeira]

Yes !
It worked properly now with Mike response ("That command requires SET ENGINEBEHAVIOR 70 ...")

Danfreeman had also told me to use it but unfortunatelly I'd changed other commands and got in errors because of other command errors. Lesson learned: Always keep original code when experiencing.

Stewart's post is valid ("...the changes made in VFP9 are designed to make results more predictable") but as I have not understood entirely the changes from VFP 6 to 9, I stayed in the old style SQL.

Andy, with your instructions I could understand a little more SQL commands.

Thank you all.
Cadeira