0

Recently I've moved one Teradata test table data to bigquery and I see the row count difference between TD and BQ. As I checked further, I see one of the row value is in "DATE"format instead of "String" because that column is PI column and the data type is VARCHAR. so this row is returning in BQ when I run select but not in TD whereas I see that row when I'm exporting data to excel. I'm really not sure what could be the reason of not showing when I run select statement. Please help me someone to know the reason and also let me know how can I search those problematic data when table is too big. Thanks. eg : create multiset table Test(a int,b varchar,c varchar,d timestamp(6)) primary index (b);

Data like below in that table.

enter image description here

prathap chowdary
  • 1
  • 1
  • A VarChar has no format, both 'Name' and '8/27/2020' are just strings. You may consider #2 a date, but for the DBMS it's just a series of characters. If Teradata doesn't return this row it's because of a WHERE-condition. Simply run a `select * from tab` to get all rows. – dnoeth Oct 14 '21 at 13:09
  • Hi Dnoeth, I'm not using any where condition but that row is skipping when I select * from tab. However that row is exporting to excel and same row I can see it in BQ. – prathap chowdary Oct 14 '21 at 14:04
  • Your question makes no sense. What do you mean a row shows up when you export to Excel but not when you query the Teradata table? That sounds pretty impossible to me. – Andrew Oct 14 '21 at 15:09
  • 1
    Andrew is right. Maybe there's a linebreak in a VarChar column which confuses Excel or BQ? – dnoeth Oct 14 '21 at 15:11
  • what does mean of linebreak in a varchar column? please provide more details so that I will check on it because there are plenty of tables like that. Thanks. – prathap chowdary Oct 14 '21 at 21:50
  • `where column like any ('%'||chr(13)||'%', '%'||chr(10)||'%')` https://en.wikipedia.org/wiki/Newline#Representation – dnoeth Oct 15 '21 at 18:12

0 Answers0