1 2 3 Previous Next 30 Replies Latest reply: Dec 31, 2009 4:11 PM by 34946 RSS

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

    KarenH
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            that would be demo, not demon. hmm
                            • 11. Re: cascading lov - 3 lovs, but if 2nd is null, 3rd doesn't work.
                              ATD
                              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
                                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
                                  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
                                    absolutely! I would certainly do the same. have fun.
                                    1 2 3 Previous Next