Hello all,

I am very new to WebFOCUS and was wondering if anyone knew how to concatenate the values from amper variables.

Sample code is as follows:

TABLE FILE RANDOM TABLE NAME
PRINT
BLAH1
BLAH2
BLAH3

ON TABLE SUBFOOT
"Prepared on: <+0>&DATE<+0> "
WHERE ( REP_MONTH GE '&START_MONTH.(FIND REP_MONTH IN RANDOM TABLE NAME).Please select a start month:.' ) AND ( REP_YEAR GE '&START_YEAR.(FIND REP_YEAR IN RANDOM TABLE NAME).Please select a start year:.' ) AND ( REP_YEAR LE '&END_YEAR.(FIND REP_YEAR IN RANDOM TABLE NAME).Please select an ending year:.' ) AND ( REP_MONTH LE '&END_MONTH.(FIND REP_MONTH IN RANDOM TABLE NAME).Please select an ending month:.' );

etc...

REP_MONTH and REP_YEAR are defined in the .mas file. The define statements are as follows:

DEFINE REP_MONTH/M=HPART(REPORT_PERIOD, 'MONTH', REP_MONTH);,$
DEFINE REP_YEAR/YY=HPART(REPORT_PERIOD, 'YEAR', REP_YEAR);,$

So what I would like to be able to do is concatenate the values the user selects for &START_MONTH and &START_YEAR (which are based on the above). I have tried using a define with no success. I tried the following define (inside the DEFINE TABLE section):

F1/M = &START_MONTH;
F2/YY = &START_YEAR;
F3/MYY = &START_MONTH||&START_YEAR;

Any help would be appreciated and thanks in advance.

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

Sorry about the bump but there is no way to edit a post.

Anyhow in the "F3" define replace the amper variables with F1 and F2 i.e.

F3/MYY = F1||F2;

thanks again and sorry.

I assume you are getting errors. Well, the right side of your calculation for F3 is correct though you might need quotes. You cannot however, equate the right side which is inherently an alphanumeric expression into a date field.

Here is only one way out of many to do this:

-SET &START_MONTH='02';
-SET &START_YEAR='2009';
-SET &START_DATE=&START_MONTH||&START_YEAR;
-TYPE &START_DATE
DEFINE FILE CAR
F3/MYY=&START_DATE;
END
TABLE FILE CAR
PRINT COUNTRY F3
END

Ginny,

What if I were to change the formats of F1 and F2 i.e.:

F1/A2=&START_MONTH;
F2/A4=&START_YEAR;
F3/A6=F2||F1;

I could then use 'EDIT' (I think) to convert F3 into an interger and be able to do a comparison, correct?

You could do that but you could also say

F3/A6='&START_MONTH'||'&START_YEAR';

which gives you two less defines to evaluate.

Ginny,

Using A6 results in the following error:

RESULT OF EXPRESSION IS NOT COMPATIBLE WITH THE FORMAT OF FIELD.

I'm going to try your earlier suggestion and expand it out to include an END_DATE as well, I'll let you know how that works.

Ginny,
Your previous suggestion seems to work but it disregards the user input from the WHERE statement. Any Suggestions?

I don't know where to start.

If the code above is what you are using and the data source is relational, I highly discourage the use of WHERE clauses against DEFINE'd fields. This opertation turns off optimization and the WHERE is not passed to the backend data base for evaluation. All the data comes back to WebFOCUS for screening.

With that said, waht is REPORT_PERIOD format in the master? Give examples of the data.

-TYPE what you are getting from the user input.

Show more code.

Even if you prompt for month and year, depending on what the REPORT_PERIOD looks like, you may be able to construct a variable that looks like it and be able to screen on it directly.

See what you can make of my requests and then we'll move forward.

Ginny,

Thanks for the tip on WHEREs and DEFINE'd fields. As per your request for more code:

fex:
-SET &START_DATE=&START_YEAR||&START_MONTH;
-TYPE &START_DATE
-SET &END_DATE=&END_YEAR||&END_MONTH;
-TYPE &END_DATE
DEFINE FILE COUNTY
SK/I3=IF COUNTY EQ 'State Total' THEN 0 ELSE IF COUNTY EQ 'Not by county' THEN 999 ELSE 1;
F3/I6=&START_DATE;
F4/I6=&END_DATE;
END
TABLE FILE COUNTY
SUM
AVE.TOTAL_CASES/P7C AS 'Cases,Total'
AVE.CHILDREN_CASES/P7C AS 'Cases,Children,Only'
AVE.ADULT_CASES/P7C AS 'Cases,With,Adults'
AVE.DIVERSION_ASSIST_CASES/P7C
AS 'Related,Cases,Diversion,Assistance'
AVE.FLEX_FUND_CASES/P7C AS ',Related,Cases,Flex,Fund'
AVE.SUPPORT_SERVICE_CASES/P7C
AS 'Related,Cases,Support,Service'
AVE.TOTAL_PERSONS/P7C AS 'Persons,Total'
AVE.ADULT_PERSONS/P7C AS 'Persons,Adults'
AVE.CHILDREN_PERSONS/P7C AS 'Persons,Children'
BY SK NOPRINT
BY COUNTY AS 'County'
BY AREA_FO NOPRINT AS 'Field Operations Area'
ON TABLE SUBHEAD
"Related Cases and Persons"
"By Category and County"
ON TABLE SUBFOOT
"Prepared on: <+0>&DATE<+0> "
WHERE ( F3 LE F4);
WHERE ( REP_MONTH GE '&START_MONTH.(FIND REP_MONTH IN COUNTY).Please select a start month:.' ) AND ( REP_YEAR GE '&START_YEAR.(FIND REP_YEAR IN COUNTY).Please select a start year:.' ) AND ( REP_YEAR LE '&END_YEAR.(FIND REP_YEAR IN COUNTY).Please select an ending year:.' ) AND ( REP_MONTH LE '&END_MONTH.(FIND REP_MONTH IN COUNTY).Please select an ending month:.' );
WHERE ( AREA_FO EQ &AREA_FO.(OR(FIND AREA_FO IN COUNTY)).Area. );
WHERE ( COUNTY EQ &COUNTY.(OR(FIND COUNTY IN COUNTY)).COUNTY. );

the rest is all the styling stuff and headers and footers (nothing in those but text for now) and I removed the -SETs that set default the values. This seems to now take the user input.

The format of REPORT_PERIOD in the .mas is HYYMDS.

Here are all the defines from the .mas that have to do with REPORT_PERIOD:

DEFINE rep_date/YYMD=HDATE(REPORT_PERIOD, 'YYMD');,$
DEFINE REP_MONTH/M=HPART(REPORT_PERIOD, 'MONTH', REP_MONTH);,$
DEFINE REP_YEAR/YY=HPART(REPORT_PERIOD, 'YEAR', REP_YEAR);,$

Here is a link that might help you:
https://forums.informationbuilders.com/eve/forums/a/tpc/...281097331#5281097331

Basically what you need to do is construct two Smart datetime variables from the input provided to you from the launch page, &START_DATE and &START_TIME. As you will see in the post, you will then compare the REPORT_PERIOD using the DT syntax to these variables. Your start datetime will be the absolute beginning of the range and the end datetime will be the absolute end of the range.

-* Simulating input variables
-SET &START_MONTH='02';
-SET &START_YEAR='2008';
-SET &END_MONTH='03';
-SET &END_YEAR='2008';
-* Calculating full dates
-SET &START_DT=&START_YEAR||&START_MONTH||'01';
-SET &END_DT=&END_YEAR||&END_MONTH||'01';
-SET &END_DTM= DATECVT( DATEMOV(DATECVT(&END_DT,'I8YYMD','YYMD'),'EOM'),'YYMD','I8YYMD');
-* Creating datetime stamps
-SET &START_DATE=EDIT(&START_DT,'9999/99/99') | ' 00:00:00';
-TYPE &START_DATE
-SET &END_DATE=EDIT(&END_DTM,'9999/99/99') | ' 00:00:00';
-TYPE &END_DATE

Now you can say:

WHERE REPORT_PERIOD GE DT(&START_DATE) AND REPORT_PERIOD LE DT(&END_DATE)

You may have to change the slashes to dashes depending on your data base but the benefit of this is that you will now be screening against a real data base field and your query will run much faster.

You may have to play around with this a bit to get it to work in your environment.

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

Ginny,

Thanks, I'll give this a try and let you know how it works out. I really do appreciate all the time and help you have provided. You have litterally stopped me from beating my head against a wall.

Ginny,

Your last suggestion nailed it. I can't thank you enough. I'm sorry I didn't get back to you sooner, I had some other issues to take care of. Thanks again.

My pleasure. It is interesting that this posting started with one question and turn into a treatise on relational efficiencies. One of my favorite topics.

Continued success in using WebFOCUS.