Filter: pla:enddate_DATE >= 77521 and pla:startdate_DATE >= 72687

[MikeR]

This is the filter displayed in debugview
the table is an sql server table
the fields are of type date
Im sure it used to work ?
I know why it doesn't now

[kevin plummer]

try this

Filter: pla:enddate_DATE <= 77521 and pla:startdate_DATE >= 72687

[MikeR]

Huh, I don't get it ?

The aim is this:
I want active contracts that start from a particular date forward.

ie EndDate must be greater or equal to today() , that makes them active and
startdate >= anydate in the past ?

[Bruce]

Hi Mike,

Kevin was trying to say that one of the > in your filter should have been an <.

ie
pla:enddate_DATE >= 77521 and pla:startdate_DATE >= 72687

the line above is saying that the end date must be after 27 march 2013, and the start date must be after 1/1/2000.
While this is a perfectly plausible filter, Kevin is suggesting that the match may be wrong. (you didn't say what you were wanting the filter to do).

>> I want active contracts that start from a particular date forward.

that matches your original filter. ie you are limiting the start date to afer some point, and the end date to after some other point (presumably today).

Assuming you haven't misspelled anything, and the data in the SQL table is stored as a LONG (containing a standard clarion date) then the filter is working correctly. If you're not seeing any data in the browse then it's because of some other factor (like maybe there isn't any data that matches.)

You can "see" the whole Prop:filter statement, as it is sent to the driver, by using the "Send filter and order to debugview" template option (and running debugview.) But maybe that's what we have here already? If so I'd look at the data - either there is nothing that matches, or there's something else filtering it out on the browse side (like maybe embed code in ValidateRecord embed point.)

cheers
Bruce

[MikeR]

Hi Bruce the fields are defined as type DATE in sqlserver

[Bruce]

ok, then presumably the longs (77521 etc) should be something else? I'm not the SQL expert so I don't know what it should be - but perhaps look in the SQL database directly to see what's in the fields?

cheers
Bruce

[astahl]

In SQL a date field is basically a string. So your sql(MySQL) statement should be pla:startdate_DATE >= '2013-01-01' and pla:enddate_DATE <= '2013-03-29' . If they were longs then you could use the numerical data like pla:startdate_DATE >= '& Today() - 30 &' and pla:enddate_DATE <= '& Today().


Ashley

[osquiabro]

I filter the sql server dates with embedded setFilter everyday, example of my filter:

loc:FilterWas =' SQL(F.CustomerCode=<39>'&clip(p_web.GSV('CustomerCode'))&|
    ' <39> AND A.BillofLadingCreationDate>= <39>'& FORMAT(Loc:Date,@D02)&|
    ' <39> AND A.BillofLadingType=<39>I<39> AND (A.BillofLadingStatus= <39>D<39> AND A.BOLNOS <> <39><39>) )'     

ThisView{prop:Filter} = loc:FilterWas

[MikeR]

Thanks for all the responses guys,

After many hours of checks re-checks and trying different things, I relized that the test data I was using didn't have and placement records, thats why nothing was showing.
The Filter (The original one) was allways working.

Which brings me to my next question WHY ?
It doesn't use the database field name in the filter it uses the clarion name eg. pla:appid not appid as defined in the sql server database.
It uses the clarion date format for dates, Why does this work. It Does.

  Below is an extract from the actual generated code that works.:
 
  ThisView{prop:order} = p_web.CleanFilter(ThisView,clip(loc:vorder))
  If False  ! Generate Filter
  ElsIf (loc:Parent = lower('NewPlacements') and p_web.gsv('loc:user') = 0)
      loc:FilterWas = 'pla:enddate_DATE >= ' & p_web.gsv('loc:fromdate')& ' and pla:startdate_DATE >= ' & p_web.gsv('glo:eeziEffectiveDate')
  ElsIf (loc:Parent = lower('NewPlacements'))
      loc:FilterWas = 'pla:enddate_DATE >= ' & p_web.gsv('loc:fromdate')& ' and pla:startdate_DATE >= ' & p_web.gsv('glo:eeziEffectiveDate') & ' and (pla:usercode = ' & p_web.gsv('loc:user') & ' or pla:user1code = ' & p_web.gsv('loc:user') & ' or pla:user2code = ' & p_web.gsv('loc:user') & ')'
  ElsIf (loc:Parent = lower('UpdateApplicant'))
      loc:FilterWas = 'pla:appid = ' & p_web.gsv('app1:appid')
  Else
  End
  ThisView{prop:Filter} = loc:FilterWas

[Bruce]

Hi Mike,

this is normal Clarion. The WebServer uses the View Engine (just like any clarion program) and it does all the hard work for you.

Cheers
Bruce

[osquiabro]

Mike you need to format a DATE with format example:

format(p_web.gsv('loc:fromdate'),@d02)

and need quotes example:

loc:FilterWas = 'pla:enddate_DATE >=<39> ' & format(p_web.gsv('loc:fromdate'),@d02)& ' and pla:startdate_DATE >= ' & format(p_web.gsv('glo:eeziEffectiveDate'),@d02) &'<39>'

you can try to debug the query in your SQL with setclipboard(loc:FilterWas)

all my Nettalk project use SQL and work perferct..