DYNAMIC WHERE STATEMENT

June 02, 2008, 09:40 AM

I have created a hold file with all the countries from the car file...

TABLE FILE CAR
PRINT COUNTRY
ON TABLE HOLD FORMAT ALPHA
END
-RUN

I am trying to dynamically build a where statement to run a
report for only the first three of the countries in the hold file.

-SET &WHERE1 = 'WHERE COUNTRY EQ';
-SET &WHERE2 = &WHERE1 | ' ' | &COUNTRY.&I | 'OR' | ; ...

Any ideas?

Thanks!

June 02, 2008, 09:52 AM

smiths

Here's an example:

TABLE FILE CAR
PRINT COUNTRY
WHERE RECORDLIMIT EQ 3
ON TABLE HOLD FORMAT ALPHA
END
-RUN


-SET &CNTRY = '';
-READ HOLD &CNTRY.10


-SET &STMT = 'WHERE COUNTRY EQ ';
-SET &IDX = 1;

-REPEAT :LOOP WHILE (&IORETURN EQ 0);

-SET &CNTRY = TRUNCATE(&CNTRY);

-SET &STMT = &STMT | '''' | &CNTRY | '''';


-SET &IDX = &IDX + 1;

-SET &STMT = IF &IDX LT 4 THEN &STMT || ' OR ' ELSE &STMT;


-READ HOLD &CNTRY.10

-:LOOP

-TYPE STMT: (&STMT)

Regards,
Sean

------------------------------------------------------------------------
PROD: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
TEST: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode

June 02, 2008, 09:59 AM

Glenda

Here's a very simplicitic method.

TABLE FILE CAR
PRINT COUNTRY
ON TABLE HOLD FORMAT ALPHA
END
-*
TABLE FILE HOLDFILE
PRINT COUNTRY
WHERE RECORDLIMIT EQ 3
ON TABLE HOLD AS CHOLD
END
-*
TABLE FILE YOURFILE
PRINT FIELDS
WHERE COUNTRYFIELD IN FILE CHOLD
END

June 02, 2008, 10:03 AM

Francis Mariani

-SET &ECHO=ALL;

TABLE FILE CAR
PRINT COUNTRY
ON TABLE HOLD AS H001 FORMAT ALPHA
END
-RUN

-SET &SEL_COUNTRY = 'WHERE COUNTRY IN (';

-SET &COUNTER = 1;

-REPEAT END_LOOP1 WHILE &IORETURN EQ 0;
-IF &COUNTER GT 3 GOTO EXIT_LOOP1;

-READ H001 &COUNTRY.A10.
-SET &SEL_COUNTRY = &SEL_COUNTRY | ' ''&COUNTRY.EVAL''';
-SET &COUNTER = &COUNTER + 1;
-END_LOOP1
-EXIT_LOOP1

-SET &SEL_COUNTRY = &SEL_COUNTRY || ');';

-TYPE &SEL_COUNTRY

TABLE FILE CAR
PRINT *
&SEL_COUNTRY
END
-RUN

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

June 02, 2008, 10:12 AM

Doug

I like Glenda's solution. However, couldn't you eliminate one hold file, as follows:

TABLE FILE CAR
PRINT COUNTRY
ON TABLE HOLD FORMAT ALPHA
WHERE RECORDLIMIT EQ 3
ON TABLE HOLD AS COUNTRY
END
-RUN
TABLE FILE YOURFILE
PRINT FIELDS
WHERE COUNTRYFIELD IN FILE COUNTRY
END

June 02, 2008, 11:42 AM

Tomsweb

quote:
Francis Mariani

Thanks everyone.

I like the one by Francis. Now what I want to make a 2nd pass to get the next 2 (or in this case), the remaining countries (values) in the database?

Seems to me I wanna do something with the &lines counter.

Ideas?

June 02, 2008, 11:58 AM

j.gross

Idea:

TABLEF FILE CAR
  LIST COUNTRY
  ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN
TABLE FILE HOLD1 
"HOLD1:"
  PRINT *
END
-RUN
TABLE FILE HOLD1
  PRINT COUNTRY
  IF LIST FROM 4 TO 5
  ON TABLE HOLD AS HOLD2
END
-RUN
TABLE FILE HOLD2 
"HOLD2:"
  PRINT *
END
-RUN
TABLE FILE CAR
  PRINT SEG.MODEL
  BY COUNTRY
  BY CAR
  WHERE COUNTRY IN FILE HOLD2;
END

The LIST verb in the first HOLD request adds a column called LIST to its output, which the WHERE in the second HOLD request references in order to pull the desired instances.

This message has been edited. Last edited by: j.gross,

June 02, 2008, 12:13 PM

Doug

You can use &LINES in your -REPEAT statement which will loop through the -REPEAT as many times as there are &LINEs. The WHERE statement in your initial TABLE FILE CAR can be used as your selection criteria to only get those countries which you desire.

June 02, 2008, 07:33 PM

Danny-SRL

Tom,
Something to show the flexibility of DM:

  
-* File tomsweb.fex
-SET &ECHO=ALL;
-DEFAULT &F=3, &T=4
DEFINE FILE CAR
-* Number the countries
L/I1 WITH COUNTRY = 1+LAST L;
AL/A1=EDIT(L);
-* Create DM variables
DMCOUNTRY/A30 = '-SET &|COUNTRY' | AL | ' = ''' | COUNTRY || ''';';
END
-RUN
-* Save the DM variables in a fex
FILEDEF CSET DISK CSET.FEX
TABLE FILE CAR
PRINT DMCOUNTRY
ON TABLE SAVE AS CSET
END
-RUN
-* Include the variables in the procedure
-INCLUDE CSET
TABLE FILE CAR
PRINT SALES
BY COUNTRY BY BODYTYPE
WHERE COUNTRY IN (
-* Use the variables that you want, here the 3rd and the 4th
-REPEAT #W FOR &I FROM &F TO &T;
'&COUNTRY.&I'
-#W
);
END

June 03, 2008, 10:12 AM

Francis Mariani

Try two loops:

-SET &ECHO=ALL;
-*
-SET &REP_LIMIT = 2;
-*
TABLE FILE CAR
PRINT COUNTRY
ON TABLE HOLD AS H001 FORMAT ALPHA
END
-RUN
-*
-SET &LIMIT = &LINES;
-*
-READ H001 NOCLOSE &COUNTRY.A10.
-*
-REPEAT END_LOOP1 WHILE &IORETURN EQ 0;
-*
-SET &SEL_COUNTRY = 'WHERE COUNTRY IN (';
-SET &COUNTER = 1;
-*
-REPEAT END_LOOP2 &REP_LIMIT TIMES;
-IF &IORETURN NE 0 GOTO EXIT_LOOP2;
-SET &SEL_COUNTRY = &SEL_COUNTRY | ' ''&COUNTRY.EVAL''';
-SET &COUNTER = &COUNTER + 1;
-READ H001 NOCLOSE &COUNTRY.A10.
-END_LOOP2
-EXIT_LOOP2
-*
-SET &SEL_COUNTRY = &SEL_COUNTRY || ');';
-TYPE &SEL_COUNTRY
-*
TABLE FILE CAR
HEADING CENTER
" CAR REPORT BY COUNTRY "
" AS OF: &DATE"
PRINT MODEL
BY COUNTRY
&SEL_COUNTRY
END
-RUN
-END_LOOP1

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server