›
Welcome to the SRP Forum! Please refer to the SRP Forum FAQ post if you have any questions regarding how the forum works.

Problem with a complex select statement using RList

jku@amstlc.comjku@amstlc.com
in OpenInsight
Has anyone had problems in v. 9 using a complex select statement with RList? I did this all the time in 8.3 and never had a problem, but in v. 9, the following select statement:
SelStatement = "SELECT TICKETS WITH PAYMENT_RECEIVED # 1 AND WITH DELIVERY_DATE BETWEEN '":Starting_Date
SelStatement:= "' AND '":Ending_date:"' AND WITH LAST_STATUS # 'Voided' "

run with RList(SelStatement, 5, "", "")

does not limit by the delivery date but selects all records in the table. Note that the Delivery_Date field has a Btree Index on it.

Comments

  • DonBakkeDonBakke
    Complex Select statements are known to have issues in all versions of OI. In general, however, the newer versions of OI have improved the situation rather than made it worse.

    Before we go running down a false bunny trail, can you confirm that a simple Select statement based on the DELIVERY_DATE column works? In other words, do we have confirmation that this index is working properly on its own?
  • jku@amstlc.comjku@amstlc.com
    No, it doesn't, even after I rebuilt the index on the Delivery_Date row. Is this likely a conversion issue?
  • DonBakkeDonBakke
    Most of the time when in index was working in OI 8 but is not in OI 9 it's due to the IDX_SETS configuration. What do you see in field 1 of your SYSENV\CFG_IDX_SETS record?

    Also, I seem to recall from another correspondence that this might be a new workstation (or a new hard drive)? Did you run the client setup after getting the new hardware?
  • jku@amstlc.comjku@amstlc.com
    Field 1 in the SYSENV\CFG_IDX_SETS record is set to IDXSETS2. You're correct, I did get a new hard drive but didn't realize I needed to rerun the client setup. I'll do that and see if it makes any difference.
  • DonBakkeDonBakke
    Yes. OI 9 introduced a .NET based index manager. IDX_SETS2 in your config record tells OI to use this new index manager. Installing the client setup will resolve this.
  • jku@amstlc.comjku@amstlc.com
    OK. I installed the client setup and reran the simple select process, but it is still selecting all of the records in the TICKET file, not limiting by the delivery date.
  • DonBakkeDonBakke
    It was good to get that out of the way regardless as it would have likely been another reason for a failed select. Now we can confidently look at more traditional causes for index problems.

    I know you said you rebuilt the index, but how is your dictionary configured? What Conversion pattern are you using? What is the length?
  • jku@amstlc.comjku@amstlc.com
    The dictionary record has a data type of 'DATE', length 11 (the default value) and currently, I had just left the default values ( (D) for input and D for output) for the conversions.
  • DonBakkeDonBakke
    Those settings should work. I forgot to ask, right justified?
  • jku@amstlc.comjku@amstlc.com
    Yes. Just out of curiosity, I changed the Oconv format to D4-, which is the form I'm using in the select statement, but that didn't seem to make any difference either.
  • DonBakkeDonBakke
    Hmmm...in my experience when we get this far and the indexes still are not working it likely means 1) something obvious was overlooked or missed, or 2) something more arcane, like delimiters in Key IDs, is corrupting the index. Something else that is necessary for properly functioning indexes is that all Key ID dictionary columns should be defined as having a length equal to one character more than the longest Key ID.

    Beyond this, it would be hard to navigate you any further. Others might have different ideas but that's where I'm at with your problem.
  • jku@amstlc.comjku@amstlc.com
    I have looked at this until I'm basically cross-eyed and tried as many variations as I could think of to see if either of the two conditions above might exist. I even looked at the old AREV documentation to see if I could spot something. Just one more question before I do something totally different: When a SELECT is executed on a Date field with any delimiter (e.g. LT, GT, EQ, BETWEEN, etc.) is the system comparing to the value of the raw data in the row or the data as converted by the output conversion specified in the dictionary? I always thought it was the latter. Additionally (I know, that's two questions...), if the data is being compared to the converted value (e.g. "D4-"), is it correct to enclose the comparison value in quotation marks in the SELECT statement? (I've always done it that way.)
  • DonBakkeDonBakke
    You correct enough on both points. At some level OI must convert the data to an internal format so that it can perform proper comparisons (especially those like LT, GT, BETWEEN), but the expectation is that you are using converted data in your select and that the converted data should be enclosed in quotes. In fact, if your converted data is not strictly numerical, you'll get an error without those quotes.
Sign In or Register to comment.