if statement problem

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
abyss
Premium Member
Premium Member
Posts: 172
Joined: Thu May 22, 2014 12:43 am

if statement problem

Post by abyss »

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
abyss
Premium Member
Premium Member
Posts: 172
Joined: Thu May 22, 2014 12:43 am

Post by abyss »

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
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

What code is setting the value?
-craig

"You can never have too many knives" -- Logan Nine Fingers
abyss
Premium Member
Premium Member
Posts: 172
Joined: Thu May 22, 2014 12:43 am

Post by abyss »

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
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
abyss
Premium Member
Premium Member
Posts: 172
Joined: Thu May 22, 2014 12:43 am

Post by abyss »

stage variable type is varchar(255)
abyss
Premium Member
Premium Member
Posts: 172
Joined: Thu May 22, 2014 12:43 am

Post by abyss »

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
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
sriven786
Participant
Posts: 37
Joined: Wed Nov 08, 2017 1:36 pm

Post by sriven786 »

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)?.
Venkata Srini
jackson.eyton
Premium Member
Premium Member
Posts: 145
Joined: Thu Oct 26, 2017 10:43 am

Post by jackson.eyton »

This is a pretty common issue for us, we use TRIM() quite extensively for this reason. Let us know what you find out?
-Me
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
sriven786
Participant
Posts: 37
Joined: Wed Nov 08, 2017 1:36 pm

Post by sriven786 »

Thanks, looks like the Stage variable defined as VARCHAR(255) in this case.
Venkata Srini
abyss
Premium Member
Premium Member
Posts: 172
Joined: Thu May 22, 2014 12:43 am

Post by abyss »

i will run some test and let you guys know later today...
abyss
Premium Member
Premium Member
Posts: 172
Joined: Thu May 22, 2014 12:43 am

Post by abyss »

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?
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

No. But simple enough to test.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply