Get Data Moving 1
Results 1 to 7 of 7

Thread: concatenation results in Null column values

  1. #1
    brian is offline Junior Member
    Join Date
    Nov 2014
    Posts
    5
    Rep Power
    0

    concatenation results in Null column values

    Hi everyone,

    We are just beginning using Cloudbeam to load data from our SQL instance into Amazon Redshift, and are having two issues that have been keeping us from progressing further into our proof of concept.

    The first issue is that, while trying to perform a simple concatenation of two columns, the computed column contains Null values. We have a simple table Person on the source DB (SQL 2012) that has FirstName and LastName columns. We are trying to compute a NAME column on the target (Redshift) with the expression $LastName || ", " || $FirstName under table settings. Evaluating the expression in the Expression Builder works without a problem. The FirstName and LastName columns end up at the target, and the NAME column is created, but populated with Null values. All values are set to type String(50).


    The second issue (which seems to be unrelated) is that we occasionally see the following sequence of errors in the log (we are unsure if this is random or related):
    Failed to execute statement: 'CREATE TABLE "attrep_apply_exceptions" ( "TASK_NAME" VARCHAR(384) NOT NULL, "TABLE_OWNER" VARCHAR(384) NOT NULL, "TABLE_NAME" VARCHAR(384) NOT NULL, "ERROR_TIME" TIMESTAMP NOT NULL, "STATEMENT" VARCHAR(8192) NOT NULL, "ERROR" VARCHAR(8192) NOT NULL )'

    RetCode: SQL_ERROR SqlState: 42P07 NativeError: 7 Message: ERROR: Relation "attrep_apply_exceptions" already exists;
    Error while executing the query

    Failed to execute create table statement: CREATE TABLE "attrep_apply_exceptions" ( "TASK_NAME" VARCHAR(384) NOT NULL, "TABLE_OWNER" VARCHAR(384) NOT NULL, "TABLE_NAME" VARCHAR(384) NOT NULL, "ERROR_TIME" TIMESTAMP NOT NULL, "STATEMENT" VARCHAR(8192) NOT NULL, "ERROR" VARCHAR(8192) NOT NULL )

    Cannot create Special table


    We've combed through the documentation but did not find anything related to either of these two issues, and we were equally unable to find anything through search engines, so we were hoping someone here may have come across this and could offer some guidance.

    Cloudbeam Configuration 1.1.0.36
    Attunity Transfer Plugin 1.1.0.36
    Attunity Replicate 4.0.0.74 x64

    Many thanks,
    Brian

  2. #2
    stevenguyen is offline Senior Member
    Join Date
    May 2014
    Posts
    297
    Rep Power
    6
    Hello Brian,

    1. The first issue is that, while trying to perform a simple concatenation of two columns, the computed column contains Null values. We have a simple table Person on the source DB (SQL 2012) that has FirstName and LastName columns. We are trying to compute a NAME column on the target (Redshift) with the expression $LastName || ", " || $FirstName under table settings. Evaluating the expression in the Expression Builder works without a problem. The FirstName and LastName columns end up at the target, and the NAME column is created, but populated with Null values. All values are set to type String(50).

    1ans. I tested with String and that did not work. However, when I set the new NAME column to WString, that works without issue.

  3. #3
    stevenguyen is offline Senior Member
    Join Date
    May 2014
    Posts
    297
    Rep Power
    6
    Hello Brian,

    2. The second issue (which seems to be unrelated) is that we occasionally see the following sequence of errors in the log (we are unsure if this is random or related):
    Failed to execute statement: 'CREATE TABLE "attrep_apply_exceptions" ( "TASK_NAME" VARCHAR(384) NOT NULL, "TABLE_OWNER" VARCHAR(384) NOT NULL, "TABLE_NAME" VARCHAR(384) NOT NULL, "ERROR_TIME" TIMESTAMP NOT NULL, "STATEMENT" VARCHAR(8192) NOT NULL, "ERROR" VARCHAR(8192) NOT NULL )'

    RetCode: SQL_ERROR SqlState: 42P07 NativeError: 7 Message: ERROR: Relation "attrep_apply_exceptions" already exists;
    Error while executing the query

    Failed to execute create table statement: CREATE TABLE "attrep_apply_exceptions" ( "TASK_NAME" VARCHAR(384) NOT NULL, "TABLE_OWNER" VARCHAR(384) NOT NULL, "TABLE_NAME" VARCHAR(384) NOT NULL, "ERROR_TIME" TIMESTAMP NOT NULL, "STATEMENT" VARCHAR(8192) NOT NULL, "ERROR" VARCHAR(8192) NOT NULL )

    2ans. I was able to duplicate the issue, and has reported to R&D and will keep you posted. However, The task should be running without any issue. The error seems to be only when you stop the task.
    Last edited by stevenguyen; 11-24-2014 at 12:55 PM.

  4. #4
    brian is offline Junior Member
    Join Date
    Nov 2014
    Posts
    5
    Rep Power
    0
    Quote Originally Posted by stevenguyen View Post
    Hello Brian,

    1. The first issue is that, while trying to perform a simple concatenation of two columns, the computed column contains Null values. We have a simple table Person on the source DB (SQL 2012) that has FirstName and LastName columns. We are trying to compute a NAME column on the target (Redshift) with the expression $LastName || ", " || $FirstName under table settings. Evaluating the expression in the Expression Builder works without a problem. The FirstName and LastName columns end up at the target, and the NAME column is created, but populated with Null values. All values are set to type String(50).

    1ans. I tested with String and that did not work. However, when I set the new NAME column to WString, that works without issue.
    Hi Steve,

    Thanks so much for the quick reply. Unfortunately, we are having the same issue even after converting the target column type to WString. I also tried re-creating the job with just the one single table and the calculated column is still full of Null values. The source database columns are of type nvarchar(150), not nullable. Is there any other information I might be able to provide to help isolate the problem?

    Thank you,
    Brian

  5. #5
    stevenguyen is offline Senior Member
    Join Date
    May 2014
    Posts
    297
    Rep Power
    6
    Quote Originally Posted by brian View Post
    Hi Steve,

    Thanks so much for the quick reply. Unfortunately, we are having the same issue even after converting the target column type to WString. I also tried re-creating the job with just the one single table and the calculated column is still full of Null values. The source database columns are of type nvarchar(150), not nullable. Is there any other information I might be able to provide to help isolate the problem?

    Thank you,
    Brian
    Hello Brian,

    1. instead of manually converting the type to WString on Redshift, could you remove the column.

    2. Try to run a test task with just the FirstName and LastName column with the new concatenation.

    See my attached screen-shot and it works without issue.

    Name:  concatenation.PNG
Views: 1092
Size:  66.8 KB

  6. #6
    brian is offline Junior Member
    Join Date
    Nov 2014
    Posts
    5
    Rep Power
    0
    Quote Originally Posted by stevenguyen View Post
    Hello Brian,

    1. instead of manually converting the type to WString on Redshift, could you remove the column.

    2. Try to run a test task with just the FirstName and LastName column with the new concatenation.

    See my attached screen-shot and it works without issue.

    Name:  concatenation.PNG
Views: 1092
Size:  66.8 KB

    Hi Steve,

    I converted it in the task, not directly on redshift, and did a full reload, which did not work. Then I dropped the column and tried again, with the same result. I also dropped the entire database and tried again from scratch. For some reason this is just not working for us. Is there any logging I can turn on that would show us a detailed output of what was getting pulled from the source, what was getting calculated, and what was getting loaded into AWS?

    Thanks,
    Brian

  7. #7
    stevenguyen is offline Senior Member
    Join Date
    May 2014
    Posts
    297
    Rep Power
    6
    Hello Brian,

    From our Web Session from yesterday:

    We discover that your source Data Types is NVARCHAR(MAX) in Attunity Replicate these become NCLOB.

    LOBs are special objects and very limited on what Replicate can do with them. These LOBs can not be concatenate.

Tags for this Thread

Posting Permissions

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