Danny Tramnitzke wrote:
>
> Thanks a lot
>
> But, there is still a problem :
>
> There are 2 two timestamp-columns, one with values, and one
> with (also)
> NULL-values, and to make a char(a,t) on a NULL value leads to
> an Error :
>
> Datetime field overflow;-3048 POS(1) Invalid date format:ISO.
This is a known bug, was fixed with 7.4.3.23, which is not available so far.
>
> But I have to get both of the timestamp-colums, each row, so
> that I can't
> write :
>
> ..where col_date2 <> NULL
!!! never ever ask for NULL using <comp op> !!!
ask : col_date2 IS NOT NULL
>
> ...because then I get only the rows, wich haven't a NULL in
> col_date2 .
> So I need a clause, that converts only the valid-values in
> col_date2, but
> not the NULL Values.
> Am I able to do so in the SELECT Statement itself (haven't
> found a solution
> in the sapdb docu), or do I have to write a Parser, which goes
> into that table and rewrites all NULL's to ' ' or a
> dummy-date or something
> like that...?
You can't write ' ' into a timestamp-column.
Changing the NULL to a dummy-timestamp (and re-changed if bug-fix is available)
may be an option.
Otherwise:
A temporary workaround (until 7.4.3.23 will be available) could be:
CHAR(DATE( VALUE (col_date2, '16480528120000000000')) , USA)
(or any other dummy-timestamp given as string-literal) if you can afford
to have this dummy_date in the result, otherwise
CHAR( decode( DATE( VALUE (col_date2, '16480528120000000000')) , '16480528', NULL,
DATE (col_date2)) , USA)
aerghz, no discussion, this is a horror and SHOULD be re-changed when the bug-fix is
available
Elke
SAP Labs Berlin
> Best regards,
> Danny
>
>
> Am Tue, 27 May 2003 16:02:53 +0200 hat Zabach, Elke
> <[EMAIL PROTECTED]>
> geschrieben:
>
> > Danny Tramnitzke wrote:
> >
> >> Hi all
> >>
> >> I have an INTERNAL Table with a timestamp column.
> >> And I want to make a selection on that column, but the
> Output shouldn't
> >> be INTERNAL Timestamp,
> >> but USA Date.
> >>
> >> So, it should be a SQL-Statement like the sybase one :
> >>
> >> SELECT convert(char(10), date_col, 103) as USA_Datum FROM testtable
> >
> > May I ask you to check the reference manual --> basic elements -->
> > Function --> conversion function resp. extraction function:
> >
> > SELECT char (date(timestamp_col), USA) from testtable
> >
> > Elke
> > SAP Labs Berlin
> >
> >
> >> The convertion of the date_col with format '103' is the
> USA Dateformat
> >> in result.
> >> So in SAPDB it could look like this :
> >>
> >> SELECT date_col = USA from testtable
> >>
> >> (but that doesn't work)
> >>
> >> I only can do this DATE-Formation/Convertation in the
> SQL-Statement,
> >> because it's part of a existing Java-Class. So I can't make the
> >> Statement "DATE 'DD/MM/YYYY' " like in a Commandfile of
> the repmcli...
> >>
> >> regards,
> >> Danny
> >>
> >> -- Using M2, Opera's revolutionary e-mail client:
> >> http://www.opera.com/m2/
> >> _______________________________________________
> >> sapdb.general mailing list
> >> [EMAIL PROTECTED]
> >> http://listserv.sap.com/mailman/listinfo/sapdb.general
> >>
> > _______________________________________________
> > sapdb.general mailing list
> > [EMAIL PROTECTED]
> > http://listserv.sap.com/mailman/listinfo/sapdb.general
> >
> >
>
>
>
> --
> Using M2, Opera's revolutionary e-mail client:
http://www.opera.com/m2/
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
