Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2010
    Posts
    7

    Unanswered: Converting Data Types in order to link tables

    I have looked through tons of threads to no avail...I am having some difficulty converting a data type - Number to a data type - String.

    I have two tables, Table1 & Table2.

    The following fields, SER_ID and the SRV_ID, should link the tables. However the SER_ID field is a Data Type: String and the SRV_ID field is a Data Type: Number.

    Each field has 346 records, SER_ID records are 000001, 000002, and 000003...000346 and so forth. SRV_ID records are 1, 2, and 3...346 and so on.

    I would like to convert the SRV_ID field to a String while keeping the leading zeros, and then link both tables in order to retrieve data from both tables.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
      1* select lpad(to_char(9),9,'0') from dual
    SQL> /
    
    LPAD(TO_C
    ---------
    000000009
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2010
    Posts
    7
    Thank you for your response...I neglected to mention that I am using Crystal Reports to retrieve this data. Earlier I read a few posts that had some similar responses as your post, but, I am not sure how to integrate this with the current statement.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I would like to convert the SRV_ID field to a String while keeping the leading zeros, and then link both tables in order to retrieve data from both tables.

    Alternatively use TO_NUMBER(SER_ID) & compare directly to SRV_ID
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Apr 2010
    Posts
    7
    Thank you so much, this worked well.

    SELECT "POPPEV"."PI_EVT_DATE", "POPPEV"."PI_EVENT", "POPPEV"."PI_CMP_UNIT", TO_NUMBER("POPPEV"."PI_SER_ID"), "POPPEV"."PI_MAST_NO", "POPSRV"."QC_SRV_ID", "POPSRV"."QC_VIP_ID", "POPSRV"."QC_SRV_TYPE", "POPSRV"."QC_AGENCY", "POPSRV"."QC_EMAIL"
    FROM "POPPEV" "POPPEV", "POPSRV" "POPSRV"
    WHERE TO_NUMBER("POPPEV"."PI_SER_ID") = POPSRV.QC_SRV_ID AND
    "POPPEV"."PI_EVENT"='CMPUNT'
    ORDER BY "POPPEV"."PI_SER_ID", "POPPEV"."PI_MAST_NO", "POPPEV"."PI_CMP_UNIT"

Posting Permissions

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