6 Replies Latest reply: Jan 9, 2013 2:22 AM by user346369 RSS

    Default_where issue driving me crazy

    789736
      Hi guys,

      I am having an issue with setting the default where (which I have done on many occassions before). Basically, I have a date field and if it is populated I want to show all records which are within 7 days of the date, if it is blank i dont want to take the date into account at all. My code is as follows:


      Declare
      v_where Varchar2(5000);
      v_date date := name_in('block.date');
      v_adv_date date;
      v_default_where varchar2(5000);
      Begin
      v_adv_date := v_date + 7;

      IF name_in('block.date') is not null then
      v_where := 'location = ' || name_in('block.location')|| ' and date >= '||v_date|| ' and date < '||v_adv_date;
      ELSE
      v_where := 'location = ' || name_in('block.location');

      END IF;

      Set_Block_Property('BLOCK',DEFAULT_WHERE,v_where);

      v_default_where := get_block_property('BLOCK', DEFAULT_WHERE);
      message('default where is ' || v_default_where);

      When I use this code my default where is null and it brings back everything (when block.date is not null). If i take out the date section and only use v_where := 'location = ' || name_in('block.location') Everything seems to work fine, now I have tried experimenting with different things to get it to work but I just cant get it.

      Any help would be GREATLY appreciated.

      Thanks.
        • 1. Re: Default_where issue driving me crazy
          983351
          Hi!

          Did you know that VARCHAR2 has a limit of 4000 bytes??

          you are declaring it like v_where_default VARCHAR2(5000)

          Take a look at that
          • 2. Re: Default_where issue driving me crazy
            789736
            Got it, i was being silly, didnt have to concatinate and bring the values in I could keep them all in the one string dugh me!

            Thanks anyway.
            • 3. Re: Default_where issue driving me crazy
              HamidHelal
              Declare
              v_where Varchar2(5000);
              v_date date := name_in('block.date');
              v_adv_date date;
              v_default_where varchar2(5000);
              Begin
              v_adv_date := v_date + 7;

              IF name_in('block.date') is not null then
              v_where := 'location = ' || name_in('block.location')|| ' and date >= '||v_date|| ' and date < '||v_adv_date;
              ELSE
              v_where := 'location = ' || name_in('block.location');

              END IF;
              Hi,
              First of all varchar2 has limit to 4000. Then you need *'* (quotation) before and after of parameter. Try the following code.
              at declare add
              
                   QT VARCHAR2(10) :='''';
              
              then try...
              IF name_in('block.date') is not null then 
                 v_where := 'location = ' || name_in('block.location')||    ' and date >= '||QT||v_date||QT||' and date < '||QT||v_adv_date||QT;      
              ELSE
                v_where :=  'location = ' || name_in('block.location');
                
              END IF;
              code tag showing wrong.. so paste again

              IF name_in('block.date') is not null then
              v_where := 'location = ' || name_in('block.location')|| ' and date >= '||QT||v_date||QT||' and date < '||QT||v_adv_date||QT;
              ELSE
              v_where := 'location = ' || name_in('block.location');

              END IF;

              try to use
               tag but out put is wrong as you write..
              
              Hope this works...
              
              Hamid
              
              If someone response is helpful or correct, mark it.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
              • 4. Re: Default_where issue driving me crazy
                Andreas Weiden
                VARCHAR2 is limited to 4000 in SQL. IN PL/SQL the limit is 32767 .
                • 5. Re: Default_where issue driving me crazy
                  HamidHelal
                  Andreas Weiden wrote:
                  VARCHAR2 is limited to 4000 in SQL. IN PL/SQL the limit is 32767 .
                  Dear Andreas,

                  Thanks for your information. :)
                  • 6. Re: Default_where issue driving me crazy
                    user346369
                    That is some of the ugliest coding with dates I've seen in a long time.

                    Why are you using Name_in()? Name_in should ONLY be used in a PLL Library procedure, where you don't have direct access to :Block.Date. Name_in is especially dangerous on dates -- it returns a varchar2 date string, and depending on whether your date is of datatype date or datetime, it returns different values. I am not sure if the format mask has an effect too. Then stuffing whatever the varchar2 string is back into v_date of datatype DATE, causes a second data conversion. Then you force a third date-to-varchar2 date conversion when you use v_date in your v_where assignment. And last, Oracle must then convert your string in the where clause back to a date before it can compare to your date values in the database. It is no wonder you got no values returned.

                    Try this instead:
                    <pre>Declare
                    v_where Varchar2(200) := 'location = :block.location';
                    Begin
                    If :Block.Date_1 is not null then
                    v_where := v_where
                    ||' and trunc(date_col) between :Block.Date_1 and :Block.Date_1 + 7';
                    End if;
                    End;</pre>
                    I added the trunc() function to remove the time from the date column in the database, just in case values contained a time other than 00:00:00.