IIF func in FireDAC SQLite Local SQL

[c0d3r 17]

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.

 

[Arnaud Bouchez 407]

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.

[c0d3r 17]

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.

[Arnaud Bouchez 407]

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.

[c0d3r 17]

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.

[Guest]

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?

[c0d3r 17]

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 October 8, 2020 by c0d3r

[Arnaud Bouchez 407]

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

[c0d3r 17]

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 October 12, 2020 by c0d3r

[Arnaud Bouchez 407]

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

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

Edited October 12, 2020 by Arnaud Bouchez

[Dmitry Arefiev 101]

FireDAC in 10.4.1 uses SQLite 3.31.1. But IIF was added in 3.32.

Instead you can use FireDAC macro function: {IIF(....)}:

http://docwiki.embarcadero.com/RADStudio/Sydney/en/System_Macro_Functions_(FireDAC)

[Guest]

@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

[Stano 143]

Sources are in higher versions