Get a Jumpstart with our Sample Apps
Results 1 to 14 of 14

Thread: Index_create.....

  1. #1
    Member
    Real Name
    Ernie Storms
    Join Date
    Mar 2008
    Location
    Wimberley, Texas
    Posts
    566

    Default Index_create.....

    Doing a join operation to create a temp table. Of course, I guess, this gets rid of any indexes you create for the table when it overwrites it. So am needing to create the index in xbasic. Could someone point out my error in the following code?
    Code:
    dim tbl as p
    dim tbl2 as p
    tbl = table.open("tmp_available")
    tbl2 = table.open("tmp_usage")
    tbl2.index_create_begin("Use_Date","Room_Id"+""+"Use_Date","U")
    indx = tbl2.index_create_end("tmp_usage") << get "Use Index_create_begin first" error in debug
    tbl2.index_primary_put("Use_Date")
    tbl.batch_begin()
    tbl.fetch_first()
    while .not. tbl.fetch_eof()
    	rec = tbl2.fetch_find(tbl.Room_id+""+tbl.Use_date)
    	if rec > 0 then
    Thanks.
    Ernie

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

    Default Re: Index_create.....

    Hi Ernie,
    In the Index builder the string returns as fieldname+" "+fieldname so I am wondering if you have too many quotes when passing this as an expression to the function. Have you tried "fieldname+" "+fieldname" in the tbl2.index_create_begin() statement? Also if your script is erring it is leaving the table open; you might want to toss in a goto label to get the table closed - just in case.
    Robin

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

  3. #3
    Member
    Real Name
    Ernie Storms
    Join Date
    Mar 2008
    Location
    Wimberley, Texas
    Posts
    566

    Default Re: Index_create.....

    Robin,
    Thanks for the response. Here's what I'm getting now with this setup.
    Code:
    dim tbl as p
    dim tbl2 as p
    dim indx as p
    tbl = table.open("tmp_available")
    tbl2 = table.open("tmp_usage",FILE_RW_EXCLUSIVE)
    tbl2.index_create_begin("Use_Date",s_quote(tmp_usage->Room_Id)+""+dtoc(tmp_usage->use_date),"U")
    indx = tbl2.index_create_end()   << get the first errror here
    tbl2.index_primary_put("Use_Date")
    hen
    Tried yours and get the second attached error.
    Code:
    tbl2.index_create_begin("Use_Date","Room_Id+""+Use_Date","U")
    indx = tbl2.index_create_end()
    Haven't been able to find anything in the help other than the basic methods explained, which isn't enough detail for my limited knowledge.
    Here's what I'm doing. Trying to build a table of availabilities.
    1. I do a join between two tables to get the tmp_usage table.
    2. Run a script that reads thru a rooms table and adds every room for each day requested from a date range passed in. Results table is tmp_available.
    3. Now I'm trying to read thru this tmp_available table and with each record see if there is a matching record, based on the room_id + use_date fields. If so, update a couple of fields in the tmp_available table.

    I may just try my own join so I can add the info to the tmp_usage table without wiping out the index. Then I won't have to worry about this problem. But would be nice to find out what I'm doing wrong here.
    Ernie
    Appreciate your help.
    Ernie

  4. #4
    Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,382

    Default Re: Index_create.....

    [quote=enstorms;457853]Tried yours and get the second attached error.
    Code:
    tbl2.index_create_begin("Use_Date","Room_Id+""+Use_Date","U")
    indx = tbl2.index_create_end()
    [/qoute]

    Code:
    <TBL>.INDEX_CREATE_BEGIN( Tag_Name as C,   Index_Expr as C [, Filter_Expr as C   [, Index_Type as C ]]   )
    The function has 4 arguments. You can't skip argument 3 - filter.
    That's why you got the logical error. A filter must evaluate to a logical.
    Use:
    Code:
     tbl2.index_create_begin("Use_Date","Room_Id+Use_Date","","U")
    Also your second argument isn't correct. Try what I've listed, but I don't have your table to try it with your field definitions. So use the index builder for the table to make the index and copy the order from it and paste that into the order expression of the function.
    Last edited by Al Buchholz; 05-11-2008 at 02:29 PM.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  5. #5
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    24,696

    Default Re: Index_create.....

    Code:
    tbl2.index_create_begin("Use_Date","Room_Id"+""+"Use_Date","U")
    The method .index_create_begin() allows you to have four parameters with the third and fourth being optional. If you choose to use the fourth (index type) you must tell the method that you are using it and skipping the third (filter).

    Code:
    tbl2.index_create_begin("Use_Date","Room_Id"+""+"Use_Date","","U")

    Must have been typing at the same time as Al.

  6. #6
    Member
    Real Name
    Ernie Storms
    Join Date
    Mar 2008
    Location
    Wimberley, Texas
    Posts
    566

    Default Re: Index_create.....

    Thanks, guys. Appreciate the help. That makes sense. Was wondering about skipping the 3rd argument without putting anything in there. Still trying to get a handle on how these expressions work with quotes.
    Ernie

  7. #7
    "Certified" Alphaholic
    Real Name
    Cal Locklin
    Join Date
    Mar 2000
    Location
    S.E. Michigan
    Posts
    5,752

    Default Re: Index_create.....

    Quote Originally Posted by enstorms View Post
    Still trying to get a handle on how these expressions work with quotes.
    Ernie
    We could help you with this one but we would need to know what you are really trying to accomplish. Is this just an index on two fields? Why is there a + "" + between them which appears to do nothing?

    Figuring out how to set up quotes is something that seems to bother a lot of people but I found it quite logical once I broke it down into separate "pieces". Here's something I actually wrote for someone else a year or two ago. Maybe it will help:

    Building complex filter expressions in xbasic that involve quotes is one of the toughest things to get your head around but you'll be able to build really complex expressions with no problem once you do. The first "method" will be primarily for explanatory purposes - so you can start to understand the logic - then I'll show you how I do it in actual practice.

    Start by building an example expression of a specific filter. I suggest you do this in the expression builder initially. The expression builder helps you get the initial filter right. Start with something simple like: (Note that my field names are always 10 characters or less and end with "f".)
    Veh_Manf="Ford" .and. Veh_Modelf="Taurus"

    Copy this filter to the appropriate place in your xbasic script.

    Now we need to put quotes around the whole thing because the basic construction is Query.filter = "filter string" HOWEVER, the problem is that there are already quotes inside the string so first we'll 'escape' those quotes with backslashes:
    Veh_Manf=\"Ford\" .and. Veh_Modelf=\"Taurus\"

    then add the quotes around the outside:
    "Veh_Manf=\"Ford\" .and. Veh_Modelf=\"Taurus\"

    We now have a filter string that we can pass to the query.filter variable but it won't be very generic until we replace the actual values with variables. That means we'll have to break this character string into multiple strings with variables between each string section. Let's start by just breaking it into string sections where the actual values being searched for (Ford and Taurus) are separate strings. Remember that every string (or string section in this case) must be enclosed in quotes to identify it as a string. If it wasn't enclosed in quotes, the program would look for a field by that name. The result is:
    "Veh_Manf=\"" + "Ford" + "\" .and. Veh_Modelf=\"" + "Taurus" + "\""

    Note that we have basically just added " + " at various locations to break it up into multiple strings. Maybe it will help to show that we could actually do this but it wouldn't make much sense:
    "Veh_M" + "anf=\"" + "F" + "o" + "r" + "d" + "\" .and. " + "Veh_Modelf=\"" + "Tau" + "rus" + "\""
    (Yes, it appears messy and you may have to look at that a bit but it makes the point that this is just a series of string values at this point.)

    Now we simply replace the values being searched for with the appropriate variable name:
    "Veh_Manf=\"" + Man_chc + "\" .and. Veh_Modelf=\"" + Model_chc + "\""

    The prefix "var->" is optional in this case because the script has no confusion. It's not enclosed in the quotes, it isn't a function call, and isn't a control command like IF, WHILE, =, >, etc., and it's in a script so it must be a variable. Using "var->" is perfectly acceptable but not necessary. As far as I'm concerned, using var-> in a script is just extra typing:
    "Veh_Manf=\"" + var->Man_chc + "\" .and. Veh_Modelf=\"" + var->Model_chc + "\""

    Note that, in this case, we are not passing the variable name to the layout
    - we are only using the variable to build the filter string. By the time the string is sent to the layout, the string will have the actual field values in it - in my example that will be "Ford" and "Taurus". If a variable is passed to the layout, it must be a GLOBAL variable. (SHARED also works in some cases but GLOBAL always works.)

    Beware when using variables in filters. A filter expression passed to a layout operates under a different assumption than an expression in a script. A script which contains an "unidentified name" assumes that name is a variable. If a filter expression containing an "unidentified name" is passed to a layout, the layout assumes the unidentified name must be a field name. This is a common error - a global variable is used in a filter expression but the developer forgets to add "var->" in front of it to identify it as a variable and the layout reports a "field not found" error.

    Personally, I almost always use the above method for building strings with variables. But that's probably because that's the way I was forced to learn it - there was no other choice at the time. It's also the most universal method - you can't count on every programming language having something like an S_Quote() function. Since the toughest part is to come up with the correct quotes and people seem to have a hard time figuring out where to use the backslashes, Alpha came up with the S_Quote() function.

    Now that I've gone through all that, the way I actually do it now that I understand the requirements is from left to right. (Unless it is a really complex expression.) I start with the first part of the string and look for where the first variable will be - the first part of the string ends where the first variable starts. If the first variable will be a character value, then the string has to include a quote for the variable, if it's a date value it has to include the {, and nothing is required if it's a numeric value:
    "Veh_Manf=\""
    "Veh_blddtf={"
    "Veh_widthf="

    Now add the variable. REMEMBER THAT WE ARE CREATING A STRING. If it's a date variable, convert it to a character string. If it's a numeric value, convert that to a character string also. Even a logical value will need to be converted to a string. Remember that the final result must be one long string:
    "Veh_Manf=\"" + man_chc
    "Veh_blddtf={" + dtoc(date_chc)
    "Veh_widthf=" + str(width_input,10,2)

    Now continue with the next part of the string up to the next place you want to use a variable. Remember to start with the closing 'operator' ( \" or } ) as appropriate:
    "Veh_Manf=\"" + Man_chc + "\" .and. Veh_Modelf=\""

    Now add the next variable:
    "Veh_Manf=\"" + Man_chc + "\" .and. Veh_Modelf=\"" + Model_chc

    And, finally, the last string which in this case is nothing but an embedded closing quote which is just an escaped quote within quotes "\"":
    "Veh_Manf=\"" + Man_chc + "\" .and. Veh_Modelf=\"" + Model_chc+ "\""

    When you are certain that there will be no single quotes and no apostrophes in the string values, another option is to use single quotes inside the double quotes. I find this method even easier and it's easy to read in the code editor. (Unfortunately, it's not easy to read in most word processors so I'll do the best I can.)
    "Veh_Manf='" + Man_chc + "' .and. Veh_Modelf='" + Model_chc + "'"

    Assuming Man_chc = "Ford" and Model_chc = "Taurus", the result passed to the report or other layout is the expression we originally started with:
    Veh_Manf="Ford" .and. Veh_Modelf="Taurus"
    or, using single quotes:
    Veh_Manf='Ford' .and. Veh_Modelf='Taurus'
    You can now see why it's important to make sure there are no commas or other single quotes in the filter string if you chose so use single quotes when building your filter expression.

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

    Default Re: Index_create.....

    Cal,
    Which only goes to show how hard this really is to grasp! I always thought that enclosing the variables as "+var+" was some syntax thing that parsed the variables themselves. And when used in a filter, necessitated adding the single quotes to wrap it. I think I see the difference in what I am supposed to be accomplishing.

    I think therefore I am (not an idiot...?) :D
    Robin

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

  9. #9
    "Certified" Alphaholic
    Real Name
    Cal Locklin
    Join Date
    Mar 2000
    Location
    S.E. Michigan
    Posts
    5,752

    Default Re: Index_create.....

    Quote Originally Posted by MoGrace View Post
    Cal,
    Which only goes to show how hard this really is to grasp! I always thought that enclosing the variables as "+var+" was some syntax thing that parsed the variables themselves. And when used in a filter, necessitated adding the single quotes to wrap it. I think I see the difference in what I am supposed to be accomplishing.

    I think therefore I am (not an idiot...?) :D
    Hard to grasp initially - yes. But once you realize that it's a series of strings with an occasional variable thrown in, then beat your way through 2-3 'conversions', it quickly becomes much easier. At least, it did for me. Then after 10 or so it starts becoming truly easy. However, if you stop after 1 because that one was difficult, you will never learn.

  10. #10
    Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,382

    Default Re: Index_create.....

    Robin

    You have a great memory. I think you may have been thinking of replace_parameters().

    That's a good option for building a filter into a character string, and then using the string as an argument in a function
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

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

    Default Re: Index_create.....

    Quote Originally Posted by Al Buchholz View Post
    Robin

    You have a great memory. I think you may have been thinking of replace_parameters().

    That's a good option for building a filter into a character string, and then using the string as an argument in a function
    Thanks for the help link. Look at the 2 examples given:

    Example (Local variables)
    Run a report where the filter is based on parameters.
    dim whatLastname as C
    dim whatDate as D
    whatLastname = "Smith"
    whatDate = {12/18/1967}
    Filter = "lastname = [varC->whatLastname] .and. " + \"date_of_birth > = [varD->whatDate]"
    filter = replace_parameters(filter, local_variables() )
    report.print("customer",filter)

    Note : If the "whatLastname" and "whatDate" variables are DIMmed as SHARED variables, then this example can be considerably simplified because you can refer the variables directly in the filter expression, and you therefore do not need to use the REPLACE_PARAMETERS() function:

    Use shared variables.
    dim SHARED whatLastname as C
    dim SHARED whatDate as D
    whatLastname = "Smith"
    whatDate = {12/18/1967}
    filter = "lastname = var->whatLastname .and. date_of_birth = var->whatDate"
    report.print("customer", filter)
    Now when the genie creates these codes, how is one to know the difference in the syntax being used? Note especially the way the quotes are used to create the string in the first example and not used in the 2nd - the part I put in red. Since I have never used replace_parameters outside of a genie created script, I would never have found this info! Lucky for me I use mostly shared or global variables when writing filter expressions (like the 2nd example). Usually because they never work otherwise!

    Also in the 1st example, why does the 2nd " not need to be escaped with the \ too? I would have thought it should be written like this when .and. is used - including parentheses:

    Filter = "(lastname = [varC->whatLastname])+\" .and. \"+(date_of_birth > = [varD->whatDate])"

    or perhaps like this using single quotes:

    Filter = "(lastname = [varC->whatLastname])+' .and. '+(date_of_birth > = [varD->whatDate])"
    Last edited by MoGrace; 05-13-2008 at 12:56 PM.
    Robin

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

  12. #12
    Member
    Real Name
    Ernie Storms
    Join Date
    Mar 2008
    Location
    Wimberley, Texas
    Posts
    566

    Default Re: Index_create.....

    Thanks so much for all the information. Cal, I have printed out your post and will study it and, I'm sure, refer to it often. Just one question so far.
    then add the quotes around the outside:
    "Veh_Manf=\"Ford\" .and. Veh_Modelf=\"Taurus\"
    Should there be an extra " at the end?
    then add the quotes around the outside:
    "Veh_Manf=\"Ford\" .and. Veh_Modelf=\"Taurus\""
    Ernie

  13. #13
    "Certified" Alphaholic
    Real Name
    Cal Locklin
    Join Date
    Mar 2000
    Location
    S.E. Michigan
    Posts
    5,752

    Default Re: Index_create.....

    Quote Originally Posted by enstorms View Post
    Should there be an extra " at the end?
    Yes, nice catch - thanks! Don't know how I missed that one.

  14. #14
    "Certified" Alphaholic CharlesParker's Avatar
    Real Name
    Charles Parker
    Join Date
    Dec 2012
    Location
    New Orleans, LA
    Posts
    1,674

    Default Re: Index_create.....

    However, if you stop after 1 because that one was difficult, you will never learn.
    Words to live by!

Similar Threads

  1. index_create
    By Schulzie in forum Alpha Five Version 4
    Replies: 21
    Last Post: 10-12-2000, 09:56 AM

Posting Permissions

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