By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,661 Members | 781 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,661 IT Pros & Developers. It's quick & easy.

Error in following query

P: 1
CREATE OR REPLACE FUNCTION efi_etl.AAA(in SYSID varchar, in periods varchar) RETURNS VOID AS '
DECLARE walker integer; startPeriod integer; endPeriod integer; tmpStr varchar; periodStr varchar; periodLabel varchar;
BEGIN
periodStr = periods;
if (periodStr != '''') AND (substr(periodStr, 1, 1) != ''0'') then
periodStr = ''0;'' || periodStr;
end if;
walker = 1;
LOOP
tmpStr = split_part(periodStr, '';'', walker);
if tmpStr = '''' then
startPeriod = 0;
endPeriod = 9999;
else
startPeriod = cast(tmpStr as integer);
if (startPeriod = 0) then
tmpStr = split_part(periodStr, '';'', walker + 1);
if ((tmpStr = '''') OR (tmpStr = ''0'')) then
endPeriod = 9999;
else
endPeriod = cast(tmpStr as integer);
end if;
else
startPeriod = startPeriod + 1;
tmpStr = split_part(periodStr, '';'', walker + 1);
if (tmpStr = '''') then
endPeriod = 9999;
else
endPeriod = cast(tmpStr as integer);
end if;
end if;
end if;
if ((startPeriod = 0) AND (endPeriod = 9999)) then
periodLabel = ''Current'';
elseif (endPeriod = 9999) then
periodLabel = ''Over '' || cast(startPeriod as varchar);
else
periodLabel = cast(startPeriod as varchar) || '' to '' || cast(endPeriod as varchar);
end if;
insert into efi_etl."T$AgingPeriodAP" (systemid, startingperiod, endingperiod, label)
values (SYSID, startPeriod, endPeriod, periodLabel);
if (endPeriod = 9999) then
exit;
else
walker = walker + 1;
end if;
END LOOP;
END;
' LANGUAGE plpgsql VOLATILE;

When I execute the above function with hard-coded values - it runs
example:- select AAA('ABC','0;30')

But when I run the same with database values - it fails
example:-
Select
APC."System-ID" as SYSID,
PRO_ELEMENT(APC."Aging-Categories", 1, 1) as AGING1,
PRO_ELEMENT(APC."Aging-Categories", 2, 2) as AGING2,
PRO_ELEMENT(APC."Aging-Categories", 3, 3) as AGING3,
PRO_ELEMENT(APC."Aging-Categories", 4, 4) as AGING4,
PRO_ELEMENT(APC."Aging-Categories", 5, 5) as AGING5
from PUB."AP-Ctl" APC
where 1=1

select AAA((SYSID, 'AGING1 || '';'' || AGING2 || '';'' || AGING3 || '';'' || AGING4 || '';'' || AGING5'))
Oct 5 '07 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 700
But when I run the same with database values - it fails
example:-
Select
APC."System-ID" as SYSID,
PRO_ELEMENT(APC."Aging-Categories", 1, 1) as AGING1,
PRO_ELEMENT(APC."Aging-Categories", 2, 2) as AGING2,
PRO_ELEMENT(APC."Aging-Categories", 3, 3) as AGING3,
PRO_ELEMENT(APC."Aging-Categories", 4, 4) as AGING4,
PRO_ELEMENT(APC."Aging-Categories", 5, 5) as AGING5
from PUB."AP-Ctl" APC
where 1=1

select AAA((SYSID, 'AGING1 || '';'' || AGING2 || '';'' || AGING3 || '';'' || AGING4 || '';'' || AGING5'))
I am not sure what the above code sholud do?
Could u explain it (i know what are select, as, from , where)?
Oct 8 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.