Alpha Video Training
Results 1 to 10 of 10

Thread: Problem with queries

  1. #1
    Member Graham Wickens's Avatar
    Real Name
    Graham Wickens
    Join Date
    Apr 2000
    Location
    Gloucestershire, UK
    Posts
    786

    Default Problem with queries

    Although I can use queries quite happily, I seem to get to a "Tide-mark" on how many values I can query in any one query.

    I can have anything from one value to find, to sometimes over 1000 values to find, My Table "Logging" has 516,000 records.
    I can use query filters like (reg="G-XWBB".or.reg="HB-JCT") for a smallish number of search values, but beyond a certain point the filter string is truncated by A5 and the query fails. I then tried reading the values into a table and access the results in a Set (the entered values as master and "Logging" as child), again this worked of a fashion, but becomes very slow and cumbersome as the total number of records grows.

    Is there a way of build filters that will cater for any amount of "Reg=".....".or." ?

    thanks
    --
    Support your local Search and Rescue Unit, Get Lost!

    www.westrowops.co.uk

  2. #2
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,358

    Default Re: Problem with queries

    The query string truncation is a nuisance, but I suspect it is necessary within Xbasic.
    Can you explain a bit more about the volume please?
    If you're just looking for Reg numbers - and say you want a list of 1000, then you may need to sub-caregorise the reg numbers into groups, or use something different like fuel type, airfield, seats etc.
    If you really need 1000 then you could separate into blocks and upload to a transaction table for processing.

    What do you intend to do with the list of 1000 records?
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  3. #3
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,388

    Default Re: Problem with queries

    Hi Graham.

    How are you generating the list of 1000 records (do you have that many columns you are checking) ?

  4. #4
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,685

    Default Re: Problem with queries

    In your post yesterday you showed us an image with a radio button that had 21 selections. Are these referring to indexed fields in the table? If they are, a drop down list might work better as you can set up the variable with display|value so what the user selects returns the field to sort by and then another text box (or two if a range is needed in a conditional object) can be displayed for the user to enter the search criteria. That should help the query you build be more concise based on just those variables using a select statement to build the filter for the query.
    Last edited by MoGrace; 10-04-2019 at 12:05 PM.
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  5. #5
    Member Graham Wickens's Avatar
    Real Name
    Graham Wickens
    Join Date
    Apr 2000
    Location
    Gloucestershire, UK
    Posts
    786

    Default Re: Problem with queries

    in this particular instance I am logging when seen, where seen and seen as against a particular airframe.
    Im trying to make it so I have the least amount of typing to do.
    the input file is a straight text file.

    Code:
    delete MyCollection
    dim shared vLogFileName as c = filename_decode("[logPath]\*.log")
    dim shared vLogText     as c = ""
    dim shared vFilter 	as c = ""
    vResult = ui_dlg_box("Log Entry File Name",<<%dlg%
    {Background=#204+153+255}
    {Font=Trebuchet ms,8}
    {region}
    [%p=ui_get_file("Input Log Filename","Log(*.Log)|Text (*.txt)",vLogFileName,"X");I=$a5_smart_file%.80vLogFilename];
    {endregion};
    {line=1,0};
    {region}
    <OK!OK> <Cancel!CANCEL>
    {endregion};
    %dlg%,<<%code%
    if .not. (a_dlg_button = "Cancel" .or. a_dlg_button = "OK" ) then
    	a_dlg_button = ""
    end if
    %code%)
    if vResult = "OK" then
    	fp = file.open(vLogFileName,FILE_RW_SHARED)
    	WHILE .not. fp.eof()
    		text = fp.read_line()
    		vCountry = ""
    		IF left(text,1)="#" THEN
    			Seendate  = substr(text,2,10)
    			LocateNo  = substr(text,13,2)
    			SeenWhere = substr(text,16)
    		else
    			if ("," $ text) then
    				vCountry = ut(substr(rtrim(text),at(",",text)+1))
    				text = left(text,at(",",text)-1)
    			end if
    			if ("/" $ text) then
    				vReg = word(text,1,"/",1)
    				vCode = word(text,2,"/")
    			else
    				vReg = text
    				vCode = ""
    			end if
    			vLogText = vLogText+ut(vReg)-"|"-vCode-"|"-vCountry-"|"-Seendate-"|"-LocateNo-"|"-ut(SeenWhere)+crlf()
    		END IF
    	END WHILE
    	fp.close()
    	dim global MyCollection as U
    	rec_count = *count(vLogText)
    	dim MyFilter[rec_count] as p
    	MyFilter.initialize_properties("REG|CODE|COUNTRY|SEENDATE|LOCATENO|SEENWHERE",vLogText)
    	myCollection.initialize("1|2|3|4|5|6","1","1|2|3|4|5|6",vLogText)
    	for x = 1 to rec_count
    		vFilter = vFilter + "reg='"-rtrim(MyFilter[x].reg-"'.or.")
    	next x	
    	vFilter = left(vFilter,len(vFilter)-4)
    	vOrder = "REG"
    	DIM Shared varP_logging as P
    	DIM layout_name as c 
    	layout_name = "logging_Detail_log"
    	DIM tempP as p
    	tempP=obj(":"+object_Name_normalize(word(layout_name,1,"@")))
    	if is_object(tempP) then
    		if tempP.class() = "form" .or. tempP.class() = "browse" then
    			tempP.activate()
    		else
    			varP_logging = :Browse.viewqueried(layout_name,vFilter,vOrder)
    		end if
    	else
    		varP_logging = :Browse.viewqueried(layout_name,vFilter,vOrder)
    	end if
    end if
    the input data format is:
    #12/08/1986|01|South Cerney
    06 blue,RUSSIANFEDER
    07 black,RUSSIANFEDER
    08 red,RUSSIANFEDER
    200/XC,FRANCE
    WZ755/BGA3481,UK
    #04/10/2019|01|{Paganhill}
    C-GBKF
    D-EEFV
    D-EFJN
    D-EHDW
    D-EHLE
    using a global collection so I can pass the relevant when,where,as to the correct record in the Viewd Browse

    this has the following code on the onRowdblclick for the loaded form

    Code:
    dim global MyCollection as U
    t = table.current()
    mylog = MyCollection_get(rtrim(t.reg))
    if t.as = "" then
    	t.change_begin()
    	t.H      = word(myLog,4,"|")
            t,code = word(myLog,2,"|")
    	t.Where  = word(MyLog,5,"|")
    	t.When   = word(myLog,3,"|")
    	t.as     = rtrim(t.Reg)
    	t.change_end(.t.)
    else
    	reply = ui_msg_box("","Confirm Removal of Logging Data for "+this.reg,UI_QUESTION_SYMBOL+UI_YES_NO)
    	if reply = UI_YES_SELECTED then
    		t.change_begin()
    		t.H = ""
    		t.where = ""
    		t.when = ""
    		t.as = ""
    		t.change_end(.t.)
    	end if
    end if
    topparent.refresh_layout()
    --
    Support your local Search and Rescue Unit, Get Lost!

    www.westrowops.co.uk

  6. #6
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,358

    Default Re: Problem with queries

    I think I get it -maybe.
    Have you thought about using a cascading Tree ( Robin is good at these ), so you can click through the items, each of which will show a drop down list.
    You should be able to get all the info if it is available, through mouse clicks only.
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  7. #7
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,685

    Default Re: Problem with queries

    Actually if using a collection, Tim Kiebert might be the one to ask.
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  8. #8
    Member Graham Wickens's Avatar
    Real Name
    Graham Wickens
    Join Date
    Apr 2000
    Location
    Gloucestershire, UK
    Posts
    786

    Default Re: Problem with queries

    I calculated that I could have a maximum of 64 iterations in my query.filter so my next attempt is thus:

    Code:
    delete MyCollection
    dim shared vLogFileName as c = filename_decode("[logPath]\*.log")
    dim shared vLogText     as c = ""
    dim shared vFilter 		as c = ""
    vResult = ui_dlg_box("Log Entry File Name",<<%dlg%
    {Background=#204+153+255}
    {Font=Trebuchet ms,8}
    {region}
    [%p=ui_get_file("Input Log Filename","Log(*.Log)|Text (*.txt)",vLogFileName,"X");I=$a5_smart_file%.80vLogFilename];
    {endregion};
    {line=1,0};
    {region}
    <OK!OK> <Cancel!CANCEL>
    {endregion};
    %dlg%,<<%code%
    if .not. (a_dlg_button = "Cancel" .or. a_dlg_button = "OK" ) then
    	a_dlg_button = ""
    end if
    %code%)
    if vResult = "OK" then
    	fp = file.open(vLogFileName,FILE_RW_SHARED)
    	WHILE .not. fp.eof()
    		text = fp.read_line()
    		vCountry = ""
    		IF left(text,1)="#" THEN
    			Seendate  = substr(text,2,10)
    			LocateNo  = substr(text,13,2)
    			SeenWhere = substr(text,16)
    		else
    			if ("," $ text) then
    				vCountry = ut(substr(rtrim(text),at(",",text)+1))
    				text = left(text,at(",",text)-1)
    			end if
    			if ("/" $ text) then
    				vReg = word(text,1,"/",1)
    				vCode = word(text,2,"/")
    			else
    				vReg = text
    				vCode = ""
    			end if
    			vLogText = vLogText+ut(vReg)-"|"-vCode-"|"-vCountry-"|"-Seendate-"|"-LocateNo-"|"-ut(SeenWhere)+crlf()
    		END IF
    	END WHILE
    	fp.close()
    	dim global MyCollection as U
    	myCollection.initialize("1|2|3|4|5","1","1|2|3|4|5|6",vLogText)
    	dim entry_count as n = *count(vLogText)
    	dim vLogData[entry_count] as p
    	vLogData.initialize_properties("reg|Code|Country|SeenDate|SeenOrder|SeenWhere",vLogText)
    	dim vFilterTxt[0] as c
    	dim index as n
    	for block = 0 to int(entry_count/64 + 1)
    		vFilter = ""
    		for iteration = 1 to 64
    			index = block * 64 + iteration
    			if index > entry_count then
    				exit for
    			end if
    			vFilter = vFilter + "reg='"-vLogData[index].reg-"'.or."
    		next iteration
    		vFilterTxt[]=left(vFilter,len(vFilter)-4)
    	next Block
    	vOrder = "REG"
    	for iterate = 1 to Block
    		DIM Shared varP_logging as P
    		layout_name = "logging_Detail_log"
    		DIM tempP as p
    		tempP=obj(":"+object_Name_normalize(word(layout_name,1,"@")))
    		if is_object(tempP) then
    			if tempP.class() = "form" .or. tempP.class() = "browse" then
    				tempP.activate()
    			else
    				varP_logging = :Form.viewqueried(layout_name,vFilterTxt[iterate],vOrder,"dialog")
    			end if
    		else
    			varP_logging = :Form.viewqueried(layout_name,vFilterTxt[iterate],vOrder,"dialog")
    		end if
    		if is_object(varP_logging) then
    			varP_Logging.close()
    		end if
    	next iterate
    end if
    end
    when I try and iterate the different filter statements/form it only allows me to display the first filter/form then ends.
    what do I need to do to make the script loop through the filtered forms?
    Last edited by Graham Wickens; 10-06-2019 at 05:06 AM.
    --
    Support your local Search and Rescue Unit, Get Lost!

    www.westrowops.co.uk

  9. #9
    Member Graham Wickens's Avatar
    Real Name
    Graham Wickens
    Join Date
    Apr 2000
    Location
    Gloucestershire, UK
    Posts
    786

    Default Re: Problem with queries

    I have been teaching myself how to use arrays and collections, so tried this problem using arrays.

    Code:
    delete MyCollection
    dim shared vLogFileName as c = filename_decode("[logPath]\*.log")
    dim shared vLogText     as c = ""
    dim shared vFilter 		as c = ""
    vResult = ui_dlg_box("Log Entry File Name",<<%dlg%
    {Background=#204+153+255}
    {Font=Trebuchet ms,8}
    {region}
    [%p=ui_get_file("Input Log Filename","Log(*.Log)|Text (*.txt)",vLogFileName,"X");I=$a5_smart_file%.80vLogFilename];
    {endregion};
    {line=1,0};
    {region}
    <OK!OK> <Cancel!CANCEL>
    {endregion};
    %dlg%,<<%code%
    if .not. (a_dlg_button = "Cancel" .or. a_dlg_button = "OK" ) then
    	a_dlg_button = ""
    end if
    %code%)
    if vResult = "OK" then
    	fp = file.open(vLogFileName,FILE_RW_SHARED)
    	WHILE .not. fp.eof()
    		text = fp.read_line()
    		vCountry = ""
    		IF left(text,1)="#" THEN
    			Seendate  = substr(text,2,10)
    			LocateNo  = substr(text,13,2)
    			SeenWhere = substr(text,16)
    		else
    			if ("," $ text) then
    				vCountry = ut(substr(rtrim(text),at(",",text)+1))
    				text = left(text,at(",",text)-1)
    			end if
    			if ("/" $ text) then
    				vReg = word(text,1,"/",1)
    				vCode = word(text,2,"/")
    			else
    				vReg = text
    				vCode = ""
    			end if
    			vLogText = vLogText+ut(vReg)-"|"-vCode-"|"-vCountry-"|"-Seendate-"|"-LocateNo-"|"-ut(SeenWhere)+crlf()
    		END IF
    	END WHILE
    	fp.close()
    	dim global MyCollection as U
    	myCollection.initialize("1|2|3|4|5","1","1|2|3|4|5|6",vLogText)
    	dim entry_count as n = *count(vLogText)
    	dim vLogData[entry_count] as p
    	vLogData.initialize_properties("reg|Code|Country|SeenDate|SeenOrder|SeenWhere",vLogText)
    	dim vFilterTxt[0] as c
    	dim index as n
    	' Build filter Text
    	for block = 0 to int(entry_count/64 + 1)
    		vFilter = ""
    		for iteration = 1 to 64
    			index = block * 64 + iteration
    			if index > entry_count then
    				exit for
    			end if
    			vFilter = vFilter + "reg='"-vLogData[index].reg-"'.or."
    		next iteration
    		vFilterTxt[]=left(vFilter,len(vFilter)-4)
    	next Block
    	block = vFilterTxt.first_empty()-1
    	vFilterTxt.resize(block)
    	vOrder = "REG"
    	dim idx as n
    	' build Display Data
    	dim vDisplayData[9999] as p
    	vDisplayData.initialize_from_table("Logging",vFilterTxt[1],vOrder)
    	for iterate = 2 to block
    		vDisplayData.initialize_from_table("Logging",vFilterTxt[iterate],vOrder,.t.)
    	next iterate
    	rec_numb = vDisplayData.first_empty()
    	vDisplayData.resize(rec_numb-1)
    	title_text = "Enter Logging"
    	ui_dlg_box("@="+title_text,<<%dlg%
    {Background=#153+204+255}
    {region1}
    {font=Courier New,7}
    {endregion2};
    {region=3}
    [%O==FmtCurrLogg(eval(value))%.60,16idx^#vDisplayData!select];
    {endregion=3}
    {region}
    {line=2,0};
    {justify=center}
    <OK><CLOSE>
    {endregion};
    %dlg%,<<%code%
    	if a_dlg_button = "select" then
    		t = table.open("logging")
    		query.filter = "unique="-quote(rtrim(vDisplayData[idx].unique))
    		showvar(query.filter)
    		query.order = "recno()"
    		t.query_create()
    		ThisReg = myCollection.get(rtrim(vDisplayData[idx].reg))
    		if rtrim(t.as) = "" then
    			t.change_begin()
    			t.H      = word(ThisReg,5,"|")
    			t.Where  = word(ThisReg,6,"|")
    			t.When   = word(ThisReg,4"|")
    			t.as     = t.reg
    			t.change_end(.t.)
    		else
    			reply = ui_msg_box("","Confirm Removal of Logging Data for "+word(ThisReg,1,"|"),UI_QUESTION_SYMBOL+UI_YES_NO)
    			if reply = UI_YES_SELECTED then
    				t.change_begin()
    				t.h = ""
    				t.where = ""
    				t.when = ""
    				t.as = ""
    				t.change_end(.t.)
    			end if
    		end if
    		a_dlg_button = ""
    	end if
    %code%)	
    end if
    end
    FUNCTION FmtCurrLogg as c (ar as p)
    	FmtCurrLogg = padr(rtrim(ar.reg),12," ")+padr(rtrim(ar.Code),10," ")+padr(rtrim(ar.H),3," ")+ar.When+" "+ar.Where
    end function
    just wondering if there is a quicker method of appending all the queried data into one array?
    --
    Support your local Search and Rescue Unit, Get Lost!

    www.westrowops.co.uk

  10. #10
    Member Graham Wickens's Avatar
    Real Name
    Graham Wickens
    Join Date
    Apr 2000
    Location
    Gloucestershire, UK
    Posts
    786

    Default Re: Problem with queries

    found a more efficient method (less searches of table to create array)

    Code:
    	dim shared MyCollection as U
    	myCollection.initialize(vCollFormat,"1",vCollFormat,vCollText)
    	dim vFilter as c = ""
    	KeyVal = mycollection.first()
    	for n = 1 to myCollection.size()
    		CurrentVal = mycollection.get(KeyVal)
    		if word(CurrentVal,4,"|") <> "" then
    			vFilter = vFilter-"(reg='"-word(currentVal,1,"|")-"'.and.country='"-word(currentVal,4,"|")-"').or."
    		else
    			vFilter = vFilter - "reg='"-word(CurrentVal,1,"|")-"'.or."
    		end if
    		KeyVal = mycollection.next(KeyVal)
    	next n
    	dim vFilterLen as n = len(vfilter)
    	dim vFilterTxt[0] as c
    	if vFilterLen > 1024 then
    		dim shared vFilterData as c = rtrim(left(vFilter,1024))
    		dim shared vFilterLen as n = len(vfilter)
    		dim shared vDataLen as n = 1
    		dim Pos as n = 1
    		while vDatalen > 0
    			vDataLen   = len(vFilterData)-len(word(vFilterData,-1,".or."))
    			vFilterTxt[] = rtrim(substr(vFilter,Pos,vDatalen-4))
    			pos = pos + vDatalen
    			vFilterData = rtrim(substr(vFilter,pos,1024))
    		end while
    		vFilterTxt.resize(vFilterTxt.first_empty()-1)
    	else
    	vFilterTxt[] = left(vFilter,len(rtrim(vFilter))-4)
    	end if
    	vOrder = "REG"
    	dim shared vDisplayData[9999] as p
    	vDisplayData.initialize_from_table("Logging",vFilterTxt[1],vOrder)
    	for n = 2 to vFilterTxt.size()
    		vDisplayData.initialize_from_table("Logging",vFilterTxt[n],vOrder,.t.)
    	next n
    	vDisplayData.resize(vDisplayData.first_empty()-1)
    I created one long filter string, then chopped it in the maximum number of conditions for each search (i.e. maximum of 1024 characters).
    --
    Support your local Search and Rescue Unit, Get Lost!

    www.westrowops.co.uk

Similar Threads

  1. Fun with queries
    By nlights in forum Application Server Version 11 - Web/Browser Applications
    Replies: 8
    Last Post: 03-13-2012, 01:17 AM
  2. Queries
    By paddlepaw in forum Application Server Version 10 - Web/Browser Applications
    Replies: 2
    Last Post: 04-15-2010, 01:14 PM
  3. queries
    By Mike Finnerty in forum Alpha Five Version 5
    Replies: 1
    Last Post: 07-18-2003, 02:07 PM
  4. Problem with Queries
    By James Moulding in forum Alpha Five Version 4
    Replies: 3
    Last Post: 07-11-2002, 06:09 AM
  5. Help with queries
    By Jamin Dunivan in forum Alpha Five Version 4
    Replies: 8
    Last Post: 04-24-2001, 06:26 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •