× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. February 2007

RE: SQL selection problem

Thanks Rob
I don't know what I was thinking about from your previous post, or what I
had tried, but this time I was slow and methodical.
At first, I could see what you were trying to tell me, but all I was
selecting turned out to be records where fieldA was blank, then I realized
that fieldB was only 4 characters, so I changed your example accordingly,
select * from qtemp/alan
where fielda=' ' or
(right('0000' concat trim(fielda), 4)
=digits(fieldb))
and lo and behold, it works
Thanks for your perseverance




Alan Shore

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

rpg400-l-bounces+alanshore=nbty.com@xxxxxxxxxxxx wrote on 02/06/2007
09:59:06 AM:


Easy
You need to swap fieldB and fieldA.
Decimal converts alpha to numeric.
Digits converts numeric to alpha.
Therefore
... fieldB = DEC(fieldA)
becomes
... fieldA = Digits(fieldB)

You got some rule against pasting what I posted?  :-)

select * from qtemp/alan
where fielda=' ' or
(right('00000' concat trim(fielda), 5)
 =digits(fieldb))


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/06/2007 09:30 AM
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 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.


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

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.