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.txtSELECT 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 defined. 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 ENDOr 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 '?').
Hello,
I am able to import timestamp from the file successfully. However, I am not able to compare it with a timstamp field on a table. Can some please help?
Here is the script:-
USING IN_LAST_UPDATE_DATE (VARCHAR(19))
SEL :IN_LAST_UPDATE_DATE,CAST(:IN_LAST_UPDATE_DATE AS TIMESTAMP(0))
,CAST(:IN_LAST_UPDATE_DATE AS TIMESTAMP(0)) ;
*** Query completed. One row found. 3 columns returned.
*** Total elapsed time was 1 second.
IN_LAST_UPDATE_DATE IN_LAST_UPDATE_DATE IN_LAST_UPDATE_DATE
------------------- ------------------- -------------------
2011-07-14 18:52:00 2011-07-14 18:52:00 2011-07-14 18:52:00
SEL EDW_UPD_TS FROM FDS_ETL_TARGET.QLT_INVSTGTN_F GROUP BY 1;
*** Query completed. One row found. One column returned.
*** Total elapsed time was 6 seconds.
EDW_UPD_TS
-------------------
2011-04-11 04:22:31
SELECT F.PARNT_ID PARNT_PR_ID
FROM FDS_ETL_TARGET.QLT_INVSTGTN_F F
--and F.EDW_UPD_TS >= CAST(:IN_LAST_UPDATE_DATE AS TIMESTAMP(0));
and F.EDW_UPD_TS >= :IN_LAST_UPDATE_DATE;
*** Failure 3857 Cannot use value (or macro parameter) to match 'IN_LAST_UPDATE_DATE'.
USING is only for the next *request*, looks like you try to use it in another request.
USING IN_LAST_UPDATE_DATE (VARCHAR(19))
SELECT F.PARNT_ID PARNT_PR_ID
FROM FDS_ETL_TARGET.QLT_INVSTGTN_F F
WHERE F.EDW_UPD_TS >= CAST(:IN_LAST_UPDATE_DATE AS TIMESTAMP(0));
Dieter
gotcha...thanks.. :)
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.