DSXchange

Posted: **Mon Dec 18, 2017 12:42 am**

hi
i am having a weird problem in transformer stage:
i created a stage variable: svCoverTypeCode the value can be : 'HA', 'H', 'A' and in the output link's derivation, i create the following if statement

if (svCoverTypeCode = 'HA' or svCoverTypeCode = 'A') then 'Y'
else 'N'

however when the value of svCoverTypeCode value is 'HA' or 'A' the if statement SOMETIMES return 'N'

I am really confused, does anyone know why?
Thanks
Abyss

Posted: **Mon Dec 18, 2017 1:04 am**

hmmmmm...........
update:
i double checked the variable svCoverTypeCode's value
when value set to 'A' or 'H' DS put 2 blanks at the end

does anyone know why? i set this variable type to varchar (255, 0) how come it does it? this is a bit unfair...

cheers
Abyss

Posted: **Mon Dec 18, 2017 8:08 am**

What code is setting the value?

Posted: **Mon Dec 18, 2017 4:29 pm**

Code: Select all

if (LkTfProdDetial.Hospital_ProductTypeID = 3 and LkTfProdDetial.Ancillary_ProductTypeID = 2) or
		LkTfProdDetial.Hospital_ProductTypeID = 1 then 
		'HA'
else if (LkTfProdDetial.Hospital_ProductTypeID = 3) then
		'H'
else if (LkTfProdDetial.Ancillary_ProductTypeID = 2) then
		'A'
else
		'Unknown'

thanks craig

Posted: **Mon Dec 18, 2017 4:36 pm**

What is the data type of the stage variable? If it's Char(3), for example, DataStage will pad to length with the characters defined by APT_STRING_PADCHAR environment variable. You might be better off using VarChar(3) as the stage variable data type.

Posted: **Mon Dec 18, 2017 5:22 pm**

stage variable type is varchar(255)

Posted: **Mon Dec 18, 2017 5:52 pm**

i put the trim() function out side the if statement and it returns right value, problem solved but i am not happy of how ds behaves.
i mark it as work around ...

thanks all

Posted: **Mon Dec 18, 2017 10:47 pm**

Open up a support case, something is not right here. Are you current on your fix packs / patches?

Posted: **Tue Dec 19, 2017 9:04 am**

Tried running simple DS Job with below 2 Columns
Hospital_ProductTypeID,Ancillary_ProductTypeID
3,2
1,8
3,6
7,2
7,7
And it worked as expected.
"Hospital_ProductTypeID","Ancillary_ProductTypeID","svCoverTypeCode","indicator"
"1","8","HA","Y"
"7","2","A","Y"
"3","2","HA","Y"
"3","6","H","N"
"7","7","Unknown","N"

I am not sure if $APT_STRING_PAD_CHAR is applicable for Stage Variables?.

As mentioned, Can you check what's the default value for $APT_STRING_PAD_CHAR (I ran with 0x0 which is default in my case).

Also you mentioned sometimes, could you please clarify if it was working sometimes (Which case)?.

Posted: **Tue Dec 19, 2017 9:41 am**

This is a pretty common issue for us, we use TRIM() quite extensively for this reason. Let us know what you find out?

Posted: **Tue Dec 19, 2017 9:43 am**

sriven786 wrote:I am not sure if $APT_STRING_PAD_CHAR is applicable for Stage Variables?

Yes. However, only applicable for CHAR fields, not VARCHAR fields.

Posted: **Tue Dec 19, 2017 10:24 am**

Thanks, looks like the Stage variable defined as VARCHAR(255) in this case.

Posted: **Wed Dec 20, 2017 7:24 pm**

i will run some test and let you guys know later today...

Posted: **Thu Dec 21, 2017 12:03 am**

Hi all:
I think I find the problem:
i didn't put the full code out. the full code is like:

Code: Select all

	if IsNotNull(LkTfProdDetial.Hospital_ProductTypeID) and IsNotNull(LkTfProdDetial.Ancillary_ProductTypeID) then
		if (LkTfProdDetial.Hospital_ProductTypeID = 3 and LkTfProdDetial.Ancillary_ProductTypeID = 2) or
			LkTfProdDetial.Hospital_ProductTypeID = 1 then 
			'HA'
		else if (LkTfProdDetial.Hospital_ProductTypeID = 3) then
			'H'
		else if (LkTfProdDetial.Ancillary_ProductTypeID = 2) then
			'A'
		else
			'Unknown'
	else if IsNotNull(LkTfProdDetial.Whics_Cover_Type) then
		LkTfProdDetial.Whics_Cover_Type
	else
		'Unknown'

the else if statement made the difference:

Code: Select all

	else if IsNotNull(LkTfProdDetial.Whics_Cover_Type) then
		LkTfProdDetial.Whics_Cover_Type

LkTfProdDetial.Whics_Cover_Type has type varchar(10) and I went back to the very first job that load the data in, then figured out the database table that i load data from has type char(3), despite the table definition on database connector stage also set to varchar(10), the actual value hold inside Whics_Cover_Type field still have empty space at the end when load into DS jobs. stage variable recognise there is a char field in the derivation and return everything in char(3) type......

question: do you think $APT_STRING_PAD_CHAR will fix the problem?

Posted: **Thu Dec 21, 2017 7:40 am**

No. But simple enough to test.

DSXchange

if statement problem

if statement problem