This discussion is archived
1 2 3 Previous Next 30 Replies Latest reply: Dec 31, 2009 2:11 PM by 34946 RSS

cascading lov - 3 lovs, but if 2nd is null, 3rd doesn't work.

KarenH Newbie
Currently Being Moderated
hi, I have a page with a collection (effort_c) that has several cascading lovs:
c020 = waters fished
c021 = state
c022 = area code

if c020 in (1,2) then user must choose c021 to populate c022 BUT, if c020 in (3,4), c021 will always be null and user only needs to select c022.

The select list is blank for c022 when c020 in (3,4) and I cannot figure out why. Any help is appreciated. thanks.


here is the query (ps the CASE is in place because c022 may be hidden for certain users):

SELECT apex_item.hidden (18, seq_id) checkbox,
apex_item.hidden (19, seq_id)
|| apex_item.select_list_from_query
(20,
c020,
'select distance_desc, distance_code
from distance_codes',
'style="width:125px;background-color:#FBEC5D;"'
|| 'onchange="f_set_casc_instate(this,f21_'
|| LPAD (seq_id, 4, '0')
|| ');'
|| CASE
WHEN :p300_authorization1 = 1
THEN 'f_set_casc_area(this,f22_'
|| LPAD (seq_id, 4, '0')
|| ');'
ELSE NULL
END
|| 'f_set_casc_subarea(this,f23_'
|| LPAD (seq_id, 4, '0')
|| ')"',
'YES',
'0',
'- Select Waters -',
'f19_' || LPAD (seq_id, 4, '0'),
NULL,
'NO'
) distance_code,
apex_item.select_list_from_query
(21,
c021,
'select partner_name, partner_abbrev
from partners
where state_code is not null order by partner_name ',
'style="width:100px"'
|| 'onchange="'
|| CASE
WHEN :p300_authorization1 = 1
THEN 'f_set_casc_area(this,f22_'
|| LPAD (seq_id, 4, '0')
|| ');'
ELSE null
END
|| 'f_set_casc_subarea(this,f23_'
|| LPAD (seq_id, 4, '0')
|| ')"',
'YES',
'0',
'- Select State -',
'f21_' || LPAD (seq_id, 4, '0'),
NULL,
'NO'
) in_state,
apex_item.select_list_from_query_xl
(22,
c022,
'select distinct area_name,'
|| ' area_code '
|| ' from areas_fished '
|| ' where sub_area_code in (decode('
|| c020
|| ',3, '
|| ' ''0000'' '
|| ' ,4, '
|| ' ''0000'' '
|| ' )) or '
|| ' (sub_area_code not in (decode('
|| c020
|| ',1, '
|| ' ''0000'' '
|| ' ,2, '
|| ' ''0000'' '
|| ' ))'
|| ' AND state = '
|| ''''
|| to_char(c021)
|| ''''
|| ') order by area_code',
'style="width:200px;background-color:#FBEC5D;"'
|| 'onchange="f_set_casc_subarea(this,f23_'
|| LPAD (seq_id, 4, '0')
|| ')"',
'YES',
'0',
'- Select Area -',
'f22_' || LPAD (seq_id, 4, '0'),
NULL,
'NO'
) area_code
from apex_collections where collection_name = 'EFFORT_C'


here are the functions:
// Cascading Select List INSTATE

function f_set_casc_instate(pThis,pSelect){
var l_Return = null;
var l_Select = html_GetElement(pSelect);
var get = new htmldb_Get(null,$x('pFlowId').value,
'APPLICATION_PROCESS=tab_casc_sel_list_instate',0);
get.add('TAB_CASCADING_DISTANCE',$x(pThis).value);
gReturn = get.get('XML');

if(gReturn && l_Select){
var l_Count = gReturn.getElementsByTagName("option").length;
l_Select.length = 0;
for(var i=0;i<l_Count;i++){
var l_Opt_Xml = gReturn.getElementsByTagName("option");
appendToSelect(l_Select, l_Opt_Xml.getAttribute('value'),
l_Opt_Xml.firstChild.nodeValue)
}
}
get = null;
};

// cascading list for AREA

function f_set_casc_area(pThis,pSelect){
var l_Return = null;
var l_Select = html_GetElement(pSelect);
var get = new htmldb_Get(null,$x('pFlowId').value,
'APPLICATION_PROCESS=tab_casc_sel_list_area',0);
get.add('TAB_CASCADING_INSTATE',$x(pThis).value);
get.add('TAB_STATE',$x(pThis).value);
gReturn = get.get('XML');

if(gReturn && l_Select){
var l_Count = gReturn.getElementsByTagName("option").length;
l_Select.length = 0;
for(var i=0;i<l_Count;i++){
var l_Opt_Xml = gReturn.getElementsByTagName("option")[i];
appendToSelect(l_Select, l_Opt_Xml.getAttribute('value'),
l_Opt_Xml.firstChild.nodeValue)
}
}
get = null;
};


and here is the tab_casc_sel_list_area process

DECLARE
v_counter NUMBER := 0;
BEGIN

FOR c IN (SELECT distinct count(distinct area_name||area_code) OVER () area_count,area_code,area_name||'-'||area_code area_name
from areas_fished where sub_area_code in (decode(nvl(:tab_cascading_distance,0),3,'0000' ,4, '0000' ))
or (sub_area_code not in (decode(nvl(:tab_cascading_distance,0),1, '0000' ,2, '0000' ))
AND state = to_char(:tab_cascading_instate)) )
LOOP
IF v_counter = 0
THEN
OWA_UTIL.mime_header ('text/xml', FALSE);
HTP.p ('Cache-Control: no-cache');
HTP.p ('Pragma: no-cache');
OWA_UTIL.http_header_close;
HTP.prn ('<select>');
HTP.prn ( '<option value="'
|| 0
|| '">'
|| '- Select AREA ('
|| c.area_count
|| ') -'
|| '</option>'
);
END IF;

HTP.prn ('<option value="' || c.area_code || '">' || c.area_name || '</option>');
v_counter := v_counter + 1;
END LOOP;

HTP.prn ('</select>');
END;;
  • 1. Re: cascading lov - 3 lovs, but if 2nd is null, 3rd doesn't work.
    KarenH Newbie
    Currently Being Moderated
    still stumped. does anyone have any ideas? thank you!
  • 2. Re: cascading lov - 3 lovs, but if 2nd is null, 3rd doesn't work.
    ATD Guru
    Currently Being Moderated
    Hi Karen,

    Not sure I'm reading your code correctly, but this bit:

    'select distinct area_name,'
    || ' area_code '
    || ' from areas_fished '
    || ' where sub_area_code in (decode('
    || c020
    || ',3, '
    || ' ''0000'' '
    || ' ,4, '
    || ' ''0000'' '
    || ' )) or '
    || ' (sub_area_code not in (decode('
    || c020
    || ',1, '
    || ' ''0000'' '
    || ' ,2, '
    || ' ''0000'' '
    || ' ))'
    || ' AND state = '
    || ''''
    || to_char(c021)
    || ''''
    || ') order by area_code

    Should the second half be "not in"? as "not in (1,2)" is the same as "in (3,4)" (ignoring the decodes and assuming the values are on 1, 2, 3 & 4)

    Andy
  • 3. Re: cascading lov - 3 lovs, but if 2nd is null, 3rd doesn't work.
    KarenH Newbie
    Currently Being Moderated
    Thanks Andy,

    To help explain the query (and make it more readable), I have stripped out all the concatenation and apostrophes.
    The columns in question are:
    c020 = distance code
    c021 = state
    c022 = area
    c023 = sub_area

    basically, there are 4 distance codes (c020). If the distance code is
    - 3 or 4 then select where the sub_area_code is in ('0000')
    - 1 or 2 the select where sub_area not '0000' AND state = c021.

    select distinct area_name, area_code
    from areas_fished
    where sub_area_code in (decode(c020,3,'0000' ,4, '0000' )
    )
    OR sub_area_code not in (decode(c020,1, '0000' , 2, '0000' )
    ) AND
    state = c021
    order by area_code

    where distance code = 1 or 2, the lov for c021 is populated. This in turn populates the lov for c022

    The problem is when distance code = 3 or 4. I think this is because c021 becomes null impacting the lovs for c022 and c023.

    hopefully that makes sense
  • 4. Re: cascading lov - 3 lovs, but if 2nd is null, 3rd doesn't work.
    ATD Guru
    Currently Being Moderated
    Hi Karen,

    It does help to see the non-code version!

    I would have written the statement as:

    select distinct area_name, area_code
    from areas_fished
    where (c020 in (1, 2) and sub_area_code = '0000')
    or (c020 in (3, 4) and sub_area_code <> '0000' and state = c021)

    Your original code may also work if you move the bracket from before the AND to after the c021 as you have A OR B AND C and you need A OR (B AND C)

    Andy
  • 5. Re: cascading lov - 3 lovs, but if 2nd is null, 3rd doesn't work.
    KarenH Newbie
    Currently Being Moderated
    thanks again Andy.

    I have tried your two suggestions, but drat, they do not do the trick. I think there is a problem when c021 is set to null (which happens when c020 = 3,4)

    again, if c020 = 1 or 2, then c021 is populated with a state code which populates c022.
    but if c020 = 3 or 4, c021 is set to null (which is as it should be) BUT c022 then doesn't populate at all.

    hmm.
  • 6. Re: cascading lov - 3 lovs, but if 2nd is null, 3rd doesn't work.
    ATD Guru
    Currently Being Moderated
    Hmm, indeed!

    The problem could be a bit more fundamental.

    If c021 is null, then what is it's value when being compared to the state field? ie, does it return "null" or "%null%" or null. I've had far too many problems with using nulls in a select list and now tend to use default values of "Z" or -1 or whatever I know could never appear in the data.

    Andy
  • 7. Re: cascading lov - 3 lovs, but if 2nd is null, 3rd doesn't work.
    KarenH Newbie
    Currently Being Moderated
    Hi Andy,

    I think the issue is less to do with that query and more to do with the functions being called within that query.

    So, when c020 is set to 1 or 2, c021 is populated with state names. As a state is selected, the ONCHANGE command is issued which calls f_set_casc_instate:

    // Cascading Select List INSTATE

    function f_set_casc_instate(pThis,pSelect){
    var l_Return = null;
    var l_Select = html_GetElement(pSelect);
    var get = new htmldb_Get(null,$x('pFlowId').value,
    'APPLICATION_PROCESS=tab_casc_sel_list_instate',0);
    get.add('TAB_CASCADING_DISTANCE',$x(pThis).value);
    gReturn = get.get('XML');

    if(gReturn && l_Select){
    var l_Count = gReturn.getElementsByTagName("option").length;
    l_Select.length = 0;
    for(var i=0;i<l_Count;i++){
    var l_Opt_Xml = gReturn.getElementsByTagName("option");
    appendToSelect(l_Select, l_Opt_Xml.getAttribute('value'),
    l_Opt_Xml.firstChild.nodeValue)
    }
    }
    get = null;
    };


    it is in this function that the application item TAB_CASCADING_DISTANCE is set.

    TAB_CASCADING_DISTANCE is used in the query to generate the LOV for c022.


    So, my guess is that because c021 does not populate when c020 = 3,4 (nor does it need to), that the value of TAB_CASCADING_DISTANCE is not set. So, is there a way to set tab_cascading_distance to a default?

    thanks
  • 8. Re: cascading lov - 3 lovs, but if 2nd is null, 3rd doesn't work.
    ATD Guru
    Currently Being Moderated
    Hi

    I hadn't even go that far in the code!! - I was just about to set up a test version on my OTN so that I could see a working version based on all values being available for select lists and then go from there!

    You could well be right about the function - this is one of the reasons why I use values instead of null as they are easier to check in javascript statements.

    You could wrap the entire function content within a single if() test that checks pThis.value and only perform the ajax call when it's not "null" (or whatever the string for null is!). So:

    function f_set_casc_instate(pThis,pSelect){
    if (pThis.value == "null") {
    ... existing code ...
    }
    }

    Andy
  • 9. Re: cascading lov - 3 lovs, but if 2nd is null, 3rd doesn't work.
    KarenH Newbie
    Currently Being Moderated
    I am going to try that....in the mean time, if you would like to log into the demom send me an email karen.holmes @ accsp.org and I will email back the link.

    thanks for thinking about this. karen
  • 10. Re: cascading lov - 3 lovs, but if 2nd is null, 3rd doesn't work.
    KarenH Newbie
    Currently Being Moderated
    that would be demo, not demon. hmm
  • 11. Re: cascading lov - 3 lovs, but if 2nd is null, 3rd doesn't work.
    ATD Guru
    Currently Being Moderated
    Hi Karen

    I'm going to set up a working example on my OTN workspace this weekend - nothing much to do as I'll be spending some time waiting for deliveries anyway and you know they never turn up when you expect them! I need to see a working example to get a good idea of what's going on (or, not going on, as the case may be!). I won't recreate everything, just enough to see what the code is doing.

    Andy
  • 12. Re: cascading lov - 3 lovs, but if 2nd is null, 3rd doesn't work.
    KarenH Newbie
    Currently Being Moderated
    you are terrific! thanks for the help. this has been really bugging me.

    hope the delivery people make it!
  • 13. Re: cascading lov - 3 lovs, but if 2nd is null, 3rd doesn't work.
    ATD Guru
    Currently Being Moderated
    np Karen

    But if they turn up early, I may just have to go out instead :D
  • 14. Re: cascading lov - 3 lovs, but if 2nd is null, 3rd doesn't work.
    KarenH Newbie
    Currently Being Moderated
    absolutely! I would certainly do the same. have fun.
1 2 3 Previous Next