Skip to Content
author's profile photo
Former Member

How do I get "CO" variable 01 to 67 in character format?

Post Author: Dennis Hernan

CA Forum: Data Connectivity and SQL

I have 2 databases. "DBA"."RMS_A0_SEGMENT" & "DBA"."RMS_G0_SEGMENT". The A0 segment has a CNTY variable. We create table B from A only to create SELYEAR & CO. SELYEAR is numeric because it is "2007 as". CO is numeric because it is "CNTY as". The values in CNTY are 1 to 67 numeric. How do I get "CO" variable 01 to 67 in character format?

Is this standard SQL or would you call it Crysal SQL format? Select "B"."CNTY", "B"."SR", "B"."SEG", "B"."FED_AID", "B"."TBK_CODE", "B"."PAVE_CYC", "B"."SURFACE", "B"."LENGTH", "B".SELYEAR, "C"."KEY_YEAR", "C"."SEG", "C"."SR", "C"."CNTY", "C"."SLDSTATU", "B"."CO" FROM (SELECT "A"."CNTY", "A"."SR", "A"."SEG", "A"."FED_AID", "A"."TBK_CODE", "A"."PAVE_CYC", "A"."SURFACE", "A"."LENGTH", 2007 as SELYEAR, CNTY as CO FROM "DBA"."RMS_A0_SEGMENT" "A") "B" LEFT JOIN "DBA"."RMS_G0_SEGMENT" "C" ON ("B"."SEG" = "C"."SEG") AND ("B"."SR" = "C"."SR") AND ("B"."CNTY" = "C"."CNTY") AND ("B".SELYEAR = "C"."KEY_YEAR")

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

7 Answers

  • author's profile photo
    Former Member
    Posted on Aug 21, 2007 at 12:53 PM

    Post Author: Dennis Hernan

    CA Forum: Data Connectivity and SQL

    What is the syntax to format a variable Character in SQL?

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo
    Former Member
    Posted on Aug 21, 2007 at 04:19 PM

    Post Author: pvierheilig

    CA Forum: Data Connectivity and SQL

    Is it that you are attempting to display, for example, 01 as "one", and so on? Or attempting to convert a numeric value to a text value...

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo
    Former Member
    Posted on Aug 21, 2007 at 05:39 PM

    Post Author: Dennis Hernan

    CA Forum: Data Connectivity and SQL

    I am attempting to convert to numeric. This in in the read (from the Database Expert Add Command) from my table for a merge with another table. I'm not able to use a function because it's needed for the merge.See the example?

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo
    Former Member
    Posted on Aug 21, 2007 at 05:55 PM

    Post Author: Dennis Hernan

    CA Forum: Data Connectivity and SQL

    Here's a simple example, althought the format is reversed:

    SELECT "A"."A01_CTY_CDE", "A"."DIST_CODE","A"."CTY_NAME",A01_CTY_CDE AS CO NUMERIC(2)FROM SYSADM.CNTY_DIST_XREF "A"

    I'm trying to create the CO variable numeric(2) from the A01_CTY_CDE which is character(2). The values are 1 to 67 character, I need them numeric to merge with another linked table. I am using the Add Command In Database Expert..

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo
    Former Member
    Posted on Aug 22, 2007 at 12:36 PM

    Post Author: Dennis Hernan

    CA Forum: Data Connectivity and SQL

    Here's a simple example: SELECT "A"."A01_CTY_CDE", "A"."DIST_CODE","A"."CTY_NAME", A01_CTY_CDE AS CO NUMERIC(2) FROM SYSADM.CNTY_DIST_XREF "A" I'm trying to create the CO variable numeric(2) from the A01_CTY_CDE which is character(2). The values in A01_CTY_CDE are 1 to 67 character, I need CO to be numeric to merge with another linked table. "I am using the Add Command In Database Expert". I need 4 variables when this is done in the Command Database, A01_CTY_CDE, DIST_CODE,CTY_NAME all charater from the original database "SYSADM.CNTY_DIST_XREF". Also I'm trying & need your help, to create a variable CO with a numberic format, and the values equal to the A01_CTY_CDE variable. Call If you like, Dennis, 717.787.7293.

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo
    Former Member
    Posted on Aug 23, 2007 at 04:00 PM

    Post Author: pvierheilig

    CA Forum: Data Connectivity and SQL

    Ok, here's a couple thoughts. In your SQL statement (assuming you are using MS SQL/T-SQL), you'd need to add either a CONVERT() or CAST() function to the SQL statement:

    Select cast(table.col as numeric(2)) as CTY_CDE... I think that is the correct syntax.

    Or,

    On the data returned from your SQL Command, apply the ToNumber() function in a new formula to your returned data and use that result for display and calculation functions.

    Might one of these options help?

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo
    Former Member
    Posted on Aug 24, 2007 at 02:40 PM

    Post Author: Dennis Hernan

    CA Forum: Data Connectivity and SQL

    Someone gave me this syntax and it works. I'm not sure what I'm using here in the Add Command of Crystal. Would you know or guess which SQL this is?

    SELECT "A"."A01_CTY_CDE", "A"."DIST_CODE","A"."CTY_NAME", INTEGER(A01_CTY_CDE) AS CO FROM SYSADM.CNTY_DIST_XREF "A"

    Add comment
    10|10000 characters needed characters exceeded