Showed this to some else who was walking past
He pointed out my error
Couldn't see the wood for the trees
SELECT
hex(substr(CFRE1A, 1, 3)),
decimal(substr(hex(substr(CFRE1A, 1, 3)), 1, 5) , 7, 2) / 100,
decimal(
decimal(substr(hex(substr(CFRE1A, 1, 3)), 1, 5),7,0) / 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'
Gives me the following results
HEX Numeric Expression LOWERLIMIT NEW
FREE
00001F .01000000000000000000000000 .01 *
00001F .01000000000000000000000000 .01 *
02500F 25.00000000000000000000000000 25.00 *&
which is correct
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
Alan Shore
<AlanShore@xxxxxx
om> To
Sent by: Midrange Systems Technical
midrange-l-bounce Discussion
s@xxxxxxxxxxxx <midrange-l@xxxxxxxxxxxx>
cc
midrange-l@xxxxxxxxxxxx,
05/07/2010 03:43 midrange-l-bounces@xxxxxxxxxxxx
PM Subject
Re: Need to create a view over a
physical file - but I don't want
Please respond to to - but I have to
Midrange Systems
Technical
Discussion
<midrange-l@midra
nge.com>
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.
midrange.com
