Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    93

    Unanswered: help with alias? on insert only

    Can someone tell me what is wrong with this statement? When I do a Select it works fine (without the insert etc)


    INSERT INTO COMPLAINT_ENQINFO(
    SELECT DISTINCT CONTACT.CONTACT_REFERENCE, UPPER(SUBSTR(HIERARCHY_ITEM.DESCRIPTION,1,3)),
    CONTACT_PART.CONTACT_PART_ID,
    CONTACT_X.D1,
    '',
    CONTACT_X.D2,
    CONTACT_PART_X.USERFIELDB,
    HIERARCHY_01||'-'||HIERARCHY_02||'-'||HIERARCHY_03||'-'||HIERARCHY_04||'-'||HIERARCHY_05,
    CONTACT_PART_X.T12,
    CONTACT_STATUS.DESCRIPTION,
    CONTACT_PART_X.PRODUCTCODE,
    '',
    CONTACT_PART_X.T45,
    A1.DESCRIPTION,
    B2.DESCRIPTION,
    CONTACT_PART_X.PRODUCTINFOB,
    C3.DESCRIPTION,
    CONTACT_PART_X.USERFIELDG
    FROM CONTACT_PART_X, CONTACT_STATUS, CONTACT,CONTACT_X, HIERARCHY_ITEM, MULTI_HIERARCHY, CONTACT_PART, PICK_LIST_ITEM A1, PICK_LIST_ITEM B2, PICK_LIST_ITEM C3 WHERE CONTACT.CONTACT_ID=CONTACT_X.CONTACT_ID
    AND HIERARCHY_ITEM.HIERARCHY_ITEM_ID=MULTI_HIERARCHY.H IERARCHY_01
    AND MULTI_HIERARCHY.CONTACT_ID=CONTACT.CONTACT_ID
    AND CONTACT_PART.CONTACT_PART_ID=CONTACT_PART_X.CONTAC T_PART_ID
    AND CONTACT.CONTACT_ID=CONTACT_PART.CONTACT_ID
    AND CONTACT_STATUS.CONTACT_STATUS_ID=CONTACT.CONTACT_S TATUS_ID
    AND A1.PICK_LIST_ITEM_ID=CONTACT_PART_X.USERFIELD01
    AND B2.PICK_LIST_ITEM_ID=CONTACT_PART_X.USERFIELD02
    AND C3.PICK_LIST_ITEM_ID=CONTACT_PART_X.USERFIELDF
    AND MULTI_HIERARCHY.PRIMARY_FLAG=1)

    ERROR at line 15:
    ORA-01722: invalid number
    Cheers
    Etravels

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You are trying to insert something (B2.DESCRIPTION according to the line number) that isn't a number into a column that can only accept a number.

    BTW, an INSERT statement that forms part of an application should always specify the target columns:

    INSERT INTO t (c1, c2, c3) SELECT ...

    It's OK to leave these out when just typing SQL into SQL Plus, but not in production code.

  3. #3
    Join Date
    Feb 2004
    Posts
    93

    Talking I have this one sorted!! sorry all for time wasting

    Just realised I had the temporary table datatype set to number as opposed to varchar

    Cheers
    Cheers
    Etravels

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •