Functional index

Hi All, We are on IDS 9.40.FC6 and IDS 10.00.FC5, HP-UX 11.11 I need to solve a problem with how integers stored as characters ar sorted. I thought to use lpad, but is doesn't seem to work on a character field. So I have a simple function that pads the left of a character field with "0" until the length of the field 20. CREATE FUNCTION lic_presc_num(l_num CHAR(20)) RETURNING CHAR(20); WHILE LENGTH(l_num) < 20 LET l_num = "0" || l_num; END WHILE; RETURN l_num; END FUNCTION; When I try to create a functional index using this statement: create index lic_presc_num_idx on cust_license (lic_presc_num(license_prescr_num)) I get this error: -9848 Functional key part cannot use a variant function function_name. I haven't been able to find how to create a NOT VARIANT function, although I find it referenced in places. Questions: 1) If there is a better wy to sort let me know. 2) How do I create a functional index for this? 3) How do I make the function not variant. As always, thanks for your assistance.

Hi All, We are on IDS 9.40.FC6 and IDS 10.00.FC5, HP-UX 11.11 I need to solve a problem with how integers stored as characters ar sorted. I thought to use lpad, but is doesn't seem to work on a character field. So I have a simple function that pads the left of a character field with "0" until the length of the field 20. CREATE FUNCTION lic_presc_num(l_num CHAR(20)) RETURNING CHAR(20); WHILE LENGTH(l_num) < 20 LET l_num = "0" || l_num; END WHILE; RETURN l_num; END FUNCTION; When I try to create a functional index using this statement: create index lic_presc_num_idx on cust_license (lic_presc_num(license_prescr_num)) I get this error: -9848 Functional key part cannot use a variant function function_name. I haven't been able to find how to create a NOT VARIANT function, although I find it referenced in places. Questions: 1) If there is a better wy to sort let me know. 2) How do I create a functional index for this? 3) How do I make the function not variant. As always, thanks for your assistance. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. Change CREATE FUNCTION lic_presc_num(l_num CHAR(20)) RETURNING CHAR(20); to CREATE FUNCTION lic_presc_num(l_num CHAR(20)) RETURNING CHAR(20) WITH (NOT VARIANT);

CREATE FUNCTION (...) <return clause> WITH ( NOT VARIANT ) <statements>; or for your existing function: ALTER FUNCTION lic_presc_num WITH ( NOT VARIANT ); Art S. Kagel ----- Original Message ----- From: Anthony Judish <ids@iiug.org> At: 8/09 13:37:28 Hi All, We are on IDS 9.40.FC6 and IDS 10.00.FC5, HP-UX 11.11 I need to solve a problem with how integers stored as characters ar sorted. I thought to use lpad, but is doesn't seem to work on a character field. So I have a simple function that pads the left of a character field with "0" until the length of the field 20. CREATE FUNCTION lic_presc_num(l_num CHAR(20)) RETURNING CHAR(20); WHILE LENGTH(l_num) < 20 LET l_num = "0" || l_num; END WHILE; RETURN l_num; END FUNCTION; When I try to create a functional index using this statement: create index lic_presc_num_idx on cust_license (lic_presc_num(license_prescr_num)) I get this error: -9848 Functional key part cannot use a variant function function_name. I haven't been able to find how to create a NOT VARIANT function, although I find it referenced in places. Questions: 1) If there is a better wy to sort let me know. 2) How do I create a functional index for this? 3) How do I make the function not variant. As always, thanks for your assistance. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

I think this can help you to move further. select lpad(l_num::char(20),0,20) from tabname ; Thanks.. Amitava "kernoal.stephens @autozone.com" <kernoal.stephens To @autozone.com> ids@iiug.org Sent by: cc ids-bounces@iiug. org Subject Re: Functional index [9742] 09/08/2007 23:09 Please respond to ids@iiug.org Hi All, We are on IDS 9.40.FC6 and IDS 10.00.FC5, HP-UX 11.11 I need to solve a problem with how integers stored as characters ar sorted. I thought to use lpad, but is doesn't seem to work on a character field. So I have a simple function that pads the left of a character field with "0" until the length of the field 20. CREATE FUNCTION lic_presc_num(l_num CHAR(20)) RETURNING CHAR(20); WHILE LENGTH(l_num) < 20 LET l_num = "0" || l_num; END WHILE; RETURN l_num; END FUNCTION; When I try to create a functional index using this statement: create index lic_presc_num_idx on cust_license (lic_presc_num(license_prescr_num)) I get this error: -9848 Functional key part cannot use a variant function function_name. I haven't been able to find how to create a NOT VARIANT function, although I find it referenced in places. Questions: 1) If there is a better wy to sort let me know. 2) How do I create a functional index for this? 3) How do I make the function not variant. As always, thanks for your assistance. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. Change CREATE FUNCTION lic_presc_num(l_num CHAR(20)) RETURNING CHAR(20); to CREATE FUNCTION lic_presc_num(l_num CHAR(20)) RETURNING CHAR(20) WITH (NOT VARIANT); ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Hi All, We are on IDS 9.40.FC6 and IDS 10.00.FC5, HP-UX 11.11 I need to solve a problem with how integers stored as characters ar sorted. I thought to use lpad, but is doesn't seem to work on a character field. So I have a simple function that pads the left of a character field with "0" until the length of the field 20. CREATE FUNCTION lic_presc_num(l_num CHAR(20)) RETURNING CHAR(20); WHILE LENGTH(l_num) < 20 LET l_num = "0" || l_num; END WHILE; RETURN l_num; END FUNCTION; When I try to create a functional index using this statement: create index lic_presc_num_idx on cust_license (lic_presc_num(license_prescr_num)) I get this error: -9848 Functional key part cannot use a variant function function_name. I haven't been able to find how to create a NOT VARIANT function, although I find it referenced in places. Questions: 1) If there is a better wy to sort let me know. 2) How do I create a functional index for this? 3) How do I make the function not variant. As always, thanks for your assistance. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. Change CREATE FUNCTION lic_presc_num(l_num CHAR(20)) RETURNING CHAR(20); to CREATE FUNCTION lic_presc_num(l_num CHAR(20)) RETURNING CHAR(20) WITH (NOT VARIANT); ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. Thanks, that took care of it.