Rocket U2 | UniVerse & UniData

 View Only

 SELECT command returning incorrect results.

Raymond Ruest's profile image
Raymond Ruest posted 05-16-2024 19:47

We have a very recent issue I cannot explain.  This just started and will cause us some major headaches.  Here is the example:

SELECT ITMMST WITH F1 = "64"

F1 is defined as A20, not numeric.  I am getting 3 results:

64, 000064, and 0064.  

I can see how a numeric search would give those results, but I am searching a string and need only the exact match in the results.  Any idea why this would start for all users, in all databases... all of a sudden?

Jonathan Smith's profile image
ROCKETEER Jonathan Smith

Is this UniVerse or UniData ?

Raymond Ruest's profile image
Raymond Ruest

This is in Unidata.

Greg Livingston's profile image
Greg Livingston

Sounds weird that something would suddenly change behavior.  Is there a possibility that you have a F1 dictionary defined in the file ITMMST and in the VOC/MD ?  If so, maybe a new dictionary was created which is now being used in the retrieve.

Jonathan Smith's profile image
ROCKETEER Jonathan Smith

When you say F1 is defined as A20, where is it defined as A20. A UniData dictionary item would normally be 20L , 20R or 20T in attribute 5 of the dictonary item. I am presuming that whatever you using to define the field as A20 is generating a dictionary with 20L in attibutue 5.

UniData also has a rule of precedence on which dictionary item will be used if mutiple F1 exists on where UniData will check.

CT DICT ITMMST
F1:
D
1
 
F1
20L
S

LIST ITMMST WITH F1 = "64"

ID1        64

Now if you change the F1 dictionary to be right justified

F1:
D
1
 
F1
20R
S
You will now get all 3 results
LIST ITMMST WITH F1 = "64" F1
ITMMST.... F1..................
 
ID2                      000064
ID3                        0064
ID1                          64              
So first of all check attribute five of F1 to see if its marked as right justifed in the dictionary of the ITMMST dictionary.
Also check to see if you have F1 record in the VOC.
There is also a file called DICT.DICT in the sys account of $UDTHOME which contains the default F1 and should be 20L.                  
The precedence the query language uses to find which dictionary to use is.
1. The dictionary of the file
2. The VOC file
3. The DICT.DICT file
So in the problem account , find out which F1 you are using and check attribute 5.
To complicate this further, there are also some UDT.OPTIONS that can effect the result, see UDT.OPTIONS 22 and see if UDT.OPTIONS 22 is turned on.
Jonathan Smith's profile image
ROCKETEER Jonathan Smith

A small note for everyone else, this difference between left and right justfied dictionaries can also have an impact when creating indexes which itself can then lead to unexpected results in a query.

Raymond Ruest's profile image
Raymond Ruest

UST OPTIONS 22 was set to OFF!  Problem solved in my current session by turning it ON.  I'm not sure where this was set permanently and why it changed... but I guess that the next diagnotic path!

Jonathan Smith's profile image
ROCKETEER Jonathan Smith

@Raymond Ruest    

..   Although changing UDT.OPTIONS 22 corrected the problem, it does indicate to me you may been using a different dictionary to the one you thought. Did you check the dictionary F1 in the files I suggested, as you may have been using F1 from DICT.DICT based on your description. What were in each dictionary of F1 for

a) the dict of ITMMST

b)  the VOC file

c) the DICT.DICT file

Raymond Ruest's profile image
Raymond Ruest

Please excuse me if I get lost... this is an old legacy system and I'm treading into unknown areas.  :)

VOC entry for ITMMST:

     ITMMST                                         
     F                                              
     ITMMST                                         
     ..\..\..\FLOPROGS\LIVE\LIVE.IIBASE8\D_ITMMST

DICT entry for F1

001 DItem Number                                                                
002 1                                                                           
003                                                                             
004 Item Number                                                                 
005 20L                                                                         
006 S       

I am uncertain how to investigate further.

Grant Boice's profile image
Grant Boice

Raymond,

Looks like you are working on either Epicor's Avante or Infoflo.    Looking at the previous posts, you might want to check the actual data records in ITMMST<1> for possible control characters or anything that looks "out of place".

Also, there could be 3 legitimate records in the file.   Check the key of the records and see if you have 3 unique CPN's.

Just sharing my $0.00000000000000002 worth...

Grant W. Boice, Jr.

Principal Software Developer

Matheson Trig-Gas, Inc.

3 Mountainview Road

Warren, NJ  07059