RE: (SQL) problems during select (QRY2283)

I would try derivations of the following.
        HEX(ANOVTO) <> x'40404040' and ANOVTO >= 2006

Hope this helps
Don Tully
www.DBiTools.com


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Wes
Sent: Thursday, March 02, 2006 12:19 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: (SQL) problems during select (QRY2283)


Have you tried

   Select *
  FROM CONTAF/FCTCTP
 WHERE ANVOTO IS NOT NULL
         and ANOVTO >= 2006


"Yivi" <nardus@xxxxxxxxx> wrote in message
news:d8e67ab40603020616k1dfa2912nb25e4c8a89599d98@xxxxxxxxxxxxxxxxx
> 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.-
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email:
MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.