I am building an interactive report for one of my users. Basically, if you want to use, say, the GPA criterion, then you just put something in the field and it uses it. Otherwise, it ignores it.

The report works GREAT if you use all the criterion.

The problem comes in with ignoring. I have about 6 table joins, and if I ignore one or two of the criterion, then those tables based on the criterion take forever to process since there is nothing limiting the data coming from those tables. Is there a way to add joins if they choose to use the criterion?

Below is my code (I am using html layout painter for the criterion):

-DEFAULT &GPA = 'ALL'
-DEFAULT &LEVL = 'A'
-DEFAULT &TERM_CODE = '200708'
-DEFAULT &AID_YEAR = '0708'
-DEFAULT &CNTY_CODE = 'ALL'
-DEFAULT &MAJR_CODE = 'ALL'
-DEFAULT &MAJR_CODE_2 = 'ALL'
-DEFAULT &SEX = 'A'
-DEFAULT &EFC = 'ALL'
-DEFAULT &STYPE = 'EVERYONE'
JOIN SPRIDEN_PIDM IN SPRIDEN TO SGBSTDN_PIDM IN SGBSTDN AS J0
JOIN SGBSTDN_PIDM IN SPRIDEN TO SHRLGPA_PIDM IN SHRLGPA AS J1
JOIN SHRLGPA_PIDM IN SPRIDEN TO RNVAND1_PIDM IN RNVAND1 AS J2
JOIN RNVAND1_PIDM IN SPRIDEN TO SPBPERS_PIDM IN SPBPERS AS J4
JOIN SPBPERS_PIDM IN SPRIDEN TO SPRADDR_PIDM IN SPRADDR AS J5
JOIN SPRADDR_CNTY_CODE IN SPRIDEN TO STVCNTY_CODE IN STVCNTY AS J6

TABLE FILE SPRIDEN
BY SPRIDEN_LAST_NAME NOPRINT
BY SPRIDEN_FIRST_NAME NOPRINT
BY SPRIDEN_ID AS 'ID'
BY SPRIDEN_LAST_NAME AS 'LAST NAME'
BY SPRIDEN_FIRST_NAME AS 'FIRST NAME'
BY SHRLGPA_GPA AS 'OVERALL GPA'
BY RNVAND1_EFC_AMT AS 'EFC AMOUNT'
BY SGBSTDN_LEVL_CODE AS 'LEVEL CODE'
WHERE SPRIDEN_CHANGE_IND EQ MISSING;
WHERE SHRLGPA_GPA_TYPE_IND EQ 'O';
WHERE RNVAND1_AIDY_CODE EQ '&AID_YEAR';

-FRESHMEN
-IF &STYPE EQ 'EVERYONE' GOTO EVERYONE;
WHERE SGBSTDN_TERM_CODE_ADMIT EQ '&TERM_CODE';
WHERE SGBSTDN_STYP_CODE EQ 'N';

-EVERYONE
-IF &STYPE EQ 'FRESHMEN' GOTO SEX;
WHERE SGBSTDN_TERM_CODE_EFF EQ '&TERM_CODE';

-SEX
-IF &SEX EQ 'ALL' THEN GOTO EFC;
WHERE SPBPERS_SEX EQ &SEX.(OR(FIND SPBPERS_SEX IN SPBPERS)).SEX.;

-EFC
-IF &EFC EQ 'ALL' THEN GOTO LEVL;
WHERE RNVAND1_EFC_AMT LE '&EFC'

-LEVL
-IF &LEVL EQ 'A' THEN GOTO GPA;
WHERE SHRLGPA_LEVL_CODE EQ '&LEVL';
WHERE SGBSTDN_LEVL_CODE EQ '&LEVL';

-GPA
-IF &GPA EQ 'ALL' THEN GOTO CNTY;
WHERE SHRLGPA_GPA GE '&GPA';

-CNTY
-IF &CNTY_CODE EQ 'ALL' THEN GOTO MAJR;
WHERE STVCNTY_DESC EQ &CNTY_CODE.(OR(FIND STVCNTY_DESC IN STVCNTY)).CNTY_CODE.;

-MAJR
-IF &MAJR_CODE EQ 'ALL' THEN GOTO MAJR_2;
WHERE SGBSTDN_MAJR_CODE_1 EQ &MAJR_CODE.(OR(FIND SGBSTDN_MAJR_CODE_1 IN SGBSTDN)).MAJR_CODE.;

-MAJR_2
-IF &MAJR_CODE_2 EQ 'ALL' THEN GOTO DONE;
WHERE SGBSTDN_MAJR_CODE_2 EQ &MAJR_CODE_2.(OR(FIND SGBSTDN_MAJR_CODE_2 IN SGBSTDN)).MAJR_CODE_2.;

-DONE
ON TABLE PCHOLD FORMAT EXL2K
END
-EXIT  

I would think that the technique you are using to bypass screening can be utilised for the JOINs as well. Checking if a &variable has a value if not use a GOTO and bypass the JOIN. Are there any fields coming from those JOINs though, this could upset everything.
Or have I missed something here?

It would be doable to bypass the join for the table, however I don't know how to do that. If that's possible, it would be a great help.

Okay, the same way that you bypass code for screening.

As I don't know your data, I will use a bit of pseudo:

-IF &VAR1 EQ 'ALL' GOTO PASSJOIN1;
JOIN FIELD1 IN FILE1 TO FIELD2 IN FILE2 AS J1
-PASSJOIN1
-IF &VAR2 EQ 'ALL' GOTO PASSJOIN2;
JOIN FIELD2 IN FILE1 TO FIELD3 IN FILE3 AS J2
-PASSJOIN2

ETC.

While that looks to be a great idea, I keep getting an Invalid Expression error on line 14. And I can't find the error.

-DEFAULT &GPA = 'ALL'
-DEFAULT &LEVL = 'A'
-DEFAULT &TERM_CODE = '200708'
-DEFAULT &AID_YEAR = '0708'
-DEFAULT &CNTY_CODE = 'ALL'
-DEFAULT &MAJR_CODE = 'ALL'
-DEFAULT &MAJR_CODE_2 = 'ALL'
-DEFAULT &SEX = 'A'
-DEFAULT &EFC = 'ALL'
-DEFAULT &STYPE = 'EVERYONE'
JOIN SPRIDEN_PIDM IN SPRIDEN TO SGBSTDN_PIDM IN SGBSTDN AS J0
JOIN SGBSTDN_PIDM IN SPRIDEN TO SHRLGPA_PIDM IN SHRLGPA AS J1
JOIN SHRLGPA_PIDM IN SPRIDEN TO RNVAND1_PIDM IN RNVAND1 AS J2
-IF &SEX = 'A' THEN GOTO JOINPASS;
JOIN RNVAND1_PIDM IN SPRIDEN TO SPBPERS_PIDM IN SPBPERS AS J4
-JOINPASS
-IF &CNTY_CODE = 'ALL' THE GOTO REPORT;
JOIN RNVAND1_PIDM IN SPRIDEN TO SPRADDR_PIDM IN SPRADDR AS J5
JOIN SPRADDR_CNTY_CODE IN SPRIDEN TO STVCNTY_CODE IN STVCNTY AS J6
-REPORT
TABLE FILE SPRIDEN
BY SPRIDEN_LAST_NAME NOPRINT
BY SPRIDEN_FIRST_NAME NOPRINT
BY SPRIDEN_ID AS 'ID'
BY SPRIDEN_LAST_NAME AS 'LAST NAME'
BY SPRIDEN_FIRST_NAME AS 'FIRST NAME'
BY SHRLGPA_GPA AS 'OVERALL GPA'
BY RNVAND1_EFC_AMT AS 'EFC AMOUNT'
BY SGBSTDN_LEVL_CODE AS 'LEVEL CODE'
WHERE SPRIDEN_CHANGE_IND EQ MISSING;
WHERE SHRLGPA_GPA_TYPE_IND EQ 'O';
WHERE RNVAND1_AIDY_CODE EQ '&AID_YEAR';
-FRESHMEN
-IF &STYPE EQ 'EVERYONE' GOTO EVERYONE;
WHERE SGBSTDN_TERM_CODE_ADMIT EQ '&TERM_CODE';
WHERE SGBSTDN_STYP_CODE EQ 'N';
-EVERYONE
-IF &STYPE EQ 'FRESHMEN' GOTO SEX;
WHERE SGBSTDN_TERM_CODE_EFF EQ '&TERM_CODE';
-SEX
-IF &SEX EQ 'A' THEN GOTO EFC;
WHERE SPBPERS_SEX EQ &SEX.(OR(FIND SPBPERS_SEX IN SPBPERS)).SEX.;
-EFC
-IF &EFC EQ 'ALL' THEN GOTO EFC2;
WHERE RNVAND1_EFC_AMT LE '&EFC'
-EFC2
-IF &EFC NE 'ALL' THEN GOTO LEVL;
WHERE RNVAND1_EFC_AMT GE '0'
-LEVL
-IF &LEVL EQ 'A' THEN GOTO GPA;
WHERE SHRLGPA_LEVL_CODE EQ '&LEVL';
WHERE SGBSTDN_LEVL_CODE EQ '&LEVL';
-GPA
-IF &GPA EQ 'ALL' THEN GOTO CNTY;
WHERE SHRLGPA_GPA GE '&GPA';
-CNTY
-IF &CNTY_CODE EQ 'ALL' THEN GOTO MAJR;
WHERE STVCNTY_DESC EQ &CNTY_CODE.(OR(FIND STVCNTY_DESC IN STVCNTY)).CNTY_CODE.;
-MAJR
-IF &MAJR_CODE EQ 'ALL' THEN GOTO MAJR_2;
WHERE SGBSTDN_MAJR_CODE_1 EQ &MAJR_CODE.(OR(FIND SGBSTDN_MAJR_CODE_1 IN SGBSTDN)).MAJR_CODE.;
-MAJR_2
-IF &MAJR_CODE_2 EQ 'ALL' THEN GOTO DONE;
WHERE SGBSTDN_MAJR_CODE_2 EQ &MAJR_CODE_2.(OR(FIND SGBSTDN_MAJR_CODE_2 IN SGBSTDN)).MAJR_CODE_2.;
-DONE
ON TABLE PCHOLD FORMAT EXL2K
END
-EXIT

  

-IF &SEX = 'A' THEN GOTO JOINPASS;

should read

-IF &SEX EQ 'A' THEN GOTO JOINPASS;

You more than of these coded like this...

I changed it to EQ and still get this error:

  0 ERROR AT OR NEAR LINE     15  IN PROCEDURE interactFOCEXEC *
 (FOC261) EXPRESSION IS INCOMPLETE BECAUSE AN OPERATION IS MISSING
 

quote:

-IF &CNTY_CODE = 'ALL' THE GOTO REPORT;
-IF &SEX = 'A' THEN GOTO JOINPASS;

Brian, I never use THEN, you have THE:


-IF &CNTY_CODE = 'ALL' GOTO REPORT;

-IF &SEX = 'A' GOTO JOINPASS;

This message has been edited. Last edited by: Tom Flynn,

quote:

-IF &VAR1 EQ 'ALL' GOTO PASSJOIN1;

should be the correct syntax, as you have around your WHERE clauses.

Thanks everyone so far. I think this is my last problem, and it's a puzzler.

Let me show you first my select from my HTML Layout Painter:

 <SELECT id=edit4 style="Z-INDEX: 20; LEFT: 340px; WIDTH: 80px; POSITION: absolute; TOP: 130px; HEIGHT: 60px" tabIndex=20 multiple size=2 name=SEX datatype="0" datafieldtype="CHAR" addalloption="1" sourcetype="typeMaster" requiredfield="200400816" operation="OR" height="205543908" width="0" accept="0" start="fileopen" readOnly="false" hspace="0" maxLength="2147483647" loop="1" CHECKED="false" indeterminate="false" vspace="0" labelid="text9"> 
<OPTION value=A selected displaytext="ALL">ALL
</OPTION> 
<OPTION value=M displaytext="MALE">MALE
</OPTION> 
<OPTION value=F displaytext="FEMALE">FEMALE
</OPTION>
</SELECT>  

I am passing the value A for "ALL". Using XRETRIEVAL and such, I did a -TYPE &SEX, -TYPE &STYPE in my report. It shows up like this:

  
'A'
 EVERYONE
 AGGREGATION DONE ...
 14.37.45 AE    SELECT T1."SPRIDEN_LAST_NAME",T1."SPRIDEN_FIRST_NAME",
 14.37.45 AE   T1."SPRIDEN_ID",T3."SHRLGPA_GPA",T4."RNVAND1_EFC_AMT",
 14.37.45 AE   T2."SGBSTDN_LEVL_CODE", MAX(T1."SPRIDEN_LAST_NAME") FROM
 14.37.45 AE   SATURN.SPRIDEN T1,SATURN.SGBSTDN T2,SATURN.SHRLGPA T3,
 14.37.45 AE   BANINST1.RNVAND1 T4,SATURN.SPBPERS T5 WHERE (T2."SGBSTDN_PIDM"
 14.37.45 AE   = T1."SPRIDEN_PIDM") AND (T3."SHRLGPA_PIDM" =
 14.37.45 AE   T2."SGBSTDN_PIDM") AND (T4."RNVAND1_PIDM" = T3."SHRLGPA_PIDM")
 14.37.45 AE   AND (T5."SPBPERS_PIDM" = T4."RNVAND1_PIDM") AND
 14.37.45 AE   T1."SPRIDEN_CHANGE_IND" IS NULL AND (T2."SGBSTDN_LEVL_CODE" =
 14.37.45 AE   'UG') AND (T2."SGBSTDN_TERM_CODE_EFF" = '200708') AND
 14.37.45 AE   (T3."SHRLGPA_GPA" >= 3) AND (T3."SHRLGPA_LEVL_CODE" = 'UG') AND
 14.37.45 AE   (T3."SHRLGPA_GPA_TYPE_IND" = 'O') AND (T4."RNVAND1_EFC_AMT" <=
 14.37.45 AE   20000) AND (T4."RNVAND1_AIDY_CODE" = '0708') AND
 14.37.45 AE   (T5."SPBPERS_SEX" = 'A') GROUP BY T1."SPRIDEN_LAST_NAME",
 14.37.45 AE   T1."SPRIDEN_FIRST_NAME",T1."SPRIDEN_ID",T3."SHRLGPA_GPA",
 14.37.45 AE   T4."RNVAND1_EFC_AMT",T2."SGBSTDN_LEVL_CODE" ORDER BY
 14.37.45 AE   T1."SPRIDEN_LAST_NAME",T1."SPRIDEN_FIRST_NAME",T1."SPRIDEN_ID",
 14.37.45 AE   T3."SHRLGPA_GPA",T4."RNVAND1_EFC_AMT",T2."SGBSTDN_LEVL_CODE";
 ...RETRIEVAL KILLED
 0 NUMBER OF RECORDS IN TABLE=        0  LINES=      0
 

Why would the A have '' around it? Everyone doesn't, and it's being passed via radio button:

 <INPUT id=text11_0 type=radio CHECKED value=EVERYONE name=STYPE displaytext="EVERYONE"> 
 

In the end, I am troubleshooting with this statement, and apparently 'A' isn't the value for &SEX that's being passed.

-IF &SEX EQ 'A' GOTO EXIT;


Does this make sense?

quote:

-IF &SEX EQ 'A' GOTO EXIT;

Brian, try:

-IF '&SEX.EVAL' EQ 'A' GOTO EXIT;

Also, forgot to mention, if the &VAR comes back with quotes, it's a Multi-Select, Multiple in the Painter...

This message has been edited. Last edited by: Tom Flynn,

ok, this is what I got.

  'A'
 EVERYONE
 0 ERROR AT OR NEAR LINE     10  IN PROCEDURE interactFOCEXEC *
 (FOC257) MISSING QUOTE MARKS:  ''A'' EQ 'A' GOTO E
 

So, if it is a multi-select, what do I do to eval this thing.

So, it is coming back as 'A'.

Remove the quotes and .EVAL.

-SET &X_SEX = &SEX;
-IF &X_SEX EQ 'A' GOTO EXIT;

Also, did you notice the SQL code:

(T5."SPBPERS_SEX" = 'A')

funsutton,

If it were me I would create a cluster join, this way WebFOCUS will join the table together and only the table that are needed, for each request giving you better SQL. I would also use FOC_NONE as the value for each variable that should be ignored.

Example.

 <SELECT id=edit4 style="Z-INDEX: 20; LEFT: 340px; WIDTH: 80px; POSITION: absolute; TOP: 130px; HEIGHT: 60px" tabIndex=20 multiple size=2 name=SEX datatype="0" datafieldtype="CHAR" addalloption="1" sourcetype="typeMaster" requiredfield="200400816" operation="OR" height="205543908" width="0" accept="0" start="fileopen" readOnly="false" hspace="0" maxLength="2147483647" loop="1" CHECKED="false" indeterminate="false" vspace="0" labelid="text9"> 
<OPTION value="FOC_NONE" selected displaytext="ALL">ALL
</OPTION> 
<OPTION value=M displaytext="MALE">MALE
</OPTION> 
<OPTION value=F displaytext="FEMALE">FEMALE
</OPTION>
</SELECT>  


in the table request

WHERE SEX EQ &SEX ;

if the value of &SEX is FOC_NONE WebFOCUS will ignore the entire line, No where statement will be generated or set to the RDBMS.

Hope this helps

quote:

<SELECT id=edit4 style="Z-INDEX: 20; LEFT: 340px; WIDTH: 80px; POSITION: absolute; TOP: 130px; HEIGHT: 60px" tabIndex=20 multiple

Brian, see the word

quote:

multiple

in the SELECT statement; this is caused by

WHERE SPBPERS_SEX EQ &SEX.(OR(FIND SPBPERS_SEX IN SPBPERS)).SEX.;


Take out the (OR and it becomes single select.

Then,

-IF &SEX = 'A' GOTO JOINPASS;

should work fine.

If not, do the .EVAL code...

This message has been edited. Last edited by: Tom Flynn,

Brian, idea that is less work than branching:
-DEFAULT &CNTY_CODE = 'ALL' ;
then add
-SET &COMMENTCC = IF &CNTY_CODE IS 'ALL' THEN '-*' ELSE ' ' ;
then for the join
&COMMENTCC.EVAL JOIN SPRADDR_CNTY_CODE IN SPRIDEN TO STVCNTY_CODE IN STVCNTY AS J6
this comments out the JOIN line if the CNTY_CODE isn't specified and the joined file STVCNTY county isn't needed.
A bit cleaner than lots of branching and statement labels.
-s