I have a Multi select dropdown in my launch page.user can select Months from the dropdown.He can either select single month or multiple months.In my database I have two fields Start_dt and End_dt.My requirement is when user selects months from the dropdown I have to check whether they are in between Start_dt and End_dt.Always Start_dt and End_dt will contain only month starts.For example 2010-02-01 which is Feb 1st.What I thought is to extract month from Start_dt and End_dt using HNAME function because they will always contain months starts. After extracting the months how will I write the WHERE clause .
DEFINE FILE W_ADDR
MONTH_START/A20 = HNAME(START_DT,'MONTH','A20');
MONTH_END/A20 = HNAME(END_DT,'MONTH','A20');
END
TABLE FILE W_ADDR
PRINT
START_DT
MONTH_START
END_DT
MONTH_END
END
Can somebody give me some tips how to achieve this scenario.Thanks.
WebFOCUS 7.6.7 windows Html,Pdf and Excel
August 04, 2011, 12:16 PM
dburton
quote:
Originally posted by swetha: After extracting the months how will I write the WHERE clause .
DEFINE FILE W_ADDR
MONTH_START/A20 = HNAME(START_DT,'MONTH','A20');
MONTH_END/A20 = HNAME(END_DT,'MONTH','A20');
END
TABLE FILE W_ADDR
PRINT
START_DT
MONTH_START
END_DT
MONTH_END
END
You will need to build a dynamic WHERE statement. If the user selects one month or more than one month then the code below should work. I have a previous post that shows this exact problem that another user was having. I have simply put your code and the same solution together below.
You need to make an amper variable and assign that amper variable to your multiselect drop down. If the user selects one value then your amper variable will only have one value. If there are more than one, the amper variable will always show like "(val1 OR val2)".
-DEFAULT &MONTHSEL = '';
-SET &MONTHSEL = IF &MONTHSEL = '' THEN 'FOC_NONE' ELSE &MONTHSEL;
DEFINE FILE W_ADDR
MONTH_START/A20 = HNAME(START_DT,'MONTH','A20');
MONTH_END/A20 = HNAME(END_DT,'MONTH','A20');
END
TABLE FILE W_ADDR
PRINT
START_DT
MONTH_START
END_DT
MONTH_END
WHERE START_DT LE &MONTHSEL AND END_DT GE &MONTHSEL
END
I hope this helps.
Dave.
WebFOCUS 7.7.03 Windows Web Server 2008 MS SQL Server 2000 Excel,CSV,PDF,HTML
&MONTH_BIR is my amper variable from the launch page.
This WHERE clause is working for one selection.When I select Multiple months I am getting an error. when I select january and february my where clause will be
WHERE MONTH_START_NUM LE '1' OR '2' AND MONTH_END_NUM GE '1' OR '2';
I am getting the following error...
0 ERROR AT OR NEAR LINE 20 IN PROCEDURE SAMPLE1 FOCEXEC * (FOC261) EXPRESSION IS INCOMPLETE BECAUSE AN OPERATION IS MISSING (FOC009) INCOMPLETE REQUEST STATEMENT BYPASSING TO END OF COMMAND
I have no clue what it is.Could you help me. Thanks.
WebFOCUS 7.6.7 windows Html,Pdf and Excel
August 04, 2011, 03:18 PM
j.gross
Please explain what requirement you are trying to achieve by allowing the user to select up to 12 months (presented to the focexec as a list of integer values in the range 1 to 12) and how the particular months should infuence the answer set?
You say the data records contain start_dt and end_dt month/year values (stored as first-of-month but representing the entire month), and you indicate those are the sole date-related fields to be used in selecting records. Do you want to select a record if at least one of the notional months selected by the user fall within the month-range of the database record?
If a record has a date range in which all 12 months occur (e.g., 12/2009 through 1/2011), would the record be included regardless of the user's selections?
I suspect you are not adequately describing the requirement.
August 04, 2011, 04:09 PM
swetha
Gross for your question - Do you want to select a record if at least one of the notional months selected by the user fall within the month-range of the database record?
It's a yes.For example we have 3 records with the following values 1) 2010-01-01 -- 2010-03-01 2) 2010-02-01 -- 2010-06-01 3) 2010-01-01 -- 2010-04-01
Now when the user selects january and may from the dropdown we get all the three records.
If a record has a date range in which all 12 months occur (e.g., 12/2009 through 1/2011), would the record be included regardless of the user's selections?
This record will be included if the user selects any month from the dropdown. Does it make sense to you. Thankyou for your time.
WebFOCUS 7.6.7 windows Html,Pdf and Excel
August 05, 2011, 09:20 AM
dburton
Hey swetha,
The reason you get the error
quote:
0 ERROR AT OR NEAR LINE 20 IN PROCEDURE SAMPLE1 FOCEXEC * (FOC261) EXPRESSION IS INCOMPLETE BECAUSE AN OPERATION IS MISSING (FOC009) INCOMPLETE REQUEST STATEMENT BYPASSING TO END OF COMMAND
is because when using a where clause to test alphanumeric characters it does not like the use of
Of course, I hard coded the date and times in where you are getting them from the database. Hope this helps.
Dave
WebFOCUS 7.7.03 Windows Web Server 2008 MS SQL Server 2000 Excel,CSV,PDF,HTML
August 05, 2011, 10:20 AM
j.gross
Based on your reply, you can do it using a McGyver-type technique and INCLUDES.
Create a reference table with two values * ref_dt /(same format as start_dt) * ref_mo /a2 (loaded with the notional month of the ref_dt value, as '1 ', '2 ', ... to '12') The table must cover the entire range of months that start_dt and end_dt may span.
join your data source to the ref table, as a one-to-many join, based on a WHERE condition: where (start_dt le ref_dt) and (end_dt ge ref_dt);
So now, for each record in your data source, the join explodes the record into multiple instances, one for each calendar month-start-date in the range.
after the join, issue a define : take/A1= if (ref_mo eq &month_list) then 'Y' else 'N'; (where &month_list is the paraneter value received for the multi-select).
now your table request, against the join structure, can use the screening condition where take includes 'Y'; to pick up the rows whose start_dt/end_dt range includes at least one of the months selected by the user.
Because of the way INCLUDES operates, the answer set will include only one instance of the row, even if more than one selected month is included in the record month-range.
Note that your request should not reference any fields in the ref table as verb-objects; only the INCLUDES clause may make reference to data fields in that segment.
(I define TAKE as alpha rather than integer, because INCLUDES/OMITS does not seem to allow use of numeric fields, at least in 7.6.9)
- Jack Gross WF through 8.1.05
August 05, 2011, 12:58 PM
swetha
Thanks Dave.
Your code worked perfectly for me.
WebFOCUS 7.6.7 windows Html,Pdf and Excel
August 05, 2011, 04:16 PM
j.gross
quote:
Thanks Dave.
Your code worked perfectly for me.
Really?
Try it with start=june 2010, end=june 2011, and selected months = 1,2,3,4,5,7,8,9,10,11,12
[and note that April has a typo]This message has been edited. Last edited by: j.gross,