Lazarus/Firebird TSQLQuery.Filter on database View with iif on select ?

[jojo86]

Hi,
I can use TSQLQuery.Filter to filter on my VIEW, and I can filter all my fields, except one.
I have a field wich use 'iif()' and I can't filter on this field...

Before using a database view, I tried directly on my TSQLQuery.SQL, but the problem is the same...

So, my view works, but I Can't filter on this callulated field.

My sql view :

Code: Pascal  [Select][+][-]

1. Create View "TestView" (
2. "FirstName", "LastName", "StatAge"
3. ) AS
4.  
5. Select "FirstName", "LastName",
6.        Cast(iif(CURRENT_DATE-BirthDate<18, 'Minor', 'Major') as VarChar(5)) as StatAge
7. from TableUsers
8. ;

This View works well. I can do that on my TSQLQuery :

Code: Pascal  [Select][+][-]

1. SQLQuery1.SQL.Add('Select * from TestView');

With this code, my DBGrid show all records transmitted by the view named TestView.

I can filter on FirstName, LastName, but I can't filter on StatAge ? DBGrid show empty grid with this criterea :

Code: Pascal  [Select][+][-]

1. SQLQuery1.Filtered:=False
2. SQLQuery1.Filter:='"StatAge" = ''Minor''';
3. SQLQuery1.Filtered:=True
4.  

That's really weird because I can do my filter with a Where clause directly on the query and it works :

Code: Pascal  [Select][+][-]

1. SQLQuery1.SQL.Add('Select * from TestView Where "StatAge"=''Minor''');

So, I'm tired with this problem... Do you have an idea ?

Thanx helphing me.

[jojo86]

So, I solved my problem with IBX4Lazarus component. This is a specific component for Interbase/firebird.

I used it in the past because firebird wasn't integrated by lazarus. Now, we can connect to firebird DB with standard lazarus component, but we can see that there are some problems... So I'm going to work only with IBX components for a better optimisation.

My problem is solved by using IBX4Lazarus : www.mwasoftware.co.uk

[sash]

I don't know a reasons why do you use
  - view, which is generally slower then direct sql, generates redundant sql-dependencies and has other drawbacks
  - client-side filter, which capabilities is much more limited than ones in sql.

So I don't recommend to use both.

As for a filter (if you really need it) it should work, but probably there's something wrong with quoted strings.
Try something simple, swap single with double quotes and vice versa, and/or remove quotes from field names.
As a last resort there's OnFilterRecord.

[dsiders]

So, I solved my problem with IBX4Lazarus component. This is a specific component for Interbase/firebird.

I used it in the past because firebird wasn't integrated by lazarus. Now, we can connect to firebird DB with standard lazarus component, but we can see that there are some problems... So I'm going to work only with IBX components for a better optimisation.

My problem is solved by using IBX4Lazarus : www.mwasoftware.co.uk

IBX4Lazarus is an excellent choice. I use it all the time. It has all of the Firebird 3 goodies too.

But its not going to solve the error in your date arithmetic logic. Being 18 days old does not make you a "Major". Check into DateDiff().