[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
Re: A few question if you have time.
Alan,
No problem. As long as I am unemployed, I might as
well help out here (OOPS, does that count as a no no
here???).
Let me give it one more shot. Is there any way you
can take apart the month, day, year in both databases
and then compare? I am kind of thinking it would be
faster and maybe easier to do in MS Access. And
finally, of the 140 columns with dates, how many
different formats could there possible be?
Sam
--- Alan Ljungberg <Alan.Ljungberg@CNPL.ENBRIDGE.COM>
wrote:
> 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
=====
************************************************************
"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