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

Alan,

That article should do the trick.

The only other thing I can offer is regarding CPNAMT and PCTAMT,
you've got two choices.

Include a single field in your view, or include both fields via a CASE
statement. I'd recommend the later. In which case one of the two
would be zero and the other presumably would be non-zero.

HTH,
Charles

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

Since I sent this e-mail (and if anyone is interested) I found the
following article on the web
'Accessing Packed Data in "Flat" Files' by Kevin Forsythe
http://www.mcpressonline.com/tips-techniques/sql/techtip-accessing-packed-data-in-flat-files.html
that looks promising



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-l@xxxxxxxxxxxx
            midrange-l-bounce                                          cc
            s@xxxxxxxxxxxx
                                                                  Subject
                                      Need to create a view over a
            05/07/2010 01:21          physical file - but I don't want to
            PM                        -        but I have to


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







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)

Name                             Type                            Length
FIELD01                  A                               63
FIELD02                  A                               63
FIELD03                  A                               63
FIELD04                  A                               63
FIELD05                  A                               63

Now - within the programs, each of these fields are moved to a separate
datastructure that is defined like this

D FIELDNBR             DS                  OCCURS(5)
D  CFREE                                 1     63
D  LLMT                                  1      3P 2
D  LLMTX                                 1      3
D  WFR                                   4     63
D                                                                DIM(5)
D  QTY1                                  4      5P 0
D  ITM#1                                 6     15
D  CPNAMT                                16     20  2
D  PCTAMT                                16     17  0
D  QTY2                                  16     17P 0
D  ITM#2                                 18     27
D  QTY3                                  28     29P 0
D  ITM#3                                 30     39
D  QTY4                                  40     41P 0
D  ITM#4                                 42     51
D  QTY5                                  52     53P 0
D  ITM#5                                 54     63
D  TYPE                          63     63

If TYPE is a 'C' then CPNAMT is the field to use
If TYPE is a 'P' then PCTAMT is the field to use

I also need to figure out how, when and  where QTY2 is used
I also need to analyze if there is anything else

At the moment all I am doing is staring at my screen, thinking whether or
not to re-design this whole file to be more meaningful, BUT what is
required is a view
As always any and all responses GRATEFULLY accepted

Thanks in advance



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