Get Data Moving 1
Results 1 to 2 of 2

Thread: NAVCMD - sql convert datetime format to DB2

  1. #1
    Betty Sullivan is offline Junior Member
    Join Date
    Sep 2006
    Posts
    12
    Rep Power
    0

    NAVCMD - sql convert datetime format to DB2

    I have been trying to convert the datetime format from sql to db2 and am using NAVCMD to test the select statement. It keeps giving me errors when I copy the statement from sql. Is there a different way to handle the parens and commas in NAVCMD when using sql functions like convert ?
    I was able to use substring on a char column.

    ,NavSQL > ,
    select convert(varchar(26),WF18_PENDED_DT,120) from wf18_assignment_pend where w
    f01_assignment_id = 0;
    ,NAV_UTIL: Calling to nvIQSpec_Prepare failed with error =. RC=-220,
    ,İA047¨ Syntax error at line 1 position 42 near ',' (one of follows was expecte,
    ,d: ')') in SQL 'select convert(varchar(26),WF18_PENDED_DT,120) from wf18_assig,
    ,nment_pend where wf01_assignment_id = 0'İRC=-220¨,


    Another error we are having is when more than 1 developer is trying to use NAVCMD - how do we change the tranlog that NAVCMD uses to a gds so more than 1 of us can use the utility?


    ,Local>,
    execute -b tran
    ,MIM1088 CONTENTION WITH BEY8303 OWNS EXCL ON A0,
    ,MIM1089 YOU NEED EXCL SYSDSN AC4600.TDB2.TMP.TRANLOG,
    ,MIM1088 CONTENTION WITH BEY8303 OWNS EXCL ON A0,
    ,MIM1089 YOU NEED EXCL SYSDSN AC4600.TDB2.TMP.TRANLOG,
    ,Cannot open log file 'AC4600.TDB2.TMP.TRANLOG'; EDC5061I An error occurred whe,
    ,n attempting to define a file to the system.,
    ,Log file is 'AC4600.TDB2.TMP.LG000004',
    ,DS name: ,

  2. #2
    laura is offline Support
    Join Date
    Aug 2006
    Posts
    19
    Rep Power
    0

    CONVERT Function

    Regarding your first question, you need to use Attunity SQL syntax.

    You can find some details about the Attunity SQL functions on the AIS User Guide in the chapter: Attunity SQL Syntax > Function.

    In your case:

    CONVERT(expression, datatype)

    Returns the expression converted to the specified target data type. The valid data types are:

    Char[(m)] (The default value of m is 1.)
    [Char] varchar(m)
    Tinyint
    Smallint
    Integer
    Numeric[(p[,s])] (The default value of p (precision) is 10 and s (scale) is 0.)
    Float
    Double
    Date
    Time
    Timestamp

    Note: expression cannot be a BLOB.

    Note: The converted expression is delegated to the data source for processing whenever possible. Note that data sources handle strings differently and therefore the result of converting an expression to a string can be different per data source.

    NAV_CONVERT(expression, datatype)

    Returns the expression converted to the specified target data type and does not pass the query to the data source for processing. The valid data types are the same as for the CONVERT function, listed above.

    TO_GRAPHIC(string)

    Returns the string converted to a graphic string (a double byte).

Posting Permissions

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