Query/400 selection error involving field

[natefarm]

I have a query that has two "OR" sections in Select Records. It has been working fine. We added a 3rd "OR" and it gets "Selection error involving field CPCAAT". Pressing F1 shows the following:

Message . . . . : Selection error involving field CPCAAT.
Cause . . . . . : The query did not run because of a comparison operand
involving data from field CPCAAT (in record 0 of file CTLNPROP in HZWORK if
other than *N). The CPCAAT data is not necessarily incorrect, since the
operand could be an expression involving other fields and constants as well
as CPCAAT, or it could require some sort of conversion that could not be
performed successfully.

Pressing F10 from there:

>> wrkqry

   Select or omit error on field CPCAAT member CTLNPROP.
   Select or omit error on field LMTTLN member LNMAST.  
   Select or omit error on field CPMTG1 member CTLNPROP.
   Selection error involving field CPCAAT.              
   3 informational messages logged.

Here's what's in Select Records:

          LMLTYP           EQ     5    
AND    LMAPPL            EQ     'IL' 
AND    LMSTAT            NE     'C'  
AND    SRTMO             EQ     datmo
AND    SEQ180NUM      LE     720  
AND    B1CHGFICO       GE     50

OR     LMLTYP            EQ     5    
AND    LMAPPL            EQ     'IL' 
AND    LMSTAT            NE     'C'  
AND    SRTMO             EQ     datmo
AND    SEQ190NUM         LE     720  
AND    B2CHGFICO         GE     50   

OR     LMLTYP            EQ     5    
AND    LMAPPL            EQ     'IL' 
AND    LMSTAT            NE     'C'  
AND    SRTMO             EQ     datmo
AND    CURRLTV           GT     100 

The odd thing is, if I delete the 1st two selection groups, the 3rd one works fine by itself, but any combination of the 1st & 3rd, 2nd & 3rd, or all three, gets the error. I even tried reversing the order just for fun, but it didn't make any difference.

CURRLTV is a defined field:

Field Expression
CURRLTV (lmttln + cpmtg1)/cpcaat * 100

However, it was already there before, and like I said, it works fine with the 3rd OR group. I suppose there could be divide-by-zero issues, but wouldn't the error then occur with the 3rd OR by itself. Any ideas?

(This is my first post, so I hope I'm on the right forum for this question)

[as400Pro]

Re: Query/400 selection error involving field

I'm a bit rusty in IBM Query....
(With SQL and some well place brackets this could be so simplified)

What happens when you remove just this line
AND CURRLTV GT 100

[natefarm]

Re: Query/400 selection error involving field

Then it works. However, that line is the key to the purpose of adding the 3rd OR.

BTW, thanks, whoever fixed my post with the code tags. Like I said - I'm new.

[as400Pro]

Re: Query/400 selection error involving field

This is just a guess, but I think you might not be able to use result fields within a select.

In the past we would create two queries and call them from a cl.
the first dumping the result fields
the second reading the results table created from first query

does this make sense?

[natefarm]

Re: Query/400 selection error involving field

We have hundreds of queries that use results fields in the selects. And again, the ORs work by themselves. It's when the 3rd is combined with the other two that the error occurs.

[tomholden]

Re: Query/400 selection error involving field

                              Define Result Fields                              
                                                                                
 Type definitions using field names or constants and operators, press Enter.    
   Operators:  +, -, *, /, SUBSTR, ||, DATE...                                  
                                                                                
 Field       Expression                         Column Heading        Len   Dec

what is in your Len & Dec fields?

[natefarm]

Re: Query/400 selection error involving field

They're undefined, so it calculates it's own defs. Specifying them makes no difference though.

[GLS400]

Re: Query/400 selection error involving field

Hi Natefarm:

I'm going to guess that CPCAAT has invalid data not zeros..... but alpha characters.

Can you please validate that by running a qry on just that field? Invalid data will appear as all +++

Thanks
GLS

[natefarm]

Re: Query/400 selection error involving field

There are no non-numeric characters. I tried switching the query from join-type 2 to type 1, since that field comes from a secondary file, but that didn't help. I may need to create a 2nd query with the 3rd selection criteria and have it append to the db file created by the 1st one. It's no solution, but it's a work-around.

[GLS400]

Re: Query/400 selection error involving field

Try looking for invalid characters in the other 2 fields: lmttln or cpmtg1

Best of Luck
GLS

[natefarm]

Re: Query/400 selection error involving field

Those are clean numerics as well.

[DeadManWalks]

Re: Query/400 selection error involving field

Use SQL. Clean up your statement and you will see better where the issue. Query is so the 90's.

[John Andersen]

Re: Query/400 selection error involving field

Blah, don't listen to the naysayers... Query/400 lives on! :-)

Did you ever figure out what was going on? If it just will not come together and you want to stick with a query definition you could break it up into two queries, output the results from the first one to a file then run the second query over the file. It's not pretty but it works...


-John Andersen

[natefarm]

Re: Query/400 selection error involving field

Thanks John. I'm happy in the dark ages! With hundreds of queries running on a regular basis, I shudder to think of converting.

I didn't come up with a solution, but the work-around was to separate the third OR into an additional query that appends to the original database file, and it works fine. I don't know why they don't work together though.

[John Andersen]

Re: Query/400 selection error involving field

Nate--- Converting isn't _that_ bad... using RTVQMQRY against your query definitions will probably get you 80% of the way.

Believe me, I love Query/400 for many many reasons... but I wouldn't want to have my hand forced to convert if or when IBM decides to stop licensing the product. I had that happen not long ago on another project using (ugh) Progress DB.

-John Andersen