Visual FoxPro SQL Queries - Problem with cross-tab

Author

Problem with cross-tab

Dawid.Federowicz@gmail.com

2006-02-06, 7:24 am

Hi,

I have a big problem with cross-table:

GCDIR='C:\' && there is C_SI table

SELECT c_si.shopcode,c_si.viz,c_si.war FROM (GCDIR)+'c_si';
GROUP BY c_si.typ,c_si.shopcode,c_si.viz;
ORDER BY c_si.viz DESC INTO CURSOR SYS(2015)
DO (_GENXTAB) WITH 'C_SI1'
SELECT * FROM C_SI1 INTO ARRAY arc_si

If I try to runing it, I've an information then VFP can't find C_SI1...

I don't know what I do wrong...
Could you help, please?

Cindy Winegarden

2006-02-07, 3:25 am

Hi Dawid,

Do you get the error on the 4th or 5th line of your code below?
Are you sure that your Select statement generates a cursor with at least one
row?
Does your crosstab create a cursor/file named C_SI1?
Have you tried "... With 'C_SI1.dbf'" so it creates a DBF instead of a
cursor?

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn
.com www.cindywinegarden.com

<Dawid.Federowicz@gmail.com> wrote in message
news:1139222248.194533.120490@f14g2000cwb.googlegroups.com...
> GCDIR='C:' && there is C_SI table
>
> SELECT c_si.shopcode,c_si.viz,c_si.war FROM (GCDIR)+'c_si';
> GROUP BY c_si.typ,c_si.shopcode,c_si.viz;
> ORDER BY c_si.viz DESC INTO CURSOR SYS(2015)
> DO (_GENXTAB) WITH 'C_SI1'
> SELECT * FROM C_SI1 INTO ARRAY arc_si
>
> If I try to runing it, I've an information then VFP can't find C_SI1...

Dawid.Federowicz@gmail.com

2006-02-07, 7:24 am

Hi, Cindy,

No, crosstab create only cursor. Even if I write:

DO (_GENXTAB) WITH 'C:\C_SI1'

I don't have a table in C:\... only cursor which's name C_SI1

I try to create application, which using this piece of codes. In
application I've this error in "SELECT * FROM C_SI1...".
But if I runing it in command line - I don't have any problems, it
works very good!

I was try "... WITH 'C_SI1.dbf'", as you suggest. But C_SI1 table
wasn't a crosstab. It was the same, which is in C_SI...

I really don't know, why this look like this... :-/

Olaf Doschke

2006-02-07, 8:25 pm

> ...INTO CURSOR SYS(2015)
> DO (_GENXTAB) WITH 'C_SI1'
SYS(2015) generates a random name.
So there is no C_SI1 when you call _GENXTAB.

Change the select to
.....INTO CURSOR C_SI1

If this was just a typo and you use SYS(2015)
for both the cursor name and as Parameter of
_GENXTAB, then it's even worse:
SYS(2015) generates a new random name
every time called, so two calls won't generate
the same name. If you want a random name
anbd reuse it later store the SYS(2015) to
a variable with the alias name:

lcAlias = SYS(2015)
....
....INTO CURSOR (lcAlias)
....
DO (_GENXTAB) WITH lcAlias
....

Bye, Olaf.

Cindy Winegarden

2006-02-07, 8:25 pm

Hi Olaf,

Isn't the cursor name supplied as a parameter to _GenXTab the name of the
destination cursor? Doesn't _GenXTab just use the current work area as its
input? In that case Sys(2015) is fine for the input cursor and C_SI1 is fine
for the output cursor?

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn
.com www.cindywinegarden.com

"Olaf Doschke" < T2xhZi5Eb3NjaGtlQFNl
dG1pY3MuZGU@strconv.14
< T2xhZi5Eb3NjaGtlQFNl
dG1pY3MuZGU@strconv.14> wrote in message
news:OKGcsRCLGHA.4052@TK2MSFTNGP15.phx.gbl...
> SYS(2015) generates a random name.
> So there is no C_SI1 when you call _GENXTAB.
>
> Change the select to
> ....INTO CURSOR C_SI1
>
> If this was just a typo and you use SYS(2015)
> for both the cursor name and as Parameter of
> _GENXTAB, then it's even worse:
> SYS(2015) generates a new random name
> every time called, so two calls won't generate
> the same name. If you want a random name
> anbd reuse it later store the SYS(2015) to
> a variable with the alias name:
>
> lcAlias = SYS(2015)
> ...
> ...INTO CURSOR (lcAlias)
> ...
> DO (_GENXTAB) WITH lcAlias
> ...
>
> Bye, Olaf.
>
>

Olaf Doschke

2006-02-07, 8:25 pm

Hi Cindy,

> Isn't the cursor name supplied as a parameter to _GenXTab the name of the
> destination cursor? Doesn't _GenXTab just use the current work area as its
> input? In that case Sys(2015) is fine for the input cursor and C_SI1 is
> fine for the output cursor?

Sorry, you're right.

Then perhaps _Genxtab cannot create the result table, eg because there
would be more than 254 columns. But an error should occur and be
prompted from _genxtab.

Bye, Olaf.

2006-02-08, 3:24 am

If there are >254 distinct values in the viz column genXtab won't be able to
create an xtab cursor as it would have to have >254 columns.
-Anders

<Dawid.Federowicz@gmail.com> skrev i meddelandet
news:1139222248.194533.120490@f14g2000cwb.googlegroups.com...
> Hi,
>
> I have a big problem with cross-table:
>
>
> GCDIR='C:' && there is C_SI table
>
> SELECT c_si.shopcode,c_si.viz,c_si.war FROM (GCDIR)+'c_si';
> GROUP BY c_si.typ,c_si.shopcode,c_si.viz;
> ORDER BY c_si.viz DESC INTO CURSOR SYS(2015)
> DO (_GENXTAB) WITH 'C_SI1'
> SELECT * FROM C_SI1 INTO ARRAY arc_si
>
> If I try to runing it, I've an information then VFP can't find C_SI1...
>
> I don't know what I do wrong...
> Could you help, please?
>

Cindy Winegarden

2006-02-08, 8:26 pm

Hi Dawid,

Do you know whether you actually get a table or just a cursor when you run
your EXE? Have you tried stepping through the debugger and checking where
C_SI1 is created? My thought is that the file is created someplace that your
next statement isn't looking for it.

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn
.com www.cindywinegarden.com

<Dawid.Federowicz@gmail.com> wrote in message
news:1139307371.289381.49290@g44g2000cwa.googlegroups.com...
> Hi, Cindy,
>
> No, crosstab create only cursor. Even if I write:
>
> DO (_GENXTAB) WITH 'C:\C_SI1'
>
> I don't have a table in C:\... only cursor which's name C_SI1
>
> I try to create application, which using this piece of codes. In
> application I've this error in "SELECT * FROM C_SI1...".
> But if I runing it in command line - I don't have any problems, it
> works very good!
>
> I was try "... WITH 'C_SI1.dbf'", as you suggest. But C_SI1 table
> wasn't a crosstab. It was the same, which is in C_SI...
>
> I really don't know, why this look like this... :-/
>

Sponsored Links