Internal error with UNION

[FRUX]

Hi!

I´m getting an error while executing SQL SELECT with UNION:
"Internal Error" and fox dies, even executing the program in debug mode (trace window, step by step). The error message is in a System Error window (Wite window with the message at the middle)

This is a sample code that crashes, and seems to be simple:

select a.anio,a.anio_oper,a.fecha,a.num_operac,a.tipo_operac,a.peso,a.memoria,a.volumen,;
a.almacen,a.num_envase,a.alm_dest ;
from historic a into cursor trasv_cursor;
where a.proceso=w_proceso .and. ALLTRIM(a.numero)=ALLTRIM(w_numero) ;
.and. a.fraccion=w_fraccion .and. a.recepcion=padl(alltrim(str(w_recep)),4,"0&quot ;
.and. a.anio=w_anio ;
union ;
select a.anio,a.anio_oper,a.fecha,a.num_operac,a.tipo_operac,a.peso,a.memoria,a.volumen,;
a.almacen,a.num_envase,"" ;
from maestro a ;
where a.proceso=w_proceso .and. ALLTRIM(a.numero)=ALLTRIM(w_numero) ;
.and. a.fraccion=w_fraccion .and. a.recepcion=padl(alltrim(str(w_recep)),4,"0&quot ;
.and. a.anio=w_anio ;
order by 1,3,5,4


Has anyone this trouble? What could be the reason?

Thanks.

 

[StewartJ]

Fields produced by the two SELECTs must be identical in type and length. Your last field in the second select is "". It should be the same length as a.alm_dest.

I would put the INTO CURSOR clause in the second SELECT.

A suggestion for efficiency :

Why not ALLTRIM(w_numero) outside of the select? Better, pad it so that you do not need to ALLTRIM(a.numero).

I would expect your code to generate an error but not to crash the system!

 

[FRUX]

Thanks for your suggestion, and sorry my poor english.

I know the fields in the select clauses with union must be identical, but like this example, I've used combinations between "something and nothing", having in mind that they must be of the same data type, and allways works fine. If fields are different in length, the only thing could occur is to lose data, but not to give an error or crash the system.

If I put the INTO CURSOR in other select than the first, Fox gives me a syntax error message.
As you say, what I don't understand is that I could expect an error message, but a crash...

Take a look at this sample code that doesn't crash:

select d.num_envase,a.*,b.nombre_emp,c.nombre_emp,d.fraccion,d.peso,d.volumen;
from recepcio a,empresa b,empresa c,maestro d into cursor rec_cursor ;
where a.fecha_alta>=w_fecha1 .and. a.fecha_alta<=w_fecha2 ;
.AND. b.codigo_emp=a.cliente ;
.AND. c.codigo_emp=a.transport ;
.AND. d.num_operac=a.num_ficha .AND. d.proceso=a.proceso ;
.AND. d.numero=a.numero .AND. d.num_total=a.num_total ;
.AND. d.anio=a.anio .AND. d.tipo_opera=&quot;001&quot;;
.AND. d.almacen=a.almacen .AND. d.envase=a.envase ;
.AND. d.estado=a.estado ;
.AND. D.PRODUCTO=a.producto ;
union;
select d.num_envase,a.*,b.nombre_emp,c.nombre_emp,d.fraccion,d.peso,d.volumen;
from recepcio a,empresa b,empresa c,historic d ;
where a.fecha_alta>=w_fecha1 .and. a.fecha_alta<=w_fecha2 ;
.AND. b.codigo_emp=a.cliente ;
.AND. c.codigo_emp=a.transport ;
.AND. d.num_operac=a.num_ficha .AND. d.proceso=a.proceso ;
.AND. d.numero=a.numero .AND. d.num_total=a.num_total ;
.AND. d.anio=a.anio .AND. d.tipo_opera=&quot;001&quot;;
.AND. d.almacen=a.almacen .AND. d.envase=a.envase ;
.AND. D.PRODUCTO=a.producto ;
.AND. d.estado=a.estado ;
order by 2,13,12,1


???????

P.D.: Tracing the program, I've seen that it makes the first select (thanks to the thermometer), and crashes just when is going to start the second. Maybe this helps you

Thank you again.

 

[rgbean]

You never indicated what version of FoxPro you were using? FP 2.0, FPD 2.5b, FPW 2.6a, etc.?

The obvious difference between the Good and Bad examples is the calls on ALLTRIM() and PADL(). Assuming w_numero and w_recep aren't changing (i.e. are varibles set before the SELECTs), why not &quot;fix&quot; them before the statement?

lw_numero = PADR(w_numero, LEN(historic.numero))
lw_recep = padl(alltrim(str(w_recep)),4,&quot;0&quot

...
where a.proceso=w_proceso .and. a.numero=lw_numero ;
.and. a.fraccion=w_fraccion .and. a.recepcion=lw_recep ;
...

Rick

 

[FRUX]

Hi,

I'm starting to have a big problem...

I've tried to do all that changes above with the same result. Anyway, Fox should allow to use this functions in a select clause, in fact, I use it in lot of programs.
The big problem is that recently I got the same error in this Select:

select a.*, c.descripcio,c.precio,c.inputa,;
c.caracteris,c.limite,c.vigente,;
b.nombre_emp,b.oficina, b.desc_ofic;
from contrat1 a, empresa b, contrat2 c into cursor cursor50;
where a.anio=w_anio2 .and. a.numero >= w_num1 .and. a.numero<= w_num2;
.and. c.anio=a.anio .and. c.numero=a.numero;
.and. a.cliente=b.codigo_emp ;
order by 1

Contrat1 has 25094 records with 863 of lenght
Contrat2 has 54617 records with 195 of lenght
Empresa has 5656 records with 395 of lenght
(It is too long a display structure here)

I'm running Fox 2.6a patched for fast machines, on a P-IV 1GHz, 128 MB RAM, memlimit=100 (but I've tried all possible combinations), MVCOUNT=3600, ATI Rage 128 Ultra, 1024x768x16bits as resolution. The machine is a DELL Optiplex GX240, with the latest drivers. In other machines, as P-III, etc happens the same, except in &quot;slow&quot; machines (< P-II).

So, I think the functions (alltrim, padl, etc) into the select clause is not the problem.

Any idea?

Thanks.

 

[StewartJ]

This looks OK but you copuld try prefixing all memory variables with m.

Try taking out one WHERE condition at a time although I doubt that it will help!

If it still does not work, it's probably time for the desperation tries. Rebuild (not just reindex) all the indexes. Do the tables have any memo fields? If so, is there any sign of corruption? Try another machine. Reinstall FoxPro.

Incidentally,

a.numero >= w_num1 .and. a.numero<= w_num2

would be better written as

a.numero BETWEEN m.w_num1 and m.w_num2

 

[DSummZZZ]

Make sure you have access and plenty of room where your temp directory is also. If the .TMP files are getting written to a disk where there isn't enough room to handle them, that could be a problem. Or if they're attempting to write to a network drive where you don't have sufficient rights, that may be a problem too.

Like StewartJ said, start with recreating the indexes also.


Dave S.