Unexpected LIST results. (Reporter Specific) [Revelation On-Line Wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
[[https://www.revelation.com/|Sign up on the Revelation Software website to have access to the most current content, and to be able to ask questions and get answers from the Revelation community]]


==== Unexpected LIST results. (Reporter Specific) ====
=== At 22 APR 2008 11:19:16AM Michael Slack wrote: ===
 
{{tag>"Reporter Specific"}}
 
<QUOTE>
I'm working with AREV 3.12.  I've come across some TCL LIST reporting that I can't explain.



Our purchasing group has gone thru our Vendors table and deleted some of the vendor rows.  I then wrote a program to remove any vendor numbers (the key to the Vendors table) from our Inventory table where the row for that Vendors key doesn't exist in the Vendors table.  Within the Inventory table we have three grouped mulivalue columns.  The Vendor Number (a real data column), the Vendor Part Number (a real data column) and the Vendor Name (a symbolic using the Vendor Number to get the vendor name from the Vendors table).



The program uses the statement:

SELECT INVENTORY WITH VENDOR_NUMBER # '' AND WITH VENDOR_NAME='



The reason for the VENDOR_NUMBER # '' is that some of our Inventory rows don't have any vendors listed at all.  So this was to remove them from the active select list because they don't need to be processed.  Then the program looped thru the active select list, got an Inventory key and then its row.  Then processed the Vendor Number multivalue column from the bottom up.  If a Vendor Number was found not to have a row in the Vendors table, that Vendor Number and coorisponding Vendor Part Number were removed.  Once the row was processed and saved, on to the next row.



Afterwards, I ran the TCL statement:

LIST INVENTORY WITH VENDOR_NUMBER # '' AND WITH VENDOR_NAME=' VENDOR_NUMBER VENDOR_NAME VENDOR_PART_NUMBER

and zero rows were found.



This got thru our Testing group and to our DBA's.  The DBA loaded and ran the process in their staging environment (where they can work with the changes before loading them into our live application).  Everything seemed to go smoothly until they ran an additional TCL statement like:

LIST INVENTORY WITH VENDOR_NUMBER # '12345' VENDOR_NUMBER VENDOR_NAME VENDOR_PART_NUMBER



The DBA had noted some of the Vendor Numbers that would be removed from the Inventory table.  Then afterwards checked to make sure that those Vendor Numbers had in fact been removed from the Inventory table.  To our surprise, some of those numbers still existed in the Inventory table.  I can't duplicate this in my development area even after rebuilding the indexes on the symbolic column of Vendor_Name and adding an index to the Vendor_Number column as well.  We've even gone as far as completely removing all of the indexes from the Inventory table and redefining and rebuilding the indexes with the same results that Vendor Numbers that should be gone are not.  With the help of the DBA, we've gone into the raw data of some of the Inventory rows and verified that the Vendor Number is there.  We've verified that the Vendors table doesn't have a row for those keys.  I've even wrote a simple little program to check the result of the symbolic Vendor Name in the Inventory table, thinking that a space or some non-printable character was coming back to make the value not null but all I found was null values for the Vendor Name symbolic for the values being checked.



I haven't been able to determine why a statement with:

WITH VENDOR_NUMBER # '' AND WITH VENDOR_NAME='

will return zero rows.  While a statement with:

WITH VENDOR_NUMBER # '12345' 

will return rows.  And the Vendor Number should have been deleted but wasn't.



Can anyone point me in the right direction?



Thanks,

Michael Slack
</QUOTE>
----

=== At 22 APR 2008 05:03PM Warren Auyong wrote:  ===

<QUOTE>Thoughts:



How many digits are the vendor numbers. Long numeric strings make selects and indexes barf by using E notation.



Are all the dictionary items on the select fields and indexes consistent as to justification? That is are they all Right or Left justified? This can make index selects barf too.

</QUOTE>

----

=== At 23 APR 2008 09:59AM Dave Harmacek wrote:  ===

<QUOTE>Is the INVENTORY table the Target of a Relational Index?  You cannot delete items in the Target column.  Even with the Editor.



Have you used the Editor to examine a suspect row in INVENTORY?



Dave

</QUOTE>

----

=== At 23 APR 2008 10:22AM Michael Slack wrote:  ===

<QUOTE>Hello Dave:



  The Inventory table does not have any Relational indexes on it.  For reasons I'm not quite sure of, we don't have any Relational indexes on any of the tables within the application.



  Yes, I used the Editor to look at the columns in the selected rows.  All looked correct.  Even down to the @VM between values.  The column looked correct.  To inspect the symbolic column, I wrote a quick and dirty program to get the symbolic and load it into a variable.  Then used the DEBUG statement so I could then inspect the variable.  Again, everything looked right as far as it went.



Thanks,

Michael Slack

</QUOTE>

----

=== At 23 APR 2008 11:51AM Michael Slack wrote:  ===

<QUOTE>Hello Warren:



  In spot checking several 100 Inventory rows, the multivalue Vendor Number column in the Inventory table, the data are 5 digit numbers.  I only found a few that were 6 digit numbers.  I didn't find any that had alpha characters in them.  In looking at the AREV manuals, I didn't see anything that suggested at what point a long string of digits is treated as a scientific notation by the system.  In checking the dictionary for the Vendor Number, there isn't anything converting it into or out of scientific notation.



  Both of the dictionary listings for the real data column of Vendor Number and the symbolic of Vendor Name are set to Left justification.  Vendor Number has a display length of 6 characters.  The Vendor Name column has a display length of 25 characters.  Should I increase the display length for the Vendor Number column since there are some 6 digit values in the column?  If I remember correctly, that as a general rule the display length on a column should be a bit wider than the widest actual value on a column that is indexed.  Or is that only for the key column?  I didn't think to check what the longest string is for the Vendor Name.



Thanks,

Michael Slack

</QUOTE>

----

=== At 24 APR 2008 02:48AM Simon G Wilmot wrote:  ===

<QUOTE>Any MFSes on the table that might cause as issue ??



Simon

</QUOTE>

----

=== At 24 APR 2008 03:56AM Richard Hunt wrote:  ===

<QUOTE>It is possible that the symbolic "VENDOR NAME" is causing the problem. What is the code for the symbolic?

</QUOTE>

----

=== At 24 APR 2008 11:01AM Michael Slack wrote:  ===

<QUOTE>Hello Richard:



  Here is the code for the Invnetory VENDOR_NAME symbolic:

IF @MV THEN

  X=XLATE('VENDORS',@RECORD,2,'X') ;*VENDOR_NO

END ELSE

  X=XLATE('VENDORS',@RECORD,2,'X')     ;*VENDOR_NO

END

@ANS=X



  Since my LIST/SELECT statements have all been from TCL, the ELSE section of the IF statement would be used.  The IF THEN part would only be used if the symbolic is being called from a multivalue prompt on a window.



  I had thought that the symbolic might be the problem.  Specifically that there might be a space character or an unprintable character in the vendor name that should otherwise be null.  Thus making the vendor name not null.  So I wrote a quick and dirty little program to read one of the Inventory rows that has a Vendor Number that should have been deleted.  From the Inventory row, I filled a variable with the symbolic output.  Then in the program had a DEBUG statement so I could then look at the variable filled by the symbolic.  I couldn't find anything wrong with the data.



Thanks,

Michael Slack

</QUOTE>

----

=== At 24 APR 2008 11:22AM Richard Hunt wrote:  ===

<QUOTE>I think, and I'm not too sure, maybe try this...



RESULT=XLATE('VENDORS',@RECORD,2,'X')

@ANS=RESULT



"XLATE" automatically handles multivalues correctly. I think that this might work. I use this method and similar items works for me.

</QUOTE>

----

=== At 24 APR 2008 12:18PM Michael Slack wrote:  ===

<QUOTE>Hello Richard:



  I'll have to give your suggestion a try.  But I tend to use the "IF @MV THEN" for when the symbolic is used within a window and I only need the one value for the current line item and to display that one value on the window.



  But now that you mention it, I should do a little experimentation to see how your code works within a window.



Thanks,

Michael Slack

</QUOTE>

----

=== At 24 APR 2008 12:28PM Michael Slack wrote:  ===

<QUOTE>Hello Simon:



  I wouldn't have thought of that.  We've been using the MFS's on that table for years without any apparent problems.  Plus they haven't been modified in a very long time.



  What we did find was that the SI.MFS was listed first.  I've written our DBA suggesting that the SI.MFS be moved to last in the list.  The indexes rebuilt just to be safe.  Run the SELECT statement used by the program.  If it finds any rows then that was most likely the problem and to then rerun the program to remove the Vendor Numbers.  Then do his additional checks on specific Vendor Numbers to see if the problem has been corrected or not.



  I'm going to have to wait until Monday at the earliest as the DBA has other things going on.



Thanks,

Michael Slack

</QUOTE>


[[https://www.revelation.com/revweb/oecgi4p.php/O4W_HANDOFF?DESTN=O4W_RUN_FORM&INQID=NONWORKS_READ&SUMMARY=1&KEY=E3CC7033582946798525743300542986|View this thread on the  forum...]]