Bug Tracker Incident #8522

 


Summary
Project
INCDT - xTuple ERP
Incident Category
Bugs
Incident Number
8522
Visibility
public


Product Version
None
Fixed In Version
3.3.0Alpha


Summary
Text > Integer issue


Description

Receiving pgsql error related to the getcustnumberfrominfo function.

ERROR: operator does not exist: text > integer at character 46
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY: SELECT ( $1 != '' AND POSITION(' ' IN $1 > 0))
CONTEXT: PL/pgSQL function "getcustnumberfrominfo" line 77 at IF
QPSQL: Unable to create query

Added parenthesis around the POSITION statement which resolved the error.


Details
Reporter
szuke
CRM Account
Core Services Group, LLC
Status
Closed
Assigned
cryan


Priority
Normal
Severity
Patch
Updated
09-21-11 14:00
Resolution
Fixed



Comments

DateUsernameComment
03/04/11 08:25mfgadmin

Steps to Reproduce:

Import XML using a blank fullname value passed to the getcustnumberfrominfo function.

Here's the updated function...

-- Function: getcustnumberfrominfo(text, text, text, text, text, boolean)

-- DROP FUNCTION getcustnumberfrominfo(text, text, text, text, text, boolean);

CREATE OR REPLACE FUNCTION getcustnumberfrominfo(text, text, text, text, text, boolean)
RETURNS text AS
$BODY$
DECLARE
_email TEXT := COALESCE(UPPER($1), E'');
_company TEXT := COALESCE(UPPER($2), E'');
_first TEXT := COALESCE(UPPER($3), E'');
_last TEXT := COALESCE(UPPER($4), E'');
_fullname TEXT := COALESCE(UPPER($5), TRIM(_first || ' ' || _last));
_generate BOOLEAN := COALESCE($6, FALSE);
_counter INTEGER;
_custcount INTEGER := 0;
_custnumber TEXT;
_candidate TEXT := '';
_loopmax INTEGER := 0;
_minlength INTEGER := 5;
_maxlength INTEGER := 8;
_numformat TEXT := '';
_testme TEXT;
BEGIN
IF (_email != '') THEN
SELECT count(*), cust_number INTO _custcount, _custnumber
FROM custinfo LEFT OUTER JOIN cntct ON (cust_cntct_id=cntct_id)
WHERE (UPPER(cntct_email)=_email)
GROUP BY cust_number;
IF (NOT FOUND) THEN
_custcount := 0;
ELSIF(_custcount = 1) THEN
RETURN _custnumber;
END IF;
END IF;

IF (_company != '') THEN
SELECT count(*), cust_number INTO _custcount, _custnumber
FROM custinfo
WHERE (UPPER(cust_name)=_company)
GROUP BY cust_number;
IF (NOT FOUND) THEN
_custcount := 0;
ELSIF(_custcount = 1) THEN
RETURN _custnumber;
END IF;
END IF;

IF (_fullname = '' AND (_first != '' OR _last != '')) THEN
_fullname := TRIM(_first || ' ' || _last);
END IF;

IF (_custcount <= 0 AND _fullname != '') THEN
SELECT count(*), cust_number INTO _custcount, _custnumber
FROM custinfo
WHERE (UPPER(cust_name)=_fullname)
GROUP BY cust_number;
IF (NOT FOUND) THEN
_custcount := 0;
ELSIF(_custcount = 1) THEN
RETURN _custnumber;
END IF;
END IF;

IF (_custcount > 1) THEN
RAISE EXCEPTION 'Found % possible Customers for % and % and %',
_custcount, _email, _company, _fullname;
END IF;

IF (_custcount <= 0 AND _generate) THEN
IF (_maxlength < _minlength) THEN
RAISE EXCEPTION 'Fix getCustNumberFromInfo: max length < min length';
END IF;

IF (_company != '') THEN
_candidate := _company;
ELSIF (_email != '') THEN
_candidate := SUBSTRING(_email FOR POSITION('@' IN _email) - 1);
ELSIF (_last != '') THEN
_candidate := _last;
IF (_first != '') THEN
_candidate := _candidate || _first;
END IF;
ELSIF (_fullname != '' AND (POSITION(' ' IN _fullname) > 0)) THEN
_candidate := SUBSTRING(_fullname FROM POSITION(' ' IN _candidate) + 1) ||
SUBSTRING(_fullname FOR POSITION(' ' IN _candidate) - 1);
END IF;
WHILE (POSITION(' ' IN _candidate) > 0) LOOP
_candidate := SUBSTRING(_candidate FOR POSITION(' ' IN _candidate) - 1) ||
SUBSTRING(_candidate FROM POSITION(' ' IN _candidate) + 1);
END LOOP;
FOR _counter IN _minlength.._maxlength LOOP
_testme := SUBSTRING(_candidate FOR _counter);
IF (NOT EXISTS(SELECT cust_number
FROM custinfo
WHERE (cust_number=_testme))) THEN
_custnumber := _testme;
EXIT;
END IF;
END LOOP;
IF (_custnumber IS NULL OR _custnumber = '') THEN
IF (LENGTH(_candidate) < _minlength) THEN
_minlength := LENGTH(_candidate);
END IF;
FOR _counter IN _minlength.._maxlength LOOP
_loopmax := _loopmax * 10 + 9;
_numformat := _numformat || '0';
END LOOP;
FOR _counter IN 1.._loopmax LOOP
_testme := SUBSTRING(_candidate FOR _minlength) ||
TRIM(TO_CHAR(_counter, _numformat));
IF (NOT EXISTS(SELECT cust_number
FROM custinfo
WHERE (cust_number=_testme))) THEN
_custnumber := _testme;
EXIT;
END IF;
END LOOP;
END IF;
IF (_custnumber IS NULL OR _custnumber = '') THEN
RAISE EXCEPTION 'Could not generate a new Customer Number';
END IF;
END IF;

IF (_custnumber IS NULL OR _custnumber = '') THEN
RAISE EXCEPTION 'Could not find Customer Number for % and % and %',
_email, _company, _fullname;
END IF;

RETURN _custnumber;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION getcustnumberfrominfo(text, text, text, text, text, boolean) OWNER TO "admin";

03/04/11 08:25mfgadmin

Receiving pgsql error related to the getcustnumberfrominfo function.

ERROR: operator does not exist: text > integer at character 46
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY: SELECT ( $1 != '' AND POSITION(' ' IN $1 > 0))
CONTEXT: PL/pgSQL function "getcustnumberfrominfo" line 77 at IF
QPSQL: Unable to create query

Added parenthesis around the POSITION statement which resolved the error.

08/18/09 14:13gmoskowitz

verified with various manual calls to getcustnumberfrominfo and by trying to import an xml file with no full name field passed to getcustnumberfrominfo.

02/23/09 11:36svn

Revision: 5302
http://postbooks.svn.sourceforge.net/postbooks/?rev=5302&view=rev
Author: cryan
Date: 2009-02-23 16:36:48 +0000 (Mon, 23 Feb 2009)

Log Message:
-----------
Fixed a two typos one where a () brace was in the wrong place and the other where the wrong variable name was being used. Issue #8522.

Modified Paths:
--------------
xtupleserver/trunk/dbscripts/api/functions/getcustnumberfrominfo.sql



Characteristics

Operating System
 
Doc Flag
False
Copyright Assigned
Yes
HaxTuple
No

Files

No Files

Related Documents

TypeNumberDescriptionRelationshipRemove
ProjectXTUPLEAPPSPorted From Mantisx


Subscribers

You do not have permission to view subscribers.


Incident History

DateUsernameFieldChange
12/16/10 09:13acdrupalNewIncident Added

 

mead