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

    Unanswered: renaming items within a column

    how do I can state the following:, the 'HIERARCHY_ITEM.DESCRIPTION' results in Complaint, Enquiry and Praise. How do I display each of the 3 items that appear in my results so that is becomes: COM, ENQ, & PRA.?


    here is my select statement:


    SELECT CONTACT.CONTACT_REFERENCE,
    HIERARCHY_ITEM.DESCRIPTION,
    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_UPDATED_BY,
    CONTACT_PART_X.PRODUCTCODE,
    '',
    CONTACT_PART_X.T45,
    CONTACT_PART_X.USERFIELD01,
    CONTACT_PART_X.USERFIELD02,
    CONTACT_PART_X.PRODUCTINFOB,
    CONTACT_PART_X.USERFIELDF,
    CONTACT_PART_X.USERFIELDG
    FROM CONTACT_PART_X, CONTACT,CONTACT_X, HIERARCHY_ITEM, MULTI_HIERARCHY, CONTACT_PART
    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 MULTI_HIERARCHY.PRIMARY_FLAG=1

    here is my results;
    1000226;Complaint;1000283;......
    1000256; Enquiry;.....
    1000458 :PRAISE......

    Basically I want trunc the the Complaint etc..? Is this correct?
    Cheers
    Etravels

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    SELECT ...,
    UPPER(SUBSTR(HIERARCHY_ITEM.DESCRIPTION1,3)) as code,
    ...

  3. #3
    Join Date
    Feb 2004
    Posts
    93

    Tony...how can i display only the first 3 characters?

    using that code, it only displays the last 3 or there afterwards...

    basically, I want COMPLAINT to become, 'COM'
    Cheers
    Etravels

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Small typo, sorry:

    SELECT ...,
    UPPER(SUBSTR(HIERARCHY_ITEM.DESCRIPTION,1,3)) as code,
    ...

Posting Permissions

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