SQL in HMG

General Help regarding HMG, Compilation, Linking, Samples

Moderator: Rathinagiri

franco
Posts: 816
Joined: Sat Nov 02, 2013 5:42 am
DBs Used: DBF
Location: Canada

Re: SQL in HMG

Post by franco »

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
All The Best,
Franco
Canada
User avatar
andyglezl
Posts: 1461
Joined: Fri Oct 26, 2012 7:58 pm
Location: Guadalajara Jalisco, MX
Contact:

Re: SQL in HMG

Post by andyglezl »

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.
...
Realmente no entendí lo que deseabas hacer ni la razón, ni que tan grandes sean tus tablas...
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 minuto 14 segundos para leer 500,000 registros y seleccionar 78,281
-------------------------------------------------------------------------------------
1 minute 14 seconds to read 500,000 records and select 78,281
Attachments
DBEVALTEST.jpg
DBEVALTEST.jpg (164.43 KiB) Viewed 3661 times
Andrés González López
Desde Guadalajara, Jalisco. México.
franco
Posts: 816
Joined: Sat Nov 02, 2013 5:42 am
DBs Used: DBF
Location: Canada

Re: SQL in HMG

Post by franco »

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
All The Best,
Franco
Canada
Javier Tovar
Posts: 1275
Joined: Tue Sep 03, 2013 4:22 am
Location: Tecámac, México

Re: SQL in HMG

Post by Javier Tovar »

Hola Andy,

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
31 Segundos!
TiempoGrid.jpg
TiempoGrid.jpg (138.55 KiB) Viewed 3620 times
Saludos
Javier Tovar
Posts: 1275
Joined: Tue Sep 03, 2013 4:22 am
Location: Tecámac, México

Re: SQL in HMG

Post by Javier Tovar »

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
Javier Tovar
Posts: 1275
Joined: Tue Sep 03, 2013 4:22 am
Location: Tecámac, México

Re: SQL in HMG

Post by Javier Tovar »

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
Post Reply