Re: A few question if you have time.

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

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"