RE: SQL selection problem

This works fine on our system V5R3 interactively. the problem may be the 
part about contains only numeric data. Your selection may contain invalid 
data and SQL cannot process the DEC portion properly.




<Matt.Haas@xxxxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
02/05/2007 03:48 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
<rpg400-l@xxxxxxxxxxxx>
cc

Subject
RE: SQL selection problem






Give this a try:

select * from File where
FieldA  = ' ' or
(FieldA  <> ' ' and FieldB = DEC(FieldA))

I think you have your parenthesis in the wrong places. If that doesn't
work, you can always combine the queries with a UNION ALL.

Matt

-----Original Message-----
From: rpg400-l-bounces+matt.haas=thomson.com@xxxxxxxxxxxx
[mailto:rpg400-l-bounces+matt.haas=thomson.com@xxxxxxxxxxxx] On Behalf
Of Alan Shore
Sent: Monday, February 05, 2007 4:43 PM
To: RPG programming on the AS400 / iSeries
Subject: Re: SQL selection problem


A further note

If I try

 select * from File where
 FieldA  = ' '
There is no problem

and then I try
 select * from File where
(FieldA  <> ' ' and FieldB = DEC(FieldA))

There is no problem.
There is a problem with the combination

Again, thanks in advance


Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx



 

             Alan Shore

             <AlanShore@xxxxxx

             om>
To 
             Sent by:                  rpg400-l-bounces@xxxxxxxxxxxx,
"RPG 
             rpg400-l-bounces@         programming on the AS400 /
iSeries" 
             midrange.com              <rpg400-l@xxxxxxxxxxxx>

 
cc 
 

             02/05/2007 04:37
Subject 
             PM                        SQL selection problem

 

 

             Please respond to

              RPG programming

              on the AS400 /

                  iSeries

             <rpg400-l@midrang

                  e.com>

 

 






Hi everyone.
I have an SQL selection problem
A file contains numerous fields, including
Field A    Alpha           6
FieldB     Packed       4   0

I want to select any records that have FieldA = spaces
OR
FieldA contains numerics only and the number in FieldA = the number in
FieldB

I tried the following

 select * from File where
(FieldA  = ' ' or
(FieldA  <> ' ' and FieldB = DEC(FieldA)))

Interactively, I get the following error
Selection error involving field XFLG11.

Anyone any thoughts on how to code SQL for this situation?

Thanks in advance



Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.