Results 1 to 5 of 5

Thread: Newbie Problem

  1. #1
    Join Date
    Sep 2003
    Posts
    5

    Post Unanswered: Newbie Problem

    I'm trying to use the following

    SELECT
    SPACE(20 - LEN(Extension)) + Extension AS Ext,
    StartDateTime,
    SPACE(9 - LEN(Duration)) + Duration AS Dur,
    SPACE(20 - LEN(ClipNumber)) + ClipNumber AS Dial,
    CONVERT(char(10)Cost) + AS Co

    FROM SMDROUT

    WHERE Flag = 'O'

    Cost is a money type
    StartDateTime is a date field and is changed to two char fields during the
    transformation

    However, i get the error
    Error description : deferred prepare could not be completed
    statements could not be prepared
    line 6: incorrect syntax near extension

    Any help would be appreciated!

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Newbie Problem

    Try this:

    SELECT
    SPACE(20 - LEN([Extension])) + [Extension] AS Ext,
    StartDateTime,
    SPACE(9 - LEN(Duration)) + Duration AS Dur,
    SPACE(20 - LEN(ClipNumber)) + ClipNumber AS Dial,
    CONVERT(char(10), Cost) AS Co

    FROM SMDROUT

    WHERE Flag = 'O'
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Sep 2003
    Posts
    5

    thanks!

    That worked great thanks

    i ended up modifying it so it is now

    SELECT
    CONVERT(char(20), [Extension]) AS Ext,
    StartDateTime,
    CONVERT(char(9), Duration) AS Dur,
    CONVERT(char(10), Cost) AS Co,
    CONVERT(char(20), DialledDigit) AS Dial
    FROM SMDROUT
    WHERE (Flag = 'O')

    this seems to make the formatting work better - however my output looks like this:

    201 ,123 , ,20021230,000000,0 , 0.00,

    and i need double quotes round each field

    "201 ","123 ", etc

    is there anyway to do this? double quotes are set to be the Text Qualifier in the properties of the export file but it doesnt seem to be putting the quotes in

  4. #4
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: thanks!

    Just to give you the idea:

    SELECT
    '"' + CONVERT(char(20), [Extension]) + '"' AS Ext,
    ....
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  5. #5
    Join Date
    Sep 2003
    Posts
    5
    That worked great - someone tried to tell me something similar before but i have now worked out that "" is different from '"' and '''' !!

    Thanks for your help

Posting Permissions

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