Try using either the "Coalesce" function or a "case when ... then ... else ... end" statement... If you still cannot solve your problem, it would be helpful to see the actual script.
Hope it helps.
Well, since I wasn't having much luck I'm now trying merely to insert a null string with bteq. However, I'm doing something wrong, because even that's not working.
All I'm trying to do is use one bteq script to export the data to a flat file, and use another bteq script to import it back. And Multiload/FastExport aren't options, has to be bteq.
Here's what I have. I appreciate any advice on what I'm doing. I hope this is something easy that I'm just missing because I'm a beginner.
--------------------------------------------
My test data is only one row in this table:
CREATE TABLE mydb.bteqtest
(
Number Integer NOT NULL
,Name CHAR(25)
)
UNIQUE PRIMARY INDEX( Number );
Test data:
5 NULL
---------------------------------------------
For my export script:
.LOGON machine/login,pwd;
.EXPORT DATA FILE=results.txt
SELECT TRIM(COALESCE(Number, ''))
||'|'||TRIM(COALESCE(Name, ''))
FROM mydb.bteqtest;
.quit
-------------------------------------------
Note: This script 'appears' to work fine. This isn't to say I'm doing it right: I might be exporting incorrectly and not successfully accounting for nulls. But it doesn't give me any erros when it runs.
Displaying the contents of results.txt shows:
5|
-------------------------------------------
Import script:
.LOGON machine/login,pwd;
.SET NULL AS ''
.IMPORT VARTEXT '|' file = results.txt
.REPEAT *
USING Number (Integer)
,String (CHAR(25))
INSERT INTO mydb.bteqtest( Number
,Name)
VALUES (:Number
,:String);
.QUIT
------------------------------------
This erros out and displays:
+---------+---------+---------+---------+---------+--- ------+---------+----
USING Number (Integer)
,String (CHAR(25))
INSERT INTO mydb.bteqtest( Number
,Name)
VALUES (:Number
,:String);
*** Starting Row 0 at Fri May 5 19:34:55 2006
*** Failure 2673 The source parcel length does not match data that was def
ined.
Statement# 1, Info =1
*** Total elapsed time was 1 second.
*** Warning: Out of data.
*** Finished at Fri May 5 19:34:55 2006
*** Total number of statements: 1, Accepted : 0, Rejected : 1
If you are using VARTEXT format for the input file, all the fields in the USING clause should be declared as VARCHAR, with the max allowable length. You may need to use explicit FORMAT, CAST, etc. to control how Teradata will convert the strings to the appropriate data types, if the default isn't suitable.
But as written, your character field would be loaded with an empty string and padded with spaces. You can translate empty string:
CASE WHEN :String = '' THEN NULL ELSE :String END
Or if an empty string/spaces is a valid value then use COALESCE or CASE on the EXPORT to set the string to some special "flag" value (like '?').
I'm having a problem using BTEQ to import some rows into a table.
I have two bteq scripts, very basic: one exports a table to a flat file, the other imports the data right back from the flat file.
My problem arises with trying to import a row which has a timestamps with a null value. Rows with a null timestamp cause a "6760 Invalid timestamp." error. Both scripts have the statement ".SET NULL AS '?'" at the top of them.
I see Multiload has a NULLIF command, so you can say "NULLIF col01= ''". That's exactly what i want to do, except in BTEQ. Is there a way to do this?
In addition, using FastLoad/FastExport is not an option; this must be done with BTEQ.
Thanks for any advice; this is very frustrating.