RE: [sqr-users] RE: Variable Declaration

[Jeff.Steinfeldt@wellsfargo.com]

I guess the ultimate would be to ask the mainframe people to write you the
file as text.  Then it could be FTPd without worrying about translation
problems and you could field map the file and TO_NUMBER() as needed after
reading a record.

-----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 kevin.reschenberg@sparkpath.com
Sent: Friday, January 14, 2005 12:09 PM
To: This list is for discussion about the SQR database reporting language
from Hyperion Solutions.
Subject: RE: [sqr-users] RE: Variable Declaration


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

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