Improper data type. Attempting to Cleanup after ABORT

[dadabhoy]

My job aborts with the following message :

Code: Select all

DataStage Job 93 Phantom 3672
Program "JOB.1763345848.DT.1483856758.TRANS1": Line 460, Improper data type.
Attempting to Cleanup after ABORT raised in stage A008_Gen_Postings..A002_Wallpaper_Lookup
DataStage Phantom Aborting with @ABORT.CODE = 3

I have checked the file "JOB.1763345848.DT.1483856758.TRANS1" at Line 460 and it reffers to :

Code: Select all

Pin%%V0S30P5.Column%%54 = (Pin%%V0S30P4.Column%%53 * Pin%%V0S30P2.Column%%6)

These in the same file reffer to :

Code: Select all

* Pin%%V0S30P5.Column%%54 <= A003_IN.GBP
* Pin%%V0S30P4.Column%%53 <= A002_IN.GBP
* Pin%%V0S30P2.Column%%6 <= l_wallpaper_lk.MULTIPLIER

Which would mean :

Code: Select all

A003_IN.GBP = A002_IN.GBP * l_wallpaper_lk.MULTIPLIER

In terms of what the job is doing (ODBC =ODBC Stage, O= OCI , T = Transform) :

Code: Select all

   ODBC
    |
O---T---T---O

Rows coming in look up against the ODBC stage which will return multiple values. From the ODBC stage returns a multipler (Decimal 50 Scale 2) which is multiplied by the GBP (Decimal 50 Scale 2) coming from the OCI Stage.

I have checked the the data in both sources, the multiplier is only populated by either -1 or 1. The GBP values are all numeric of which the maximum is 145021683.83 and min is -167574564.3.

Any idea's ?

**ADDITIONAL INFO**

Noticed on other posts you generaly want 10 lines before and after the issue :

Code: Select all

	Pin%%V0S30P2.Column%%3 = Pin%%V0S30P4.Column%%47
	    IF STAGECOM.TRACE.STATS THEN CALL $PERF.END(-2)
	GET.Pin%%V0S30P2
LOOP

	STAGECOM.PINNO = 3
	    IF STAGECOM.TRACE.STATS THEN CALL $PERF.BEGIN(-3)
	    IF @TRUE THEN
		* Column derivation code for pin A003_IN
		Pin%%V0S30P5.Column%%5 = ('Y')
		Pin%%V0S30P5.Column%%54 = (Pin%%V0S30P4.Column%%53 * Pin%%V0S30P2.Column%%6)
		Pin%%V0S30P5.Column%%55 = (Pin%%V0S30P4.Column%%52 * Pin%%V0S30P2.Column%%6)
		Pin%%V0S30P5.Column%%56 = (Abs(Pin%%V0S30P4.Column%%52))
		Pin%%V0S30P5.Column%%58 = ( IF (Pin%%V0S30P4.Column%%52 * Pin%%V0S30P2.Column%%6) < 0 then '50' else '40')
		Pin%%V0S30P5.Column%%61 = ( IF LEFT(Pin%%V0S30P2.Column%%8, 1) = '3' THEN 'PL' ELSE 'BS')
		Pin%%V0S30P5.Column%%63 = (rGTUploadYN(Pin%%V0S30P4.Column%%44, Pin%%V0S30P4.Column%%41, rQtrType(Pin%%V0S30P4.Column%%49, Pin%%V0S30P4.Column%%50)))
		Pin%%V0S30P5.Column%%64 = ('ORIGINAL POSTING')
		Pin%%V0S30P5.Column%%65 = (@Null)
		Pin%%V0S30P5.Column%%66 = (@Null)
		Pin%%V0S30P5.Column%%67 = (rQtrType(Pin%%V0S30P4.Column%%49, Pin%%V0S30P4.Column%%50))
		Pin%%V0S30P5.Column%%68 = (@Null)

Let me know if you need more !!

[ray.wurlod]

What do rGTUploadYN and rQtrType routines do?

Is Pin%%V0S30P4.Column%%52 guaranteed to be numeric?

[dadabhoy]

rGTUploadYN :

Code: Select all

INNUM = QTR_IN[2,1]
OUTNUM = QTR_FOR[2,1]



If INNUM  > OUTNUM  then
  FinalAnswer= 'P'
end


If INNUM  < OUTNUM  then
  FinalAnswer= 'F'
end


If INNUM  = OUTNUM  then
  FinalAnswer= 'C'
end

Ans = FinalAnswer

rQtrType :

Code: Select all

FinalAnswer = 'Y'

If Est_Indicator = 1 then
 FinalAnswer  = 'N'
 If Left(Entry_Code_Description,3) = 'RES' then
  FinalAnswer  = 'Y'
 End
End

If QTR_TYPE = 'P' then
  FinalAnswer  = 'N'
end

Ans = FinalAnswer 

Checked the Pin%%V0S30P4.Column%%52 field on the database by doing a SUM and it work fine and all the values are numeric.

Just from a process of elimination I @Null the derivations that use the above routines and ran the job and the same error appeared. I may do the same on some of the other derivations to see.

Any ideas ?

[dadabhoy]

I added an additional Transform before and put a 0 value in the fields :

Pin%%V0S30P4.Column%%53 (A002_IN.GBP )
Pin%%V0S30P4.Column%%52 (A002_IN.TC)


Ran the job and it worked fine, this would suggest there is a problem in the data. So whent back chacked and there are either valid numbers or null values. Changed the query to do an NVL and return 0 instead of null.
re ran the job and it failed on exactly the same thing.

[srinagesh]

What is the Data type as defined in transformer for

Code: Select all

A002_IN.GBP 

also let us know how are these defined

Code: Select all

A002_IN.GBP and l_wallpaper_lk.MULTIPLIER 

Can you also verify the Lookup to the ODBC stage and check that the data types (and also value) of the fields being looked up are compatible.