Re: A few question if you have time.

[Sam Spritzer <teknogeek9@YAHOO.COM>]

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