UDF question.

[Former Member]

Hello,

I have following code in UDF that pads string to 9 characters long (fills with leading spaces) and adds to SQL query. Please look at the following code.

public void getStatement(String[] companyId,ResultList result,Container container){

int len = companyId.length;

StringBuffer ids = new StringBuffer();

int totalLen = 9;

for (int i=0; i<len;i++)

{

<b> int len2 = companyId<i>.length();

StringBuffer paddedId = new StringBuffer(totalLen);

int pad = totalLen-len2;

while(pad>0)

{

paddedId.append(" ");

pad--;

}

paddedId.append(companyId<i>);</b>

ids.append("'"paddedId.toString()"'");

if(i != len-1)

{

ids.append(",");

}

}

result.addValue("selectemp_no, emp_name from employee where emp_no in ("ids.toString()")");

}

I tested above function using test mapping. The following is the output.

16:57:44 Start of test

Document start

Start tag [ns1:MT_Cma_Customer_Select_Request]

Add raw attribute [ xmlns:ns1="http://freemanco.com/xi/Get_Customer"]

Start tag [Statement]

Start tag [LawsonCustomer]

Add attribute: [action]=[SQL_QUERY]

Start tag [access]

Put value [<b>select emp_no, emp_name from employee where emp_no in(' 45632')</b>

Close tag [access]

Close tag [LawsonCustomer]

Close tag [Statement]

Close tag [ns1:MT_Cma_Customer_Select_Request]

Document end

Executed successfully

16:57:44 End of test

As you see the bold code is used to pad leading spaces and after test the I do not see leading spaces in the select statement.

Can some tell me what am I doing wrong?.

Thank you for the help.

Balaji

[justin_santhanam · Accepted solution]

Balaji,

What is the exact reqmt, for the company id length must be 9 ,if not pad with spaces am I right? why u are using Advanced UDF -Queue, you can use standard UDF iteslf. Can u give the reqmt, so that it could be easy to suggest.

-raj.

[Former Member · Accepted solution]

Raj,

This is related to my question

However the requirement is when I pass in emp_no 45632, it needs to be padded with leading spaces until total length equlas to 9 characters ( 45632).

The eample in the posting, <b>emp_no in ('45632')</b>. However after padding to 9 characters, it looks like <b>emp_no in (' 45632')</b>

Please let me know if you need more information.

Thank you,

Balaji

[justin_santhanam · Accepted solution]

Balaji,

Use the below code and let us know if it helps.

Employee_Number [Change Context]-->UDF---->Access

Lets consider input argument name is<b> test</b>.

StringBuffer sb= new StringBuffer();
sb.append("Select * from employee_tbl where employee_id in (");
                                int len;
		for(int m=0;m<test.length;m++)
		{
			len= test[m].length();
			sb.append(test[m]);
			for(int k=len;k<=9;k++)
			{
				if(!(len==9))
				sb.append(" ");
			}
		
			if(m==test.length-1)
			{
				sb.append(");");
			}
			else
			{
				sb.append(",");
			}
						
		}
		result.addValue(""+sb.toString()+"");

Best regards,

raj.

[Former Member · Accepted solution]

Hi

Try this out in UDF. Pass the corresponding string to UDF

length_string = str1.length(); // length of string passed to UDF

int length_const = 9;

if (length_string< length_const) {

for(i=0; i< (length_const - length_string); i++) {

Str1 = " "+Str1; // leading space

}

return Str1;

}

[justin_santhanam · Accepted solution]

Balaji,

I'm sorry u want Leading spaces right? My program will give u Trailing spaces. So change one line of code.

Remove sb.append(test[m]); before the 2'nd for loop and add the same in the end of seconf for loop. So now ur code b'come

StringBuffer sb= new StringBuffer();
sb.append("Select * from employee_tbl where employee_id in (");
                                int len;
		for(int m=0;m<test.length;m++)
		{
			len= test[m].length();
			
			for(int k=len;k<=9;k++)
			{
				if(!(len==9))
				sb.append(" ");
			}
		                sb.append(test[m]);
			if(m==test.length-1)
			{
				sb.append(");");
			}
			else
			{
				sb.append(",");
			}
						
		}
		result.addValue(""+sb.toString()+"");

Best regards,

raj.

[Former Member · Accepted solution]

Raj & Mugdha,

Thank you for your response, I tried both of your suggestions. I do not see any difference in outcome. When I ran mycode and your code in a java class outside XI tool, I see out come is padded with appropriate number of leading empty spaces. However the same code is not producing all needed lead spaces. The string looks like as follows.

<b>Select * from employee_tbl where employee_id in (' 6547')</b>

As you can see it has only one lead space rather 5 empy lead spaces. I tried different ways. Not sure why can't I see all lead spaces.

Thank you,

Balaji

[justin_santhanam · Accepted solution]

Balaji,

Right click on the UDF and select Display Queue and see the results. I'm sure it will work.

-raj.

[Former Member · Accepted solution]

hi balaji,

try this way

//9 spaces to a var

padspace = " "

totlen = 9;

for (int i=0;i<a.length;i++)

{

String val = padspace.substring(0,totlen-a<i>.length()) + a<i>;

}

[Former Member · Accepted solution]

Hi Balaji,

Instead of padding the emp_id in the UDF you can try to pad it using the "FormatNum" standard function under Arithmetic group and then pass the padded output to form the query string.

FormatNum

Converts I according to a pattern that you define using the function properties. The possible patterns are the same as in the Java class java.text.DecimalFormat.

http://help.sap.com/saphelp_nw70/helpdata/en/ae/d03341771b4c0de10000000a1550b0/frameset.htm

[Former Member · Accepted solution]

Thank you all for suggestions. The original code I have is working, however I can't see when I tested mapping. I checked mapping queue as suggested by Raj and looks like the padding is fine.

Balaji