Re: A few question if you have time.

[Alan Ljungberg <Alan.Ljungberg@CNPL.ENBRIDGE.COM>]

Hello Sam,
Much appreciate the time spent in helping me here.
The issue isn't with the loading of date fields into Oracle.  The problem
is in validating Oracle date outputs against DB2 date output, without using
custom date variables.  What I would like to do after the Oracle db is
loaded is use the same SQR to output the Oracle tables to flat files then
compare the DB2 flat files to the Oracle ones.

And yes there is a lot to sort through.  We are talking some 1374 total
columns 140 of which are date columns in various positions, all different.

Since Oracle seems to be friendlier to this kid of manipulation with the
ALTER SESSION command I think we will leave the DB2 scripts as is and add
the add this SQL command to the Oracle versions to make the output formats
match.  That is unless someone has a better suggestion.

-Alan.





                    Sam Spritzer
                    <teknogeek9@YA        To:     SQR-USERS@list.iex.net
                    HOO.COM>              cc:
                    Sent by:              Subject:     Re: A few question if 
you have time.
                    "Discussion of
                    SQR, Brio
                    Software's
                    database
                    reporting
                    language"
                    <SQR-USERS@lis
                    t.iex.net>


                    02/05/2002
                    12:56 PM
                    Please respond
                    to sqr-users






Alan,
Before you load the data into Oracle, why don't you
reformat the dates?  How many formats could you
possible have?  If it was me, I think I would write an
#Include procedure to test for the various formats and
convert them to what is required on the Oracle side.
Sam

--- Alan Ljungberg <Alan.Ljungberg@CNPL.ENBRIDGE.COM>
wrote:
> Thanks for the feed back guys.  Perhaps I should
> provide some more
> background information.  We are converting data on a
> PeopleSoft DB2
> environment to a PeopleSoft Oracle environment.
> Exporting the DB2 data to
> flat files then importing to Oracle both via SQR.
> Sorry I should have
> mentioned we are running on SQR v4.3.4 .
>
> Part of the problem is we are dumping data from some
> 80 to 100 tables to
> delimited flat files, all the tables have different
> structures.  Rather
> then define each date column with a date variable,
> which would require a
> custom SQR for each table, we are just dumping dates
> as character strings
> into the flat file.  When we do this of course SQR
> picks up the DB default
> date format, since none is defined.
>
> This isn't a huge problem and only becomes an issue
> when we want to
> validate the data after it's loaded into Oracle with
> that from DB2.  I
> think the answer is to use the ALTER SESSION command
> in Oracle to match the
> format of the DB2 dates since DB2 has no such
> facility.
>
> -Alan.
>
>
>
>
>                     Don Mellen
>                     <donm@ONTKO.CO        To:
> SQR-USERS@list.iex.net
>                     M>                    cc:
>                     Sent by:              Subject:
>   Re: A few question if you have time.
>                     "Discussion of
>                     SQR, Brio
>                     Software's
>                     database
>                     reporting
>                     language"
>                     <SQR-USERS@lis
>                     t.iex.net>
>
>
>                     02/05/2002
>                     10:21 AM
>                     Please respond
>                     to sqr-users
>
>
>
>
>
>
> On Tue, 5 Feb 2002, Alan Ljungberg wrote:
>
> > I'm having to run some SQRs on both DB2 and Oracle
> databases.
> ...
> > 3. When selecting data from and Oracle data base
> one can control the
> > default date display but doing an:
> >
> >  BEGIN-SQL
> >   ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'
> >  END-SQL
> >
> > Is there any way to accomplish the same when
> selecting data on a DB2
> > platform ?
>
> Since Scott already hit 1 & 2, I'll chime in on 3
>
> If you're using SQR 4.0 or later, it would probably
> be best to pull out
> the dates as dates and edit them to get the format
> you want.  That way you
> don't have to worry about which DB you're hitting
> against.
>
> When you get a column....
>
> begin-select
> alias.date_col_name   &this_date=date
> ... etc.
>
>    let $print_date =
> datetostr(&this_date,$date_edit_mask)
>    print $print_date (#line, #col)
>
> from table_name  alias
> .... etc.
>
> The "=date" is necessary if you use any dynamic
> values in the select (when
> part of the sql is dynamic, all columns come out as
> char unless otherwise
> specified).  You can simply print the date with a
> print edit mask, such
> as...
>
>    print $print_date (#line, #col) edit
> :$date_edit_mask
>
> but certain versions of SQR will give an erroneous
> warning about 2 digit
> years when printing a date column directly (it still
> works correctly, you
> just get warning messages)
>
> HTH,
>
-----------------------------------------------------------------------
> Donald Mellen  | Ray Ontko & Co. - Richmond, IN -
> http://www.ontko.com/
> donm@ontko.com |  "In the beginning, there was
> nothing, which exploded"


=====
************************************************************
"In the beginning, there was something...which exploded yet, they find
nothing."

__________________________________________________
Do You Yahoo!?
Send FREE Valentine eCards with Yahoo! Greetings!
http://greetings.yahoo.com