DSXchange

Simple - concatenate a zero time onto the end of your date: YourField : " 00:00:00" I was just searching for a function to convert String to Date. But couldn't find it. Are there any functions that will convert string to date? I have cracked the problem. The date fields are declared as DA...

chulett wrote:Simple - concatenate a zero time onto the end of your date:

Code: Select all

YourField : " 00:00:00"

I was just searching for a function to convert String to Date. But couldn't find it. Are there any functions that will convert string to date?

Write a shell script to line count (wc -l) the file and then issue head -n??? where ??? is the result from the line count operation minus 1. Put this in the filter portion of the sequential stage. Hi there, I tried the same logic yesterday and it worked FILE=$1 ROWS=`wc -l $FILE | awk '{print $1}'`...

chulett wrote:Simple - concatenate a zero time onto the end of your date:

Code: Select all

YourField : " 00:00:00"

I Tried concatenating zero to the date field. I tried even converting String to Date format. I don't know why it is still giving me the same error

Look at the generated SQL in the Oracle stage. Note the format it expects the DATE fields declared as Timestamps to come in as. Transform your fields to match that format. Problem goes away. :wink: My input date fields just have a date in them, they don't have timestamp. I tried changing the date t...

The ORAOCI stage adds a To_DATE() to all columns where the datatype is DATE when using generated SQL. You need to make sure your datatypes say DATE and get all your dates into YYYY-MM-DD HH24:MI:SS format, or atleast YYYY-MM-DD in order to take advantage of generated SQL. If you wrote user-sql, it'...

Hi, I have a customer record in a PIPE delimited Customer File. The record format is as follows 750007|DET|514028073|514028073|01|75|1|M|Mr|M|David |Rewwick|D|Dear Mr Rewwick|Mr D Rewwick|rywyk|dyvyt|RewwickD|22-12-1 962|22|12|NULL|NULL|27 Fernwood Avenue|STREATHAM||London||SW16 1RD|SW161RD|27 Fernw...

Vincent is talking about the Sequential File stage in a Parallel job, which will automagically reject / not process any rows where the metadata doesn't match without raising a fuss. A Server job is different and will complain when there are mismatches. You can handle your problem by switching to th...

You have a option within the parallel sequential file stage to "Read First Rows" that limits the number of rows read from the file. You would need to use a Unix command to count the number of rows, subtract 1 and then pass this into the job as a job parameter, set the "Read First Row...

you need to preprocess the file using OS (unix) commands or a datastage routine, personally I use a DS routine to copy the selected lines (count(lines)-1) to a new file... As I am new to datastage, I really don't know how to add this DS routine as a preprocess, do you mind explaining me this. I mea...

Hi , I have got a problem while connecting to DB2 database thru Db2 stage. and same problem with Oracle database thru Oracle OCI stage. Is there any process to configure the database to connect via datastage? plz give me the solution asap thanks Ravi, did you configure your dsenv file with the orac...

You have a option within the parallel sequential file stage to "Read First Rows" that limits the number of rows read from the file. You would need to use a Unix command to count the number of rows, subtract 1 and then pass this into the job as a job parameter, set the "Read First Row...

Hi, I am uploading a sequential file with around 5 million records to the oracle database. I am using a transformer to do some processing. This is a sample data format 45080469|DET|45080464|60087074|NULL|2|Sep 1 2000 12:00AM|E|M|SYSTEM|09-06-2002|N|END 45080470|DET|45080465|60087074|NULL|2|Oct 5 200...

hi i have tried using If DSLink4.Field002 = "null" then IsNull(DSLink4.Field002 ) else DSLink4.Field002 and it worked it might have worked for you, because you might never had 'null' in input file, otherwise the same error mght have appeared. Actually, I didn't have null in input field, b...

What do you mean by 'work with the sql being generated'? :? You've done the right thing in my book - treat the Oracle DATE field as a Timestamp datatype and format what you received into what Oracle was expecting. I wouldn't go switching to user defined sql or anything. And handling the two pieces ...