Re: (SQL) problems during select (QRY2283)

["Luis Rodriguez" <luisrodriguez@xxxxxxxxxxx>]

Yivi,

What happens if you create a view where ANOVTO is a character field?

Regards,

Luis Rodriguez

> ------------------------------
> 
> message: 8
> date: Thu, 2 Mar 2006 15:16:44 +0100
> from: Yivi <nardus@xxxxxxxxx>
> subject: (SQL) problems during select (QRY2283)
> 
> Hello everyone.
> 
> Ok, I have the following problem:
> 
> FCTCTP it's  a large, very old file still maintained by very old programs
> out there in the production system.
> For a new program I'm working at I need to check against this file for
> few things before printing a suscint report.
> 
> I am writing the program in SQLRPGLE, and before I actually start coding
> the RPG part I test all the SQL code until I get the desired results.
> 
> But, when I do somthing like "SELECT * FROM CONTAF/FCTCTP WHERE ANOVTO >=
> 2006", I find that not everything is pretty.
> Theoretically, ANOVTO is a packed(4,0). But, one or two of these old
> programs that no one really knows who wrote are inserting blanks where
> 0's (or any other number) should go, and when the SQL gets to about 500000
> records it dies with the error code QRY2283. Checking the pf member I see
> that after that record having that situation is not unusual, with many
> times ANOVTO being '    ' and not 0000.
> 
> How can I get around this? I tried using DEC to 'cast' this field into a
> number each time, but the result is the same. Trying to compare against
> an empty string fails, because the SQL processor complains (and rightly so)
> that the comparison of a string against a packed numeric makes very
> little sense.
> 
> Any ideas how can I get around this problem using SQL?
> 
> I can't touch the file. Can't touch the code that's producing these
> results, at least not right now.
> 
> Any hint or idea would be very welcome!
> 
> Thanks in advance and regards,
> 
> I.-
> 
----------------
  Luis Rodriguez