I'm getting this error:

(FOC336) THE SUM OF ALL FIELD LENGTHS EXCEEDS MAXIMUM.
BYPASSING TO END OF COMMAND

columns add up to 173 bytes of varchar2 fields + date field

how can i bypass this error to get all columns?

Thanks,
Nate

By the way we're on 7.1.4

This message has been edited. Last edited by: Kerry,

quote:

FOC336) THE SUM OF ALL FIELD LENGTHS EXCEEDS MAXIMUM.
BYPASSING TO END OF COMMAND

I remember getting that error a long time ago when we were in the mainframe world. Solved it by changing column headings to be short. Had it happen in WebFOCUS 4.3.6 once as well. If you are using the field names as 'default' headings then try an AS .... with shorter size to pull the dats.

The detailed error message is:

quote:

(FOC336) THE SUM OF ALL FIELD LENGTHS EXCEEDS MAXIMUM.
The sum of the lengths of all fields in a structure exceeds the limit. The default limit is 32,768 bytes. Reduce the number and size of the fields in the file, or eliminate JOINs and cross-references if possible.

My columns only come out to be about 200 bytes.

And it can't be joins, because I can delete a few columns and it works. But like I said my columns only come to 200 bytes.

So is it something with varchar2 columns?

Just a thought. I have not encountered this message so this is just a guess.

I think the error message refers to total length of all the fields in your joined reporting structure and not the total lengths of all fields referenced in your request.

Can we see your request and any joins you have with it?

SELECT spriden_id ID,
spriden_first_name first_name,
spriden_mi middle_name,
spriden_last_name last_name,
spbpers_birth_date birth_date,
spbpers_ethn_code ethn_code,
f_student_get_desc ('STVETHN', spbpers_ethn_code, 30) ethn_desc,
aac003_f.f_get_grade (spriden_pidm, 'ENGL', '110') engl110,
aac003_f.f_get_grade_term (spriden_pidm, 'ENGL', '110') engl110_term,
aac003_f.f_get_grade (spriden_pidm, 'ENGL', '111') engl111,
aac003_f.f_get_grade_term (spriden_pidm, 'ENGL', '111') engl111_term,
aac003_f.f_get_grade (spriden_pidm, 'COMM', '211') comm211,
aac003_f.f_get_grade_term (spriden_pidm, 'COMM', '211') comm211_term,
aac003_f.f_get_grade (spriden_pidm, 'COMM', '225') comm225,
aac003_f.f_get_grade_term (spriden_pidm, 'COMM', '225') comm225_term,
aac003_f.f_get_grade (spriden_pidm, 'EDUC', '115') educ115,
aac003_f.f_get_grade_term (spriden_pidm, 'EDUC', '115') educ115_term,
aac003_f.f_get_grade (spriden_pidm, 'EDUC', '223') educ223,
aac003_f.f_get_grade_term (spriden_pidm, 'EDUC', '223') educ223_term,
aac003_f.f_get_grade (spriden_pidm, 'EDUC', '224') educ224,
aac003_f.f_get_grade_term (spriden_pidm, 'EDUC', '224') educ224_term,
aac003_f.f_get_grade (spriden_pidm, 'EDUC', '320') educ320,
aac003_f.f_get_grade_term (spriden_pidm, 'EDUC', '320') educ320_term
FROM sgbstdn,
spriden,
spbpers
WHERE sgbstdn_term_code_eff = (SELECT MAX (sgbstdn_term_code_eff)
FROM sgbstdn
WHERE sgbstdn_pidm = spriden_pidm)
AND spriden_change_ind IS NULL
AND (sgbstdn_majr_code_conc_1_3 = 'TEDO'
OR sgbstdn_majr_code_conc_1 = 'TEDO'
OR sgbstdn_majr_code_conc_1_2 = 'TEDO'
OR sgbstdn_majr_code_conc_2 = 'TEDO'
OR sgbstdn_majr_code_conc_2_2 = 'TEDO'
OR sgbstdn_majr_code_conc_2_3 = 'TEDO'
OR sgbstdn_majr_code_conc_121 = 'TEDO'
OR sgbstdn_majr_code_conc_122 = 'TEDO'
OR sgbstdn_majr_code_conc_123 = 'TEDO'
OR sgbstdn_majr_code_conc_221 = 'TEDO'
OR sgbstdn_majr_code_conc_222 = 'TEDO'
OR sgbstdn_majr_code_conc_223 = 'TEDO')
AND sgbstdn_pidm IN (
SELECT sfrstcr_pidm
FROM sfrstcr
WHERE sfrstcr_term_code = '&ug_term'
AND (sfrstcr_rsts_code = 'AU'
OR sfrstcr_rsts_code IN (SELECT stvrsts_code
FROM stvrsts
WHERE stvrsts_incl_sect_enrl = 'Y')))
AND spriden_pidm = sgbstdn_pidm
AND spbpers_pidm = sgbstdn_pidm
ORDER BY spriden_last_name, spriden_first_name, spriden_mi, spriden_id;


Here's the AAC003_F.f_get_grade function code
Note the aac003_f.f_get_grade_term is similar just returning the term:

CREATE OR REPLACE FUNCTION AAC003_F.f_get_grade (
p_pidm IN NUMBER,
p_subj IN VARCHAR2,
p_crse IN VARCHAR2)
RETURN VARCHAR2
IS
v_grade VARCHAR2 (6);
BEGIN
SELECT shrtckg_grde_code_final
INTO v_grade
FROM shrtckg,
shrtckn
WHERE shrtckn_subj_code = p_subj
AND shrtckn_crse_numb = p_crse
AND shrtckg_credit_hours > 0
AND shrtckg_gmod_code = 'N'
AND shrtckn_pidm = p_pidm
AND (shrtckn_repeat_course_ind IS NULL
OR shrtckn_repeat_course_ind = 'I')
AND shrtckg_seq_no =
(SELECT MAX (shrtckg_seq_no)
FROM shrtckg
WHERE shrtckg_pidm = p_pidm
AND shrtckg_term_code = shrtckn_term_code
AND shrtckg_tckn_seq_no = shrtckn_seq_no)
AND shrtckg_term_code = shrtckn_term_code
AND shrtckg_pidm = shrtckn_pidm
AND shrtckg_tckn_seq_no = shrtckn_seq_no;

*****Note I can take a few(9) of those functions out of the select and it will work.

Thanks for the help.

Nate

I notice that your stored proc is not doing
RETURN v_grade at the end so the length of the returned item is undefined and wf might default this to a large value.

Try ?FF SQLOUT immediately after you do the passthru to look at the fields wf has generated.

John

John,

Actually the procedure did have a RETURN v_grade is was just cut off from the post.

I tried ?FF SQLOUT and it's giving me this error:
(FOC336) THE SUM OF ALL FIELD LENGTHS EXCEEDS MAXIMUM.
NO DATA FILE IS CURRENTLY ACTIVE
(FOC1517) UNRECOGNIZED COMMAND ON TABLE SET XRETRIEVAL OFF

Thanks for the help,
Nate

Hi Nate

Try cutting back the number of calls to your stored proc so it works and then have a look at the ?FF output to see what size wf has set its version of the variable to.

I would guess its got A4096Vs.

Again I cant remember if you can do this in Oracle but can't see why not

CREATE OR REPLACE FUNCTION AAC003_F.f_get_grade (
p_pidm IN NUMBER,
p_subj IN VARCHAR2,
p_crse IN VARCHAR2)
RETURN VARCHAR2(6)

This will let wf explicitly know that you're return 6 bytes.

Regards

John

John,

Thanks for your help.

The length was A4000V for each of the columns calling functions.

Even though the variable that is being returned is declared as varchar2 (6), it's returning varchar2 (4000).

I tried what you advised: RETURN VARCHAR2(6) and even looking in Oracle docs. Nothing.

So what I did to make it work was to use Oracle's substr function to trim the columns calling each function. Works like a charm.

Thanks again,
Nate

Nice One

Using substr(fieldname,n,n) in the Oracle sql being passed worked for me, too - after an afternoon of scratching my head.

What's odd to me is that Oracle's rtrim function didn't work, and trimspool setting didn't work, either.

Isn't there a way to change some global WebFOCUS setting that's making fields 4000 characters long?

Do a search on VARCHAR. I think you can SET VARCHAR OFF in your program, but it is more effective if you do it for all files in the eda profile. You will need to regenerate your masters. Not turning VARCHAR OFF does affect SUBSTR in a passthru.

Thanks for the information.... the function my user was using return 512 and they didn't want to reduce it. So, we put TRIM(function) around the function and it worked.

Just wanted to share.

Thanks,
--wg