Re: Need to create a view over a physical file - but I don't want to - but I have to

Thanks for your reply Luis
I sent a later e-mail where I had mentioned what I had done wrong



Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill



Luis Rodriguez
<luisro58@xxxxxxx
om> To
Sent by: Midrange Systems Technical
midrange-l-bounce Discussion
s@xxxxxxxxxxxx <midrange-l@xxxxxxxxxxxx>
cc

05/07/2010 04:21 Subject
PM Re: Need to create a view over a
physical file - but I don't want to
- but I have to
Please respond to
Midrange Systems
Technical
Discussion
<midrange-l@midra
nge.com>






Alan,

No sure but, you have a field defined as numeric (7,2) with a value of .01
and, after that, you divide by 100. As I see, you are "running out" of
decimals. Have you tried your sentence without the /100 part?
Luis Rodriguez
IBM Certified Systems Expert â eServer i5 iSeries


On Fri, May 7, 2010 at 3:05 PM, Alan Shore <AlanShore@xxxxxxxx> wrote:

Thanks for both of your replies Chuck
I am saving ALL of these e-mails into a separate folder, to use as
ammunition when I present my case for the length of time that this

"little"

project will take me

However, I have come across something that is even more puzzling
Hopefully this is not where I cry UNCLE

here is the sql statement that i Have just used

SELECT
hex(substr(CFRE1A, 1, 3)),
decimal(substr(hex(substr(CFRE1A, 1, 3)), 1, 5) , 7, 2) / 100,
decimal(substr(hex(substr(CFRE1A, 1, 3)), 1, 5) / 100, 7, 2) *
Case when substr(hex(substr(CFRE1A, 1, 3)), 6, 1) = 'F' then 1
else -1 end as Lowerlimit,
CFRE1A FROM catlogp WHERE substr(CFRE1A, 63, 1) = 'P'

and here are a couple of results
HEX Numeric Expression LOWERLIMIT

NEW

FREE
00001F .01000000000000000000000000 .00
*
00001F .01000000000000000000000000 .00
*
02500F 25.00000000000000000000000000 25.00

*&

As you can see, the 3rd line results in the LOWERLIMIT being 25.00 - no
problem
but the first 2 lines end up as being .00. I expected to see .01.
Does this make sense to anyone?
What am I doing wrong?


Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill




CRPence
<CRPbottle@yahoo.
com> To
Sent by: midrange-l@xxxxxxxxxxxx
midrange-l-bounce cc
s@xxxxxxxxxxxx
Subject
Re: Need to create a view over a
05/07/2010 03:17 physical file - but I don't want
PM to - but I have to


Please respond to
Midrange Systems
Technical
Discussion
<midrange-l@midra
nge.com>






The data will be made accessible as columns with the noted data
types via the VIEW. The column definitions as expressions defined
over the physical data, and those expressions deriving these typed
columns encapsulated in a VIEW, make the typed data available
directly to all /reporting services/ that support query of a SQL VIEW.

The real issue is not for /reporting/ the data, but for update
and insert of rows, for which INSTEAD OF triggers would probably
become a requirement to make such a VIEW more usable.

Regards, Chuck

Aaron Abreu wrote:

Consider the future use of the data, do you want to be locked
into decimal subtr options for the life of this data... If you
may need to access this data easily from crystal, excel, iseries
nav, or other reporting services... I VOTE for redesign in this
case.. A couple of days rebuilding a database could save you
days and headaches down the road... can't say much on the RPG
solution, been too many years now, but I do use the sql
substr/decimal options a lot for data imported from outside
organizations, and it does work just fine.

Alan Shore on 5/7/2010 12:02 PM wrote:

Sorry about the subject description - but I couldn't think of
what to put in there that would convey the predicament that I
face

I need to create a view over a physical that contains the
following fields (believe me - NOT my design)<<SNIP>>

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

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