SQL ISSUE

[priyanthan]

Hi All,

I'm using the following view in my report, but for some reason the first column is not showing the actual data that is in the view.

CREATE VIEW POSH.VWHOLIDAY

(HOLIDAY, HLDATE, HLNAME) AS

(SELECT (SUBSTR(CHAR(HLDATE),1,2)||SUBSTR(CHAR(HLDATE),4,2)) AS HOLIDAY ,HLDATE,HLNAME FROM POSH.AGHOLIDAY);
COMMIT;

HOLIDAY(char 4) HLDATE(date) HLNAME (VARCHAR)
------- ------ -------


This is how it's showing up in the report.

200- 1/1/2010 NEW YEAR
200- 2/15/2010 FAMILY DAY
200- 4/2/2010 EASTER
200- 5/24/2010 VICTORIA DAY
200- 7/1/2010 CANADA DAY
200- 8/2/2010 CIVIC DAY
200- 9/6/2010 LABOUR DAY
200- 10/11/2010 THANKSGIVING

WHEN I QUERY THE DATABASE IN THE COMMAND PROMPT IT GIVES THE CORRECT RESULTS,

HOLIDAY HLDATE HLNAME
------- ---------- --------------
0101 01/01/2010 NEW YEAR
0215 02/15/2010 FAMILY DAY
0402 04/02/2010 EASTER
0524 05/24/2010 VICTORIA DAY
0701 07/01/2010 CANADA DAY
0802 08/02/2010 CIVIC DAY
0906 09/06/2010 LABOUR DAY
1011 10/11/2010 THANKSGIVING

Can anyone please tell me what am i missing here.

working with db2& CR8.5

Thanks,

 

[lbass]

It looks to me like the data in the database starts with the year, since 200- would result if the actual field was: 2010-01-01
and that it is just formatted to display as MM/dd/yyyy. I think you have to specify the formatting in the view so that your subselect results in the desired characters.

-LB