Jump to content
c0d3r

IIF func in FireDAC SQLite Local SQL

Recommended Posts

Any one know if IIF function works in FireDAC SQLite local SQL or not, I can't make it work:

 

select iif(xx>0, 10, -1) as field1, .... from..

 

select iif(xx>0, 'Yes', 'No') as field1, ... from...

 

select sum(iif(xx>0, 0, 1)) as field1, ... from ...

 

 

field1 always returned blank/empty.

 

Share this post


Link to post
1 hour ago, Arnaud Bouchez said:

Your usage seems coherent with https://sqlite.org/lang_corefunc.html#iif

 

Does your SQL statement returns the data on an external SQlite3 explorer program?

If it does, then the problem is in FireDAC.

No, I didn't use SQLite3 explorer,  I had few memory datasets (mostly from Reports), try finding a local sql engine to run queries against them. so far I found only TxQuery is working.  However, if Delphi 10.4.1's Compiling Optimization is turned on (Release mode), it seriously  screwed TxQuery engine codes and causing AVs, so I'm looking for an alternative, try FireDAC SQLite Local SQL, it doesn't work as long as using IIF function, try FireDAC samples/Local SQL demo, it doesn't work with IIF either.

Share this post


Link to post

Now I am confused.

Is you SQL statement correct outside of FireDAC?
You need to try it to be sure that the problem is in FireDAC configuration, not in your SQL itself.

Share this post


Link to post
2 hours ago, Arnaud Bouchez said:

Now I am confused.

Is you SQL statement correct outside of FireDAC?
You need to try it to be sure that the problem is in FireDAC configuration, not in your SQL itself.

The SQL statement works with TxQuery,  works with Firebird,  ..., pretty much all the modern SQL engines supported IIF function nowadays.

Share this post


Link to post

Seems TxQuery is a TDataSet-based component implementing SQL. Like kbmMemTable's add-on. So that SQL is parsed and "transplied" to TDataSet operations (?).

Anyways, IMHO Arnaud wanted you to run your actual query in another tool/ide/whatever far away from FireDAC.

Modern DACs to process the SQL, adjustment, additions, et.al before sending to the server so this could be a FireDAC problem.

I do NOT understand from above that you actually executed the query (you do not publish it) sucessfully outside FireDAC.

Confusion grows exponentially when you mention FireBird. Did you run that query on FireBird using FireDAC or ISQL? What happened to SQLLite?

If FireDAC wants to help with a positioning cursor or locking cursor or whatever thingy "behind the scenes", problems can arise for just one combination of RDBMS, drivers and a specific feature.

So did you confirm the query is ok? Not just looking at FireBird/SQLLite docs and check that is is listed?

Share this post


Link to post
3 hours ago, Dany Marmur said:

Seems TxQuery is a TDataSet-based component implementing SQL. Like kbmMemTable's add-on. So that SQL is parsed and "transplied" to TDataSet operations (?).

Anyways, IMHO Arnaud wanted you to run your actual query in another tool/ide/whatever far away from FireDAC.

Modern DACs to process the SQL, adjustment, additions, et.al before sending to the server so this could be a FireDAC problem.

I do NOT understand from above that you actually executed the query (you do not publish it) sucessfully outside FireDAC.

Confusion grows exponentially when you mention FireBird. Did you run that query on FireBird using FireDAC or ISQL? What happened to SQLLite?

If FireDAC wants to help with a positioning cursor or locking cursor or whatever thingy "behind the scenes", problems can arise for just one combination of RDBMS, drivers and a specific feature.

So did you confirm the query is ok? Not just looking at FireBird/SQLLite docs and check that is is listed?

Yeah, query is OK:  "select clientid, sum(iif(price > 0, amount, 0))  as total_amount from sales group by clientid",   ran the query with TxQuery, got right result,  ran the query with Firebird iSQL, got right result too.  Using SQLite Local SQL, it listed all the ClientIDs but the Total_Amount fields were all blank. In order to test "iif" works or not,  I then tried FireDAC local sql demo program (the sample installed by Delphi 10.4.1) connected to demo db, 'select clientid, freight from qOrders' was working,  but:  "select clientid, iif(freight > 0, 10, -10) as test from qOrders" wasn't working,  it listed all the ClientIDs but the "Test" fields were all blank.

Edited by c0d3r

Share this post


Link to post
5 hours ago, Arnaud Bouchez said:

Did you try with another SQLite3 library?
That was my point. 

Our production back end database is Firebird,  we just want to run some queries against in memory datasets at client side applications for reporting purpose, I was told FireDAC Local SQL was based on embedded SQLite3 (?) and doesn't need any other libraries since the entire engine codes are embedded directly into applications(?)  Please let me know how to try another SQLite3 library??  Anyway,  I'm working and testing with kbmMWMemSQL at the moment, so far its good although lack of some features, missing some standard SQL syntax like HAVING, .., etc.

Edited by c0d3r

Share this post


Link to post

SQLite3 supports IIF() so it should work even on virtual tables.

I don't know why FireDac Local SQL rejects it.

Edited by Arnaud Bouchez

Share this post


Link to post

@Dmitry Arefiev

I would love to use FireDAC. But it is wholly apparent, here in Delphi-Praxis even, that a DAC is to convoluted to parse without the sources.

Will... and in that case when... will we have FireDAC with sources? In Professional, that is.

 

TIA,

 

/Dany

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×