This discussion is archived
6 Replies Latest reply: Jan 9, 2013 12:22 AM by user346369 RSS

Default_where issue driving me crazy

789736 Explorer
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    VARCHAR2 is limited to 4000 in SQL. IN PL/SQL the limit is 32767 .
  • 5. Re: Default_where issue driving me crazy
    HamidHelal Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points