data type overflow

[ranga1970]

Code: Select all

CSRFQ..X_RFQ.Out_RFQ_INSERT: DSD.BCIPut call to SQLExecute failed.
SQL statement:INSERT INTO STG_RFQ(SaleProbability, DateProposedEffective, SalesDeadLineDate, EligibleEmployees, CloseType, MemberCount, UserID, ClientID, BrokerID, CloseReason, RFQID, IsLifeQuote, IsDentalQuote, status_ind, RegionKey) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) 
SQLSTATE=22008, DBMS.CODE=0
[DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver]Datetime field overflow

folks this the error i been trying to solve, my source oracle, problem causinf field in the source is of datatype time stamp of 38, but my target is timestamp of datetype 23, 3, taget is SQL serve

there is another data field in the same table which i am passing to target of same where metadata of source is again timestamp 38 and target timestamp 23,3 which is passing without any problems

tthinking that there is some problem in thsi source I tried writng two routean to check validdate

and trimming the date

to trim date the routene used is

A = Iconv(Arg1[1,10], "DMDY")
Ans = OCONV(A, "DMDY")

this works fine if i test at routene level but when i try passing the values ds throws error dattype mismatch

and another routene i wrote is

Code: Select all

if len(Enter.DATE) < 6 
Then 
Ans = -1
End
Else
*DOB = Iconv(Enter.DATE, "D2")
DOB = Iconv(Enter.DATE, "DYMD")
ConvStatus = Status()
Ans = Enter.DATE
*Var1= Oconv(DOB, "D/DYMD")
ConvStatus = Status()
BEGIN CASE
  CASE Enter.DATE = ""
      Ans = -2
  Case ConvStatus = 0
* ...conversion succeeded
Case ConvStatus = 1
    Ans = -3
* ...conversion failed - Entered Date not parsable as a date
Case ConvStatus = 2
    Ans = -4
* ...conversion failed - conversion "D2" invalid (unlikely!)
Case ConvStatus = 3
     Ans = -5
  CASE @True
  Ans = Enter.DATE

END CASE
End

here also ds gives warning

unable to insert values -1,
or -2

-3

-4

etc
and with out even passing any values

could some help me in this regard , i tried different options but i am not able to pass these values, funny part is about 45 thousands are passing then giving datatype overflow if i try passing the field with out any conversion

i am really struck on this issue

[ray.wurlod]

Adjacent to this warning in the log should be another one showing you the actual data in the row to which the ODBC driver objected. If you inspect the data you may be able to spot the offending value.

There are apparently only two Date columns, so it should not be all that difficult to ascertain which is giving the problem.

Your routine considers only dates in YMD order; in some circumstances you may need to consider other orders.

However, when you're selecting from Oracle, the ODBC stage will usually add a TO_DATE function with an ODBC-legal date picture. It may be this that is obscuring your diagnosis. You should be able to view the generated SQL statement in the ODBC stage, or in the job log.

Note, particularly, that the ODBC standard requires YMD order, but you seem to be specifying MDY order in your Iconv() function. This could be expected to fail reliably. Try testing your Routine with the date 2004-12-19.

[ranga1970]

In the generated output at odbc i could not find any to-date conversion
And coming to the routene
before it was

A = Iconv(Arg1[1,10], "DMDY")
Ans = OCONV(A, "DMDY")
i changed to
A = Iconv(Arg1[1,10], "DMDY")
Ans = OCONV(A, "DYMD")
Now i encounter different type of problem

Code: Select all

CSRFQ..X_RFQ.Out_RFQ_INSERT: DSD.BCIPut call to SQLExecute failed.
SQL statement:INSERT INTO STG_RFQ(SaleProbability, DateProposedEffective, SalesDeadLineDate, EligibleEmployees, CloseType, MemberCount, UserID, ClientID, BrokerID, CloseReason, RFQID, IsLifeQuote, IsDentalQuote, status_ind, RegionKey) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) 
SQLSTATE=22005, DBMS.CODE=0
[DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver]Invalid character value for cast specification

and of the two date i could identify which is causing problem but not knowing how to sove the problem(the problem is because oe effective date

it source is from orcle 7.2 with data type datetime of 38 and target is MSSQL of date 23,3 I totally did not understand why datetype is specified as 23,3, the person who designed this had been rolled over, could this be problem??????
if so when try put to management of this problem, they might ask me why other date fileds are passing through though for them source and target are of same type with same data types, so i am exploring all option fo rhtis reason could some one help in this regartd? your help is appriciated

thanks

[ray.wurlod]

SQL Server uses milliseconds in the timestamp format.

There are 23 characters in "YYYY-MM-DD HH:MM:SS.sss".

That's where the (23,3) comes from.

[ranga1970]

Folks,

after doing lot of reasearch I had found the there were three records which were causing the proble to load them

they were 0101/01/01, 0102/01/01, 0102/01/02, fo rhtis i had implimentd dirty solution which works that is I defiend two stage variable
first variable = DateTimeStampToInternal(source.date)
variable1 = Oconv(Variable, "DY")
then
what i did was

Code: Select all

 target.date = If variable1 < 1929 or variable1 > 2500
Then 
"2500-01-01 00:00:00" Else 
 source.date 

I know i only made comparison on the year and it was dirty way of doing, but i tried all other options of making full comparion, could please some one throw light on me about thease kind of things, though my problem is solved i would love to gain some knowledge