Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: SQL Query question on Input Table

  1. #1
    Join Date
    Oct 2017
    Posts
    3

    Question SQL Query question on Input Table

    Hello there! This is my first post, as i'm an absolutely begginer with PDI.

    I am trying to perform an Input Table, with a very simple Teradata SQL Query:

    SELECT number
    FROM table

    Query works fine

    Lets say that number = 1234 or 5678 and i'd like to append an '9' integer to all number records. So, i execute this query


    SELECT concat(9, number)
    FROM table

    But i am getting this error:

    2017/10/26 15:44:01 - [Teradata Database] [TeraJDBC 16.10.00.03] [Error 3706] [SQLState 42000] Syntax error: expected something between '(' and the integer '9'.

    I've also noticed that common functions as LEFT(), RIGHT() are throwing same error for me.

    I don't know if there's any difference between usual Teradata syntax and syntax needed to get this work on PDI, but i can't get what i need

    Thanks in advance
    Last edited by Chaskon; 10-26-2017 at 02:47 PM.

  2. #2
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Quote Originally Posted by Chaskon View Post
    Lets say that number = 1234 or 5678 and i'd like to append an '9' integer to all number records. So, i execute this query
    Why bury that in the SQL statement?
    If you know that all "number" are 4 digits long, you can add a constant integer of 90000, and do an A+B in the calculator step to give you your 9####.
    But really, what you're asking to do is mixing string manipulation with numbers, which is rarely a good idea.

  3. #3
    Join Date
    Oct 2017
    Posts
    3

    Default

    Quote Originally Posted by gutlez View Post
    Why bury that in the SQL statement?
    If you know that all "number" are 4 digits long, you can add a constant integer of 90000, and do an A+B in the calculator step to give you your 9####.
    But really, what you're asking to do is mixing string manipulation with numbers, which is rarely a good idea.
    Hi gutlez, thanks for your reply.

    For sure, there are workarounds for append that 9. But my problem is deeper than that. For instance, LEFT(number,3) should return 123 for the 1234 number, but it is throwing syntax error as well.

    The thing is that i am getting Error 3706 in querys that don't have any

    ps: btw, i've noticed that using || operator for concatenate is working fine in this case.

    ps2: i could use CONCAT function doing this: {CONCAT(9,number)}. How can i get rid of these bothering {}??
    Last edited by Chaskon; 10-26-2017 at 04:19 PM.

  4. #4
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Quote Originally Posted by Chaskon View Post
    For instance, LEFT(number,3) should return 123 for the 1234 number, but it is throwing syntax error as well.
    No. LEFT(1234,3) shouldn't return 123, it should return undefined or error.
    https://info.teradata.com/HTMLPubs/D...l%23wwID0EWCIM

    https://info.teradata.com/HTMLPubs/D...l%23wwID0E1UIM

    LEFT(CAST(1234 as VARCHAR(4),3) should return "123"

    That's what I was meaning about conflating String manipulation and Number manipulation.


    The point about not burying it in the SQL statement is that it will make it easier to maintain by someone else if your logic is clear.

  5. #5
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Quote Originally Posted by Chaskon View Post
    ps: btw, i've noticed that using || operator for concatenate is working fine in this case.

    ps2: i could use CONCAT function doing this: {CONCAT(9,number)}. How can i get rid of these bothering {}??
    According to TeraData documentation, || is the correct concat operator.
    According to https://developer.teradata.com/doc/c....html#BGBDDAFD the {} are a TeraData feature instructing it to "translate" the supplied SQL into its own SQL... That might work in this case because it's seeing the number field and adding the required CAST for you.

    Perhaps try: CONCAT('9',CAST(number as VARCHAR(5)))

  6. #6
    Join Date
    Oct 2017
    Posts
    3

    Default

    Thanks guys, i am much clearer now.

    Since here’s holiday today, i got to try this on next monday :P

    Regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.