RE: SQL selection problem

Thanks for your reply Rob, but changing DEC to DIGITS resulted in the same
error
Selection error involving field XFLG11.
In fact when I try
 select * from File where
(FieldA  <> ' ' and FieldB = DEC(FieldA))
There is no problem.
however when I try
 select * from File where
(FieldA  <> ' ' and FieldB = DIGITS(FieldA))
There IS a problem.
HMMMMMM


Alan Shore

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


rpg400-l-bounces@xxxxxxxxxxxx wrote on 02/05/2007 05:17:47 PM:

Try again.  Use DIGITS instead of DEC in case data is corrupted like the
following.

select * from qtemp/alan
....+....1....+
FIELDA  FIELDB
12345   12,345
             1
yhdfd        2
45678        9
   26       26
  030       30
250        250
********  End o

select * from qtemp/alan
where fielda=' ' or
(right('00000' concat trim(fielda), 5)
 =digits(fieldb))
....+....1....+
FIELDA  FIELDB
12345   12,345
             1
   26       26
  030       30
250        250
********  End of data

You can add this if you like
order by right('00000' concat trim(fielda), 5), fieldb
// add fieldb in case two blank numbers with different digits.
to get
....+....1....+
FIELDA  FIELDB
             1
   26       26
  030       30
250        250
12345   12,345
********  End of data

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





Alan Shore <AlanShore@xxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
02/05/2007 04:55 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
cc

Fax to

Subject
RE: SQL selection problem






Thanks Matt - I had already tried syntax with the same end result
This small and simple(?) selection is part of a much larger selection and

I
have determined that this simple(?) selection is what is causing the
problem



Alan Shore

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

rpg400-l-bounces@xxxxxxxxxxxx wrote on 02/05/2007 04:48:31 PM:

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.


--
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.

--
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.


--
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.