Hi,

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.

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.

Thanks Dave for your suggestion.

This is what I tried.

  

DEFINE FILE W_ADDR
MONTH_START/A3 = HNAME(START_DT,'MONTH', 'A3');
MONTH_END/A20 = HNAME(END_DT,'MONTH','A20');
MONTH_START_NUM/A2=DECODE MONTH_START('JAN' 1 'FEB' 2 'MAR' 3 'APR' 4 'MAY' 5 'JUN' 6 'JUL' 7 'AUG' 8 'SEP' 9 'OCT' 10 'NOV' 11 'DEC' 12);
MONTH_END_NUM/A2=DECODE MONTH_END('JAN' 1 'FEB' 2 'MAR' 3 'APR' 4 'MAY' 5 'JUN' 6 'JUL' 7 'AUG' 8 'SEP' 9 'OCT' 10 'NOV' 11 'DEC' 12);
END
TABLE FILE W_ADDR
PRINT 
START_DT
MONTH_START
MONTH_START_NUM
END_DT
MONTH_END
MONTH_END_NUM

WHERE MONTH_START_NUM LE &MONTH_BIR AND MONTH_END_NUM GE &MONTH_BIR;

END

&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.

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.

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.

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

 MONTH_START_NUM LE '1' OR '2' 

It likes to see

MONTH_START_NUM LE '1' OR MONTH_START_NUM LE '2'

So, try what I have pasted below as a solution.

 
-DEFAULT &MONTHSEL = ''
-DEFAULT &SWHERE = ''
-DEFAULT &EWHERE = ''
-DEFAULT &START_WHERE = ''
-DEFAULT &END_WHERE = ''
-DEFAULT &STARTPHRASE = ''
-DEFAULT &ENDPHRASE = ''
-DEFAULT &MYWHERE = ''
-SET &STARTPHRASE = 'OR MONTH_START_NUM LE';
-SET &ENDPHRASE = 'OR MONTH_END_NUM GE';
-SET &MONTHSEL = IF &MONTHSEL EQ '' THEN 'FOC_NONE' ELSE &MONTHSEL;
-SET &SWHERE = IF &MONTHSEL.LENGTH LT 3 THEN 'FOC_NONE' ELSE STRREP(&MONTHSEL.LENGTH,&MONTHSEL,2,'OR',&STARTPHRASE.LENGTH,&STARTPHRASE,200,'A200');
-SET &EWHERE = IF &MONTHSEL.LENGTH LT 3 THEN 'FOC_NONE' ELSE STRREP(&MONTHSEL.LENGTH,&MONTHSEL,2,'OR',&ENDPHRASE.LENGTH,&ENDPHRASE,200,'A200');
-SET &START_WHERE = IF &MONTHSEL.LENGTH LT 3 THEN 'MONTH_START_NUM LE ' || &MONTHSEL ELSE '(MONTH_START_NUM LE ' | &SWHERE || ')';
-SET &END_WHERE = IF &MONTHSEL.LENGTH LT 3 THEN 'MONTH_END_NUM GE ' || &MONTHSEL ELSE ' (MONTH_END_NUM GE ' | &EWHERE || ')';
-SET &MYWHERE = &START_WHERE || ' AND ' || &END_WHERE;


DEFINE FILE CAR
START_DT/HYYMD = DT(20110101 05:45);
END_DT/HYYMD = DT(20110801 05:45);
MONTH_START/A3 = HNAME(START_DT,'MONTH','A3');
MONTH_END/A3 = HNAME(END_DT,'MONTH','A3');
MONTH_START_NUM/A2 = DECODE MONTH_START('JAN' 1 'FEB' 2 'MAR' 3 'ARP' 4 'MAY' 5 'JUN' 6 'JUL' 7 'AUG' 8 'SEP' 9 'OCT' 10 'NOV' 11 'DEC' 12);
MONTH_END_NUM/A2 = DECODE MONTH_END('JAN' 1 'FEB' 2 'MAR' 3 'ARP' 4 'MAY' 5 'JUN' 6 'JUL' 7 'AUG' 8 'SEP' 9 'OCT' 10 'NOV' 11 'DEC' 12);
END
TABLE FILE CAR
PRINT
COUNTRY
START_DT
MONTH_START
MONTH_START_NUM
END_DT
MONTH_END
MONTH_END_NUM
WHERE &MYWHERE
END

Of course, I hard coded the date and times in where you are getting them from the database. Hope this helps.

Dave

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)

Thanks Dave.

Your code worked perfectly for me.

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,