 | |
I need help with joining derived tables in VFP 9
comp.databases.xbase.fox comp.databases.xbase.fox
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
I need help with joining derived tables in VFP 9 -
06-26-2008
, 02:56 AM
#2
| |||
| |||
|
|
I have three derived tables generated by select some columns from the same tabel . I have written SQL statements to summarize the information of each table based on certain columns in the main table.I wrote *an SQL statement where in it sql should perform the summarization of each of the three derived tables separately and then the summarized results be joined together using where clause. My query is as follows: SELECT tb1.gs7usr as User, tb1.ADD, tb2.UPD, tb3.DEL from (SELECT B.gs7usr,COUNT(B.GS7nin)as ADD FROM RGS8 B where B.GS7act='ADD'and B.gs7dte=20080310 and B.GS7tme=(select Max(E.GS7tme) AS tme from RGS8 E where e.gs7nin=b.gs7nin) GROUP BY B.GS7USR) as tb1 FULL JOIN (SELECT A.gs7usr,COUNT(A.GS7nin)as UPD FROM RGS8 a where A.GS7act='UPD'and A.gs7dte=20080310 and A.GS7tme=(select Max(D.GS7tme) AS tme from RGS8 D where D.gs7nin=A.gs7nin) GROUP BY a.GS7USR)as tb2 ON tb1.GS7usr = tb2.gs7usr FULL JOIN (SELECT c.gs7usr,COUNT(c.GS7nin)as DEL FROM RGS8 c where c.GS7act='DEL' and c.gs7dte=20080310 and c.GS7tme=(select Max(f.GS7tme) AS tme from RGS8 f where c.gs7nin=f.gs7nin) GROUP BY c.GS7USR) as tb3 ON tb1.gs7usr=tb3.gs7usr order by tb1.gs7usr But VFP V9 *keep on displaying *the same erroreach time I try to execute the query and that is "Command contains unrecognized phrase/ keyword " It worth mentioning that each query executes sucessflly when it is written in a separate query even if I tired the query with joining 2 tables only I still get the same error. |
original post any way...I figured out the problem
If the SQL statement you want to pass is quite long, it might exceed
the maximum length of a string literal in Visual FoxPro, which is 255
characters. Longer strings will cause a "Command contains unrecognized
phrase/keyword" error. Solution you can pass long SQL statements if
you break them up into several concatenated literals.
Well I did that and got rid of that Error and ended up with another
error the sqlexec() returns -1 wich indicates an error in the
connection or the sql query Any help ? I need to know how to fix this
problem ASAP. Please help
#3
| |||
| |||
|
|
I have three derived tables generated by select some columns from the same tabel . I have written SQL statements to summarize the information of each table based on certain columns in the main table.I wrote *an SQL statement where in it sql should perform the summarization of each of the three derived tables separately and then the summarized results be joined together using where clause. My query is as follows: SELECT tb1.gs7usr as User, tb1.ADD, tb2.UPD, tb3.DEL from (SELECT B.gs7usr,COUNT(B.GS7nin)as ADD FROM RGS8 B where B.GS7act='ADD'and B.gs7dte=20080310 and B.GS7tme=(select Max(E.GS7tme) AS tme from RGS8 E where e.gs7nin=b.gs7nin) GROUP BY B.GS7USR) as tb1 FULL JOIN (SELECT A.gs7usr,COUNT(A.GS7nin)as UPD FROM RGS8 a where A.GS7act='UPD'and A.gs7dte=20080310 and A.GS7tme=(select Max(D.GS7tme) AS tme from RGS8 D where D.gs7nin=A.gs7nin) GROUP BY a.GS7USR)as tb2 ON tb1.GS7usr = tb2.gs7usr FULL JOIN (SELECT c.gs7usr,COUNT(c.GS7nin)as DEL FROM RGS8 c where c.GS7act='DEL' and c.gs7dte=20080310 and c.GS7tme=(select Max(f.GS7tme) AS tme from RGS8 f where c.gs7nin=f.gs7nin) GROUP BY c.GS7USR) as tb3 ON tb1.gs7usr=tb3.gs7usr order by tb1.gs7usr But VFP V9 *keep on displaying *the same erroreach time I try to execute the query and that is "Command contains unrecognized phrase/ keyword " It worth mentioning that each query executes sucessflly when it is written in a separate query even if I tired the query with joining 2 tables only I still get the same error. |
original post any way...I figured out the problem
If the SQL statement you want to pass is quite long, it might exceed
the maximum length of a string literal in Visual FoxPro, which is 255
characters. Longer strings will cause a "Command contains unrecognized
phrase/keyword" error. Solution you can pass long SQL statements if
you break them up into several concatenated literals.
Well I did that and got rid of that Error and ended up with another
error the sqlexec() returns -1 wich indicates an error in the
connection or the sql query Any help ? I need to know how to fix this
problem ASAP. Please help
#4
| |||
| |||
|
|
I have three derived tables generated by select some columns from the same tabel . I have written SQL statements to summarize the information of each table based on certain columns in the main table.I wrote *an SQL statement where in it sql should perform the summarization of each of the three derived tables separately and then the summarized results be joined together using where clause. My query is as follows: SELECT tb1.gs7usr as User, tb1.ADD, tb2.UPD, tb3.DEL from (SELECT B.gs7usr,COUNT(B.GS7nin)as ADD FROM RGS8 B where B.GS7act='ADD'and B.gs7dte=20080310 and B.GS7tme=(select Max(E.GS7tme) AS tme from RGS8 E where e.gs7nin=b.gs7nin) GROUP BY B.GS7USR) as tb1 FULL JOIN (SELECT A.gs7usr,COUNT(A.GS7nin)as UPD FROM RGS8 a where A.GS7act='UPD'and A.gs7dte=20080310 and A.GS7tme=(select Max(D.GS7tme) AS tme from RGS8 D where D.gs7nin=A.gs7nin) GROUP BY a.GS7USR)as tb2 ON tb1.GS7usr = tb2.gs7usr FULL JOIN (SELECT c.gs7usr,COUNT(c.GS7nin)as DEL FROM RGS8 c where c.GS7act='DEL' and c.gs7dte=20080310 and c.GS7tme=(select Max(f.GS7tme) AS tme from RGS8 f where c.gs7nin=f.gs7nin) GROUP BY c.GS7USR) as tb3 ON tb1.gs7usr=tb3.gs7usr order by tb1.gs7usr But VFP V9 *keep on displaying *the same erroreach time I try to execute the query and that is "Command contains unrecognized phrase/ keyword " It worth mentioning that each query executes sucessflly when it is written in a separate query even if I tired the query with joining 2 tables only I still get the same error. |
original post any way...I figured out the problem
If the SQL statement you want to pass is quite long, it might exceed
the maximum length of a string literal in Visual FoxPro, which is 255
characters. Longer strings will cause a "Command contains unrecognized
phrase/keyword" error. Solution you can pass long SQL statements if
you break them up into several concatenated literals.
Well I did that and got rid of that Error and ended up with another
error the sqlexec() returns -1 wich indicates an error in the
connection or the sql query Any help ? I need to know how to fix this
problem ASAP. Please help
#5
| |||
| |||
|
|
I have three derived tables generated by select some columns from the same tabel . I have written SQL statements to summarize the information of each table based on certain columns in the main table.I wrote *an SQL statement where in it sql should perform the summarization of each of the three derived tables separately and then the summarized results be joined together using where clause. My query is as follows: SELECT tb1.gs7usr as User, tb1.ADD, tb2.UPD, tb3.DEL from (SELECT B.gs7usr,COUNT(B.GS7nin)as ADD FROM RGS8 B where B.GS7act='ADD'and B.gs7dte=20080310 and B.GS7tme=(select Max(E.GS7tme) AS tme from RGS8 E where e.gs7nin=b.gs7nin) GROUP BY B.GS7USR) as tb1 FULL JOIN (SELECT A.gs7usr,COUNT(A.GS7nin)as UPD FROM RGS8 a where A.GS7act='UPD'and A.gs7dte=20080310 and A.GS7tme=(select Max(D.GS7tme) AS tme from RGS8 D where D.gs7nin=A.gs7nin) GROUP BY a.GS7USR)as tb2 ON tb1.GS7usr = tb2.gs7usr FULL JOIN (SELECT c.gs7usr,COUNT(c.GS7nin)as DEL FROM RGS8 c where c.GS7act='DEL' and c.gs7dte=20080310 and c.GS7tme=(select Max(f.GS7tme) AS tme from RGS8 f where c.gs7nin=f.gs7nin) GROUP BY c.GS7USR) as tb3 ON tb1.gs7usr=tb3.gs7usr order by tb1.gs7usr But VFP V9 *keep on displaying *the same erroreach time I try to execute the query and that is "Command contains unrecognized phrase/ keyword " It worth mentioning that each query executes sucessflly when it is written in a separate query even if I tired the query with joining 2 tables only I still get the same error. |
original post any way...I figured out the problem
If the SQL statement you want to pass is quite long, it might exceed
the maximum length of a string literal in Visual FoxPro, which is 255
characters. Longer strings will cause a "Command contains unrecognized
phrase/keyword" error. Solution you can pass long SQL statements if
you break them up into several concatenated literals.
Well I did that and got rid of that Error and ended up with another
error the sqlexec() returns -1 wich indicates an error in the
connection or the sql query Any help ? I need to know how to fix this
problem ASAP. Please help
#6
| |||
| |||
|
|
I have three derived tables generated by select some columns from the same tabel . I have written SQL statements to summarize the information of each table based on certain columns in the main table.I wrote *an SQL statement where in it sql should perform the summarization of each of the three derived tables separately and then the summarized results be joined together using where clause. My query is as follows: SELECT tb1.gs7usr as User, tb1.ADD, tb2.UPD, tb3.DEL from (SELECT B.gs7usr,COUNT(B.GS7nin)as ADD FROM RGS8 B where B.GS7act='ADD'and B.gs7dte=20080310 and B.GS7tme=(select Max(E.GS7tme) AS tme from RGS8 E where e.gs7nin=b.gs7nin) GROUP BY B.GS7USR) as tb1 FULL JOIN (SELECT A.gs7usr,COUNT(A.GS7nin)as UPD FROM RGS8 a where A.GS7act='UPD'and A.gs7dte=20080310 and A.GS7tme=(select Max(D.GS7tme) AS tme from RGS8 D where D.gs7nin=A.gs7nin) GROUP BY a.GS7USR)as tb2 ON tb1.GS7usr = tb2.gs7usr FULL JOIN (SELECT c.gs7usr,COUNT(c.GS7nin)as DEL FROM RGS8 c where c.GS7act='DEL' and c.gs7dte=20080310 and c.GS7tme=(select Max(f.GS7tme) AS tme from RGS8 f where c.gs7nin=f.gs7nin) GROUP BY c.GS7USR) as tb3 ON tb1.gs7usr=tb3.gs7usr order by tb1.gs7usr But VFP V9 *keep on displaying *the same erroreach time I try to execute the query and that is "Command contains unrecognized phrase/ keyword " It worth mentioning that each query executes sucessflly when it is written in a separate query even if I tired the query with joining 2 tables only I still get the same error. |
original post any way...I figured out the problem
If the SQL statement you want to pass is quite long, it might exceed
the maximum length of a string literal in Visual FoxPro, which is 255
characters. Longer strings will cause a "Command contains unrecognized
phrase/keyword" error. Solution you can pass long SQL statements if
you break them up into several concatenated literals.
Well I did that and got rid of that Error and ended up with another
error the sqlexec() returns -1 wich indicates an error in the
connection or the sql query Any help ? I need to know how to fix this
problem ASAP. Please help
#7
| |||
| |||
|
|
I have three derived tables generated by select some columns from the same tabel . I have written SQL statements to summarize the information of each table based on certain columns in the main table.I wrote *an SQL statement where in it sql should perform the summarization of each of the three derived tables separately and then the summarized results be joined together using where clause. My query is as follows: SELECT tb1.gs7usr as User, tb1.ADD, tb2.UPD, tb3.DEL from (SELECT B.gs7usr,COUNT(B.GS7nin)as ADD FROM RGS8 B where B.GS7act='ADD'and B.gs7dte=20080310 and B.GS7tme=(select Max(E.GS7tme) AS tme from RGS8 E where e.gs7nin=b.gs7nin) GROUP BY B.GS7USR) as tb1 FULL JOIN (SELECT A.gs7usr,COUNT(A.GS7nin)as UPD FROM RGS8 a where A.GS7act='UPD'and A.gs7dte=20080310 and A.GS7tme=(select Max(D.GS7tme) AS tme from RGS8 D where D.gs7nin=A.gs7nin) GROUP BY a.GS7USR)as tb2 ON tb1.GS7usr = tb2.gs7usr FULL JOIN (SELECT c.gs7usr,COUNT(c.GS7nin)as DEL FROM RGS8 c where c.GS7act='DEL' and c.gs7dte=20080310 and c.GS7tme=(select Max(f.GS7tme) AS tme from RGS8 f where c.gs7nin=f.gs7nin) GROUP BY c.GS7USR) as tb3 ON tb1.gs7usr=tb3.gs7usr order by tb1.gs7usr But VFP V9 *keep on displaying *the same erroreach time I try to execute the query and that is "Command contains unrecognized phrase/ keyword " It worth mentioning that each query executes sucessflly when it is written in a separate query even if I tired the query with joining 2 tables only I still get the same error. |
original post any way...I figured out the problem
If the SQL statement you want to pass is quite long, it might exceed
the maximum length of a string literal in Visual FoxPro, which is 255
characters. Longer strings will cause a "Command contains unrecognized
phrase/keyword" error. Solution you can pass long SQL statements if
you break them up into several concatenated literals.
Well I did that and got rid of that Error and ended up with another
error the sqlexec() returns -1 wich indicates an error in the
connection or the sql query Any help ? I need to know how to fix this
problem ASAP. Please help
#8
| |||
| |||
|
|
I have three derived tables generated by select some columns from the same tabel . I have written SQL statements to summarize the information of each table based on certain columns in the main table.I wrote *an SQL statement where in it sql should perform the summarization of each of the three derived tables separately and then the summarized results be joined together using where clause. My query is as follows: SELECT tb1.gs7usr as User, tb1.ADD, tb2.UPD, tb3.DEL from (SELECT B.gs7usr,COUNT(B.GS7nin)as ADD FROM RGS8 B where B.GS7act='ADD'and B.gs7dte=20080310 and B.GS7tme=(select Max(E.GS7tme) AS tme from RGS8 E where e.gs7nin=b.gs7nin) GROUP BY B.GS7USR) as tb1 FULL JOIN (SELECT A.gs7usr,COUNT(A.GS7nin)as UPD FROM RGS8 a where A.GS7act='UPD'and A.gs7dte=20080310 and A.GS7tme=(select Max(D.GS7tme) AS tme from RGS8 D where D.gs7nin=A.gs7nin) GROUP BY a.GS7USR)as tb2 ON tb1.GS7usr = tb2.gs7usr FULL JOIN (SELECT c.gs7usr,COUNT(c.GS7nin)as DEL FROM RGS8 c where c.GS7act='DEL' and c.gs7dte=20080310 and c.GS7tme=(select Max(f.GS7tme) AS tme from RGS8 f where c.gs7nin=f.gs7nin) GROUP BY c.GS7USR) as tb3 ON tb1.gs7usr=tb3.gs7usr order by tb1.gs7usr But VFP V9 *keep on displaying *the same erroreach time I try to execute the query and that is "Command contains unrecognized phrase/ keyword " It worth mentioning that each query executes sucessflly when it is written in a separate query even if I tired the query with joining 2 tables only I still get the same error. |
original post any way...I figured out the problem
If the SQL statement you want to pass is quite long, it might exceed
the maximum length of a string literal in Visual FoxPro, which is 255
characters. Longer strings will cause a "Command contains unrecognized
phrase/keyword" error. Solution you can pass long SQL statements if
you break them up into several concatenated literals.
Well I did that and got rid of that Error and ended up with another
error the sqlexec() returns -1 wich indicates an error in the
connection or the sql query Any help ? I need to know how to fix this
problem ASAP. Please help
#9
| |||
| |||
|
|
I have three derived tables generated by select some columns from the same tabel . I have written SQL statements to summarize the information of each table based on certain columns in the main table.I wrote *an SQL statement where in it sql should perform the summarization of each of the three derived tables separately and then the summarized results be joined together using where clause. My query is as follows: SELECT tb1.gs7usr as User, tb1.ADD, tb2.UPD, tb3.DEL from (SELECT B.gs7usr,COUNT(B.GS7nin)as ADD FROM RGS8 B where B.GS7act='ADD'and B.gs7dte=20080310 and B.GS7tme=(select Max(E.GS7tme) AS tme from RGS8 E where e.gs7nin=b.gs7nin) GROUP BY B.GS7USR) as tb1 FULL JOIN (SELECT A.gs7usr,COUNT(A.GS7nin)as UPD FROM RGS8 a where A.GS7act='UPD'and A.gs7dte=20080310 and A.GS7tme=(select Max(D.GS7tme) AS tme from RGS8 D where D.gs7nin=A.gs7nin) GROUP BY a.GS7USR)as tb2 ON tb1.GS7usr = tb2.gs7usr FULL JOIN (SELECT c.gs7usr,COUNT(c.GS7nin)as DEL FROM RGS8 c where c.GS7act='DEL' and c.gs7dte=20080310 and c.GS7tme=(select Max(f.GS7tme) AS tme from RGS8 f where c.gs7nin=f.gs7nin) GROUP BY c.GS7USR) as tb3 ON tb1.gs7usr=tb3.gs7usr order by tb1.gs7usr But VFP V9 *keep on displaying *the same erroreach time I try to execute the query and that is "Command contains unrecognized phrase/ keyword " It worth mentioning that each query executes sucessflly when it is written in a separate query even if I tired the query with joining 2 tables only I still get the same error. |
original post any way...I figured out the problem
If the SQL statement you want to pass is quite long, it might exceed
the maximum length of a string literal in Visual FoxPro, which is 255
characters. Longer strings will cause a "Command contains unrecognized
phrase/keyword" error. Solution you can pass long SQL statements if
you break them up into several concatenated literals.
Well I did that and got rid of that Error and ended up with another
error the sqlexec() returns -1 wich indicates an error in the
connection or the sql query Any help ? I need to know how to fix this
problem ASAP. Please help
#10
| |||
| |||
|
|
I have three derived tables generated by select some columns from the same tabel . I have written SQL statements to summarize the information of each table based on certain columns in the main table.I wrote *an SQL statement where in it sql should perform the summarization of each of the three derived tables separately and then the summarized results be joined together using where clause. My query is as follows: SELECT tb1.gs7usr as User, tb1.ADD, tb2.UPD, tb3.DEL from (SELECT B.gs7usr,COUNT(B.GS7nin)as ADD FROM RGS8 B where B.GS7act='ADD'and B.gs7dte=20080310 and B.GS7tme=(select Max(E.GS7tme) AS tme from RGS8 E where e.gs7nin=b.gs7nin) GROUP BY B.GS7USR) as tb1 FULL JOIN (SELECT A.gs7usr,COUNT(A.GS7nin)as UPD FROM RGS8 a where A.GS7act='UPD'and A.gs7dte=20080310 and A.GS7tme=(select Max(D.GS7tme) AS tme from RGS8 D where D.gs7nin=A.gs7nin) GROUP BY a.GS7USR)as tb2 ON tb1.GS7usr = tb2.gs7usr FULL JOIN (SELECT c.gs7usr,COUNT(c.GS7nin)as DEL FROM RGS8 c where c.GS7act='DEL' and c.gs7dte=20080310 and c.GS7tme=(select Max(f.GS7tme) AS tme from RGS8 f where c.gs7nin=f.gs7nin) GROUP BY c.GS7USR) as tb3 ON tb1.gs7usr=tb3.gs7usr order by tb1.gs7usr But VFP V9 *keep on displaying *the same erroreach time I try to execute the query and that is "Command contains unrecognized phrase/ keyword " It worth mentioning that each query executes sucessflly when it is written in a separate query even if I tired the query with joining 2 tables only I still get the same error. |
original post any way...I figured out the problem
If the SQL statement you want to pass is quite long, it might exceed
the maximum length of a string literal in Visual FoxPro, which is 255
characters. Longer strings will cause a "Command contains unrecognized
phrase/keyword" error. Solution you can pass long SQL statements if
you break them up into several concatenated literals.
Well I did that and got rid of that Error and ended up with another
error the sqlexec() returns -1 wich indicates an error in the
connection or the sql query Any help ? I need to know how to fix this
problem ASAP. Please help
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.