Help with Concatenation..

[sujark]

Hi,

I have a select statement like this:

Select
VA.Vndr_No as VendorNo ,
SGV.Vndr_Nm as VendorName ,
VA.Ref_No
Get_Terms( 'S ' , va.ref_no) as Terms,
from Vndr_Att VA , SG_Vendor SGV
where VA.SGV_No = SGV.SGV_No
and VA.cmpny_id = 'S '
order by vndr_no

Get_Terms is a function that Concatenates the Terms into a string from another table:

Function GET_TERMS
( a_cmpny_id vndr_att.cmpny_id%type,
a_ref_no vndr_att.ref_no%type)
RETURN varchar2 IS

v_Terms_Str varchar2(30000);

BEGIN
FOR TermRec IN (SELECT T.Term_nm
FROM VES.Vendor_Terms VT,
VES.Terms T
WHERE (VT.Cmpny_ID = a_Cmpny_ID
AND VT.REF_NO = a_REF_NO
AND VT.Term_ID = T.Term_ID)
ORDER BY T.Term_Nm) LOOP
if v_Terms_str is null then
v_Terms_str := TermRec.Term_Nm ;
else
v_Terms_Str := v_Terms_Str || ', ' || TermRec.Term_Nm ;
end if;
END LOOP;

RETURN v_Terms_Str;

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLCODE || SQLERRM);
END;

Right now, My query returns:

VendorNo VendorName Terms
--------- ---------- -------------
3456 AMLC ROG, Merchandise Pay
8676 New Traders PLC, Total SGV Pay, SPP

I need the query to look like follows:

VendorNo VendorName Terms
--------- ---------- -------------
3456 AMLC ROG , Merchandise Pay, ACC ,
8676 New Traders PLC , Total SGV Pay ,

I need the concatenation to be done in Fixed lengths(size 15). While concatenating, is there a way to convert the value to char(15) and then concatenate? so that the value no matter how small it is, always appears to be as 15.

Your help will be greatly appreciated.

Thanks.

 

[Turkbear]

Which value?
v_Terms_Str ( 15 total)
or
v_Terms_Str ( length 15) AND
TermRec.Term_Nm ( length 15) so that
v_Terms_Str ends up length 30?

 

[sujark]

I need TermRec.Term_Nm to be of size 15.

i.e, If TermRec.Term_Nm has a value 'ABC', I need to change it to 'ABC ' and then concatenate with V_Term_Str.

The datatype of TermRec.Term_Nm in the database is
Varchar2(15).

Thanks.

 

[ociruj]

hi.. ope this may be of help...

v_Terms_Str := rpad(v_Terms_Str, 15) || ', ' || TermRec.Term_Nm

 

[sujark]

That helps. Thanks!