Forum Stats

  • 3,733,249 Users
  • 2,246,737 Discussions
  • 7,856,634 Comments

Discussions

Invalid number error

799603
799603 Member Posts: 677
edited November 2011 in SQL & PL/SQL
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!!

Answers

This discussion has been closed.