THANKS TO ALL FROM FRANCO,
It looks like a can not do what I wanted to do.
I am going try to create temporary memory tables and relate them to my real tables.
From here I can work all the information I need into one temp table.
I am just worried about how fast this can work on large tables.
I will give this a try and let you all know how it works when finished.
Thanks again for your input ..................... Franco
SQL in HMG
Moderator: Rathinagiri
Re: SQL in HMG
All The Best,
Franco
Canada
Franco
Canada
Re: SQL in HMG
Realmente no entendí lo que deseabas hacer ni la razón, ni que tan grandes sean tus tablas...It looks like a can not do what I wanted to do.
...
From here I can work all the information I need into one temp table.
I am just worried about how fast this can work on large tables.
...
Pero yo lo haría de la siguiente manera...(Según tu ejemplo)
---------------------------------------------------------------------
I really did not understand what you wanted to do or why, or how big your tables ...
But I would do it as follows ... (In your example) ...
Code: Select all
#include <hmg.ch>
FUNCTION MAIN
tbls()
USE INV INDEX NUM1
DEFINE WINDOW Form_1 AT 0,0 WIDTH 500 HEIGHT 480 Main on release DBCloseALL() TITLE 'DBEVAL() Test WITH 500,000 regs.!!! By AndyGlezL'
@ 005,010 BUTTON Button_2 CAPTION ' SEARCH' WIDTH 75 HEIGHT 20 ;
TOOLTIP 'Add New Item:' ACTION ADD()
@ 005,180 LABEL LB_1 VALUE "" WIDTH 300 HEIGHT 20 FONT 'Verdana' SIZE 10 FONTCOLOR RED BOLD
@ 030,010 GRID Grid_Search OF Form_1 WIDTH 465 HEIGHT 420 FONT 'Verdana' SIZE 9 ;
HEADERS { "NUM1", "DESC", "NUM3" } WIDTHS { 100, 180, 120 }
END WINDOW
form_1.Center
form_1.Activate
RETURN
function tbls
LOCAL CF := {}
if ! file('inv.dbf')
aADD(CF,{'NUM1' ,'C' , 15,0})
aADD(CF,{'DESC' ,'C' , 25,0})
aAdd(CF,{'NUM2' ,'C' , 25,0})
aAdd(CF,{'NUM3' ,'C' , 25,0})
DBCREATE( 'INV.DBF',CF )
USE INV EXCLUSIVE NEW
do while recno()< 500000 // 500,000 Regs !!!!!!!!!!!!
Inv->( DBAPPEND())
Inv->( FIELDPUT(1, 'P' +ALLTRIM(STR(20+RECNO() ))) )
Inv->( FIELDPUT(2, 'DESC' +ALLTRIM(STR(20+RECNO() ))+CMONTH(DATE() + Random( 12 ) ) ) )
Inv->( FIELDPUT(3, 'N2' +ALLTRIM(STR(20+RECNO() ))) )
Inv->( FIELDPUT(4, 'N3' +ALLTRIM(STR(20+RECNO() ))) )
LOOP
enddo
INDEX ON NUM1 TO NUM1
endif
return
FUNCTION Add( )
Form_1.LB_1.Value := TIME() + " / "
Inv->( DBEVAL( {|| IF( SUBSTR( NUM1, 1, 2 ) == "P2" .AND. "Oct" $ DESC, ; // Condition
Domethod( "Form_1","Grid_Search","AddItem", { Inv->NUM1, Inv->DESC, Inv->NUM3 } ), ; // Action
nil ) } ) )
Form_1.LB_1.Value := Form_1.LB_1.Value + TIME() + " Regs: " + STR( Form_1.Grid_Search.ItemCount )
RETURN nil
-------------------------------------------------------------------------------------
1 minute 14 seconds to read 500,000 records and select 78,281
- Attachments
-
- DBEVALTEST.jpg (164.43 KiB) Viewed 3661 times
Andrés González López
Desde Guadalajara, Jalisco. México.
Desde Guadalajara, Jalisco. México.
Re: SQL in HMG
Andrés ,
Thank you for reply. I really like the way you have done this and it helps me a lot for single files.
The only thing is I have two files.
File 1 is invoice file with Fields Invoicenumber.... and more fields . Each record has a different invoice number
File 2 is invoice items with Fields Invoicenumber and more fields . It can have many records with same invoicenumber.
I do not understand how method works but is there a way to get records out both files that meet file 1 condition.
Like select file1 go top
do while ! eof() // or condition
select file2 and seek file1 invoice number.
do while invoicenumber = file1->invoicenumber
grid add file1->??, file2->//
skip and loop
enddo
select file 1
skip and loop
enddo
This is what I am working on but dbeval looks like it work better if it works with 2 or more tables.
Thanks again .................. Franco
Thank you for reply. I really like the way you have done this and it helps me a lot for single files.
The only thing is I have two files.
File 1 is invoice file with Fields Invoicenumber.... and more fields . Each record has a different invoice number
File 2 is invoice items with Fields Invoicenumber and more fields . It can have many records with same invoicenumber.
I do not understand how method works but is there a way to get records out both files that meet file 1 condition.
Like select file1 go top
do while ! eof() // or condition
select file2 and seek file1 invoice number.
do while invoicenumber = file1->invoicenumber
grid add file1->??, file2->//
skip and loop
enddo
select file 1
skip and loop
enddo
This is what I am working on but dbeval looks like it work better if it works with 2 or more tables.
Thanks again .................. Franco
All The Best,
Franco
Canada
Franco
Canada
-
- Posts: 1275
- Joined: Tue Sep 03, 2013 4:22 am
- Location: Tecámac, México
Re: SQL in HMG
Hola Andy,
Si pones la sentencia: "Do Events" al final de tu función será mejor!!!
31 Segundos!
Saludos
Si pones la sentencia: "Do Events" al final de tu función será mejor!!!
Code: Select all
FUNCTION Add( )
Form_1.LB_1.Value := TIME() + " / "
Inv->( DBEVAL( {|| IF( SUBSTR( NUM1, 1, 2 ) == "P2" .AND. "Oct" $ DESC, ; // Condition
Domethod( "Form_1","Grid_Search","AddItem", { Inv->NUM1, Inv->DESC, Inv->NUM3 } ), ; // Action
nil ) } ) )
Form_1.LB_1.Value := Form_1.LB_1.Value + TIME() + " Regs: " + STR( Form_1.Grid_Search.ItemCount )
Do Events
RETURN nil
Saludos
-
- Posts: 1275
- Joined: Tue Sep 03, 2013 4:22 am
- Location: Tecámac, México
Re: SQL in HMG
Hola Franco,
No entiendo bien lo que necesitas, pero creo que estableciendo una relación entre archivos se hace!!!
Ejemplo: C:\hmg.3.3.1\SAMPLES\Miscellaneous "Pedidos.prg"
Saludos
No entiendo bien lo que necesitas, pero creo que estableciendo una relación entre archivos se hace!!!
Ejemplo: C:\hmg.3.3.1\SAMPLES\Miscellaneous "Pedidos.prg"
Saludos
-
- Posts: 1275
- Joined: Tue Sep 03, 2013 4:22 am
- Location: Tecámac, México
Re: SQL in HMG
Hola Andy,
Viendo el tiempo que tarda en hacer el procedimiento son: 34 seg., y no 1 minuto y 14 segundos; bueno aunque como dijo Einstein
"El tiempo es relativo"
Saludos
Viendo el tiempo que tarda en hacer el procedimiento son: 34 seg., y no 1 minuto y 14 segundos; bueno aunque como dijo Einstein
"El tiempo es relativo"
Saludos