Actually i narrowed down the problem .

/* Dasn't work
SELECT WRVINV,WRALPH,Decimal(WRAA,11,0) as
WRAA,WRDRCRCAD,WRCUR,WREXR,DB2ADMIN.TO_DATE(WRIVD)
FROM TDDTA.TDCTAPW
WHERE WRRTYPE = 'IH'
ORDER BY WRVINV;
*/

To_date is a custom function to format julian date ( double as a sourse)
to standart date.

/* This works
SELECT WRVINV,WRALPH,Decimal(WRAA,11,0) as
WRAA,WRDRCRCAD,WRCUR,WREXR,WRIVD
FROM TDDTA.TDCTAPW
WHERE WRRTYPE = 'IH'
ORDER BY WRVINV;
*/

/* This works
SELECT WRVINV,WRALPH,Decimal(WRAA,11,0) as
WRAA,WRDRCRCAD,WRCUR,WREXR,DB2ADMIN.TO_DATE(WRIVD) as WRIVD
FROM TDDTA.TDCTAPW
WHERE WRRTYPE = 'IH';
*/

Here is the code for date function

create function DB2ADMIN.TO_DATE(i_julian_date double)
specific TO_DATE_yyyymmdd
returns varchar(50)
reads sql data
begin atomic
declare v_db2_year int;
declare v_db2_days int;
declare mydate date;
declare mydate_formatted varchar(50);
if i_julian_date is null then return null;
end if;
set v_db2_year = trunc((i_julian_date/1000),0);
set v_db2_days = i_julian_date - 1000 * v_db2_year;
set mydate = (select Date(Cast(Ltrim(Rtrim(cast((1900 + v_db2_year) as
char(4)))) || Right('000' || Ltrim(Rtrim(Cast(v_db2_days as char(3)))), 3)
as char(7))) from sysibm.sysdummy1);
set mydate_formatted = Cast(Year(mydate) as char(4)) || Right('00' ||
Ltrim(Rtrim(Cast(Month(mydate) as char(2)))),2) || Right('00' ||
Ltrim(Rtrim(Cast(Day(mydate) as char(2)))),2);
return mydate_formatted;
end


Thanks !!!






DANIEL CALLAHAN <[Email address protected]
28/06/2005 11:24 AM

To
"Ness, Maxim @ OYCBRE" <maxim.[Email address protected]
cc

Subject
Re: Order by problem - it's URGENT !!!






**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal
**Anything below this line is the original email text







What column name are you ordering on ?





"Maxim Ness "
<db2udbdba-ezmlms
hield-x4240379.x1 To

[Email address protected] "LazyDBA Discussion"
.com> [Email address protected]
cc

06/28/2005 11:02
AM Subject

Order by problem - it's URGENT !!!










Hello All,

I have a problem with order by in one table. I am able to view the data
using select * statement but when i try to do "select * order by
column_name" i get:

SQL0181N The string representation of a datetime value is out of range.
SQLSTATE=22007

sqlcode : -181

I don't have any datetime columns in the table. Environment is DB2 UDB
8.1.8.762 on Windows 2003 server.

This is production issue please advise.


Thanks !!!




---------------------------------------------------------------------
PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html








DB2 & UDB email list listserv db2-l LazyDBA home page