Tom, blaming the Oracle environment for having a unique feature covered by others is correct. But the trick trying to do the same in TD will most likely fail. The varchar inTD removes trailing blanks and the char has them.
I see no way to support trailing blanks in TD. As mentioned before more weird things to be aware of.
You can definitely store trailing blanks into VARCHAR() variables in TD, because I have seen the exact issue that poster is complaining about.
Not sure how easy it would be to get them there if you are loading from SAS though.
First of all thank you everyone for taking out their valuable time and help me out to solve my problem.
Thanks Tom for giving me an idea to deal it on Oracle level by specifying another column of length.
I am not making that space value column as my UPI in landing, but the UPI in fact in landing is a REC_ID column which is a surrogate key.
UPI for that column has been made in the staging area.
Pardon me if I did something wrong as I am a fresher.
I solved the problem by executing a simple pass through query.
This is how I did it.
Correct me if I am wrong but for now the problem has been solved and if anyone wishes the same to replicate the data from the source to the target as the way it was stored in source then one may follow this:
Problem:
Trailing spaces in a column at Oracle source end but not coming in target Teradata table.
Solution:
1. Pass through in Oracle, replace space by '$', insert into Teradata target table.
2. Again pass through in Teradata, Write an update query to replace '$' with spaces.
proc sql;
connect to oracle(......);
insert into tera.tbl1
select * from connection to oracle
(select col1
, replace(col2,' ','$') as col2
, col3
from schema.tbl2
);
disconnect from oracle;
connect to teradata(.....);
execute( update schema.tbl2
set col2 = oreplace(col2,'$',' ')
)by teradata;
execute(commit);
disconnect from teradata;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: