Hi all,when i am trying to convert date with to_char,i am getting
Invalid number error .Can someone suggest me where i am doing wrong.
SQL> ed
Wrote file afiedt.buf
1 SELECT DISTINCT rsh.SHIPMENT_NUM FILE#,
2 rsh.NUM_OF_CONTAINERS No_of_cartons,
3 aps.VENDOR_NAME VENDOR,
4 pha.SEGMENT1 PO#,
5 pha.COMMENTS Description,
6 msi.segment2 Customer,
7 to_char(rsh.attribute1,'DD-Mon-YYYY') ETA,
8 rsh.ATTRIBUTE2 REFERENCE# ,
9 wc.freight_code,
10 rsh.SHIPPED_DATE SCHEDULE_SHIP_DATE,
11 wc.SCAC_CODE ,
12 rsh.receipt_num,
13 rsh.COMMENTS Container#,
14 rsh.BILL_OF_LADING
15 FROM apps.rcv_shipment_headers rsh,
16 apps. rcv_shipment_lines rsl,
17 apps.ap_suppliers aps,
18 apps.mtl_system_items msi,
19 apps.po_headers_all pha,
20 apps.wsh_carriers wc
21 WHERE rsh.VENDOR_ID = aps.VENDOR_ID
22 and rsl.shipment_header_id = rsh.shipment_header_id
23 and pha.po_header_id = rsl.po_header_id
24 and rsl.item_id = msi.inventory_item_id
25 and rsl.to_organization_id = msi.organization_id
26 --and rsh.ATTRIBUTE1 is NOT NULL
27 and rsh.freight_carrier_code = wc.freight_code(+)
28 and rsh.SHIPMENT_NUM = 'NOV618'
29* --and pha.SEGMENT1 = '12770'
SQL> /
to_char(rsh.attribute1,'DD-Mon-YYYY') ETA,
*
ERROR at line 7:
ORA-01722: invalid number
SQL> select to_char(sysdate,'DD-Mon-YYYY') from dual;
TO_CHAR(SYSDATE,'
-----------------
08-Nov-2011
Any help is appreciated,
Thanks in advance!!