RE: [sqr-users] RE: Variable Declaration

[kevin.reschenberg@sparkpath.com]

If you are rewriting these old COBOL programs in SQR, try to avoid using
the equivalent of COMP and COMP-3 fields if you can!  Just read and
write numbers as normal strings and convert them to/from numbers as
needed using normal SQR functions such as EDIT() and TO_NUMBER(), as
Steve suggested.
 
If you really, really need to read or write "mainframe" files directly,
there are a few things to keep in mind.
 
As Jeff pointed out, you must FTP the input file without using the
ASCII/CRLF options, which translate the data from EBCDIC to ASCII but
will destroy any COMP or COMP-3 data.  You need an exact copy of the
file without translation.
 
However, that still leaves you with two problems.  First, all text
fields will still be in EBCDIC (assuming the file is from an
EBCDIC-based mainframe) and your SQR program must translate them to
ASCII.  That's not too difficult.
 
The second problem has to do with the binary COMP and packed COMP-3
fields.  Again, as Jeff mentioned, these will contain binary zero bytes
and therefore cannot be read by SQR as strings.  The secret to dealing
with these is to read the file, one character at a time, reading each
character into a numeric #variable.  (Go back and reread the first
sentence of this email before continuing.)  Open the file as 
RECORD=1:FIXED-NOLF .  Then  READ 1 INTO #CHAR:1  to get each
character, which could be a 0 or any other number.  If it's a binary
field you are handling, do the math to get the value of the entire
field.  If it's packed (COMP-3) or zoned (PIC S999 DISPLAY), you need
to pay special attention to the last byte, which encodes the sign.
 
When I worked at PeopleSoft I created an SQC containing procedures for
reading and writing binary, packed, zoned and EBCDIC text fields, but
it's probably lost by now.  I just checked Tony DeLia's site and there
are links to something similar, but the links seem to be broken. 
(Anybody know what happened to him?)  It isn't too difficult to do, IF
you have a good understanding of these formats on the mainframe and use
the one-character numeric variable trick.  
 
Sorry about this.  If Jeff's idea about loading the data into a table
works for you, that's far better than trying to code this!


Kevin Reschenberg
SparkPath Technologies, Inc.
sqr-info.com

www.sparkpath.com
www.sqr-info.com


 
-------- Original Message --------
Subject: RE: [sqr-users] RE: Variable Declaration
From: Jeff.Steinfeldt@wellsfargo.com
Date: Fri, January 14, 2005 8:46 am
To: sqr-users@sqrug.org

Well, true, he asked how to declare the variables but the short answer
is
that you can't declare anything in SQR that will allow you to read a
file
containing a COBOL COMP-3 data type.  File either needs to be pure ASCII
to
read from SQR or must be converted to compatible data types in some
other
fashion.

-----Original Message-----
From: sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org
[mailto:sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org] On
Behalf Of Alexander, Steve
Sent: Friday, January 14, 2005 10:39 AM
To: 'This list is for discussion about the SQR database
reportinglanguage
from Hyperion Solutions.'
Subject: RE: [sqr-users] RE: Variable Declaration


Is Krishna asking how to import data that has been formatted in a
specific
way, or how to declare variables that have a specific number of digits?

I thought it was the latter.  SQR usually does not require declarations
for
string or numeric variables.  If numbers have to be formatted for
output, we
use the edit() function to convert them to a string.

-----Original Message-----
From: Jeff.Steinfeldt@wellsfargo.com
[mailto:Jeff.Steinfeldt@wellsfargo.com]
Sent: Friday, January 14, 2005 6:41 AM
To: sqr-users@sqrug.org
Subject: RE: [sqr-users] RE: Variable Declaration


Ok, that helps.  I'll assume that you'll be pulling the COBOL files
across
via a BINARY FTP so the COMP-3 doesn't get pooched when translated.

You really don't want to try to read this file directly from SQR.  That
COMP-3 field has to come across as a binary and will likely contain the
binary zero.  SQR doesn't handle those well, basically treating them as
end-of-line characters and truncating the record.

I suggest that you look into something called SQL*Loader which is an
data
loading utility provided by Oracle (with the install).  You would use
SQL*Loader to load the data into an Oracle table and then query that
from
your SQR program.  Your Oracle DBA will be able to help set up the
control
file for SQL*Loader.

Its been a while since I worked with mainframe COBOL so there's a
certain
amount of guessing here but I think your records will look something
like
this:

xxxxxxxxxxxxyyyyyyyyyyyyywwwwwwwwzzzzzzzzzzzzzz
9999999999.99999999999.99-9999.999999999999999F

Did they tell you the record length on the COBOL file?  Is it 47?  Must
be
more to the record than what you've included.

-----Original Message-----
From: sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org
[mailto:sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org] On
Behalf Of Krishna Prasad Kommuri
Sent: Friday, January 14, 2005 8:20 AM
To: sqr-users@sqrug.org
Subject: [sqr-users] RE: Variable Declaration


We are on Oracle and we operate on Windows Environment. We are
converting
COBOL program which were previous run on mainframe into SQR.  Please let
me
know if you need further details.

Thanks,


On Fri, 14 Jan 2005 06:04:23 -0500, sqr-users-request@sqrug.org
<sqr-users-request@sqrug.org> wrote:
> Send sqr-users mailing list submissions to
>        sqr-users@sqrug.org
> 
> To subscribe or unsubscribe via the World Wide Web, visit
>        http://www.sqrug.org/mailman/listinfo/sqr-users
> or, via email, send a message with subject or body 'help' to
>        sqr-users-request@sqrug.org
> 
> You can reach the person managing the list at
>        sqr-users-owner@sqrug.org
> 
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of sqr-users digest..."
> 
> 
> Today's Topics:
> 
>   1. Variable Declaration (Krishna Prasad Kommuri)
>   2. RE: Variable Declaration (Jeff.Steinfeldt@wellsfargo.com)
> 
> 
> 
> ---------- Forwarded message ----------
> From: Krishna Prasad Kommuri <kommuri@gmail.com>
> To: sqr-users@sqrug.org
> Date: Thu, 13 Jan 2005 16:13:47 -0600
> Subject: [sqr-users] Variable Declaration
> Hi,
>  How to declare the following decimal variables in SQR
> In COBOL: X PIC 9(10)V9(2) VALUE 0.
> Y PIC 9(10)V99 VALUE ZEROS.
> W PIC S9(4)V99 SIGN IS LEADING, SEPARATE.
> Z PIC S9(10)V9(2) USAGE COMP-3.
> 
> Any help is appreciated.
> Thanks in advance,
> 
> 
> 
> ---------- Forwarded message ----------
> From: Jeff.Steinfeldt@wellsfargo.com
> To: sqr-users@sqrug.org
> Date: Thu, 13 Jan 2005 16:19:07 -0600
> Subject: RE: [sqr-users] Variable Declaration
> Are you running on a mainframe or are you importing an ebcdic file
> into Unix or Windows environment?  Database is Oracle?
> 
> -----Original Message-----
> From: sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org
> [mailto:sqr-users-bounces+jeff.steinfeldt=wellsfargo.com@sqrug.org] On
> Behalf Of Krishna Prasad Kommuri
> Sent: Thursday, January 13, 2005 4:14 PM
> To: sqr-users@sqrug.org
> Subject: [sqr-users] Variable Declaration
> 
> Hi,
>  How to declare the following decimal variables in SQR
> In COBOL: X PIC 9(10)V9(2) VALUE 0.
> Y PIC 9(10)V99 VALUE ZEROS.
> W PIC S9(4)V99 SIGN IS LEADING, SEPARATE.
> Z PIC S9(10)V9(2) USAGE COMP-3.
> 
> Any help is appreciated.
> Thanks in advance,
> 
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users
> 
> 
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users
> 
> 
>

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users 


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users