Varchar2 gets converted to DATE in EXCEL

MarcLaf

Hi,

I have a report that outputs data to EXCEL format (spreadsheet). I have columns (ROOM and STORAGE AREA) that are declared as VARCHAR2 in the database.

Query from report:

SELECT vs.BSE_NAME BASE

    , LTRIM (vs.bld_no) BUILDING_NUMBER        

    , LTRIM (vs.bld_name) building

    , LTRIM (DECODE (:p_language

             , 'F', vs.rnm_name_fre

             , vs.rnm_name_eng) ) room

    , LTRIM (vs.rom_room_no) ROOM_NUMBER

    , LTRIM (DECODE (:p_language

           , 'F', vs.san_name_fre || ' ' || vs.sar_no

           , vs.san_name_eng || ' ' || vs.sar_no) ) STORAGE_AREA

    , LTRIM (vs.sar_no) STORAGE_AREA_NUMBER

    , (SELECT COUNT(*)

    FROM SAR, HLD 

    WHERE SAR.SAR_ID = HLD.SAR_ID

     AND SAR.SAR_ID = vs.sar_id) count_of_hmra

  FROM v_storage_areas vs

    , loc l

    , frm f

    , unt u

    , sec s

    , snm sn

  WHERE vs.loc_id = l.loc_id

   AND vs.FRM_ID = f.FRM_ID

   AND vs.UNT_ID = u.UNT_ID

   AND vs.SEC_ID = s.SEC_ID

   AND sn.SNM_ID = s.SNM_ID

When I execute this command in Sqlpplus:

When I look at the EXCEL file, the ROOM_NUMBER is converted to DATE... WHY?

Suresh Sangaran-Oracle

Hello,

What is the version of Oracle Reports?

In Oracle Reports you have "Format mask" based on the "Source" type. Use the Format mask to preserve the field format.

Thanks, Suresh

MarcLaf

Hi,

I have Oracle Reports Builder 12.2.

I use the field name, from my QRY (Select statement) for the Source. All the COLUMN information comes from the database:

I can not select the FORMAT option when I set the source to a field name that is in my select qry...

Cheers,

Marc L

Suresh Sangaran-Oracle

Hi,

Try to_char(LTRIM (vs.sar_no))STORAGE_AREA_NUMBER.

Thanks, Suresh