Home » U++ Library support » U++ SQL » Simple SQL question
Simple SQL question [message #31366] |
Fri, 25 February 2011 12:43 |
|
koldo
Messages: 3358 Registered: August 2008
|
Senior Veteran |
|
|
Hello all
I wanted to select all registers from columns Name, Surname and Location that contains either text1 or text2.
For example, if the table is:
Name Surname Location
John Doe München
Mary Poppins Bruxelles
with text1 = "pin" and text2 = "elle", the second register would be selected.
How could it be done?
Best regards
IƱaki
|
|
|
|
|
|
Re: Simple SQL question [message #31464 is a reply to message #31463] |
Fri, 04 March 2011 13:36 |
|
koldo
Messages: 3358 Registered: August 2008
|
Senior Veteran |
|
|
Hello Mirek
There are some problems (I use sqlite):
- Instead of FULLDATA it has to be used "FULLDATA", as it is a new column so it is not in .sch file
- NAME|"."|SURNAME is translated to "NAME + '.' + SURNAME". It does not produce any SQL error, but no register is found.
However this works: "NAME || '.' || SURNAME"
- I would like to select all columns plus FULLDATA. How is it done in the Select()?
- A comment: As() is replaced by "" in sqlite. sqlite understands it well, although it also understands the classic "AS"
Thank you for your answer.
Best regards
IƱaki
[Updated on: Fri, 04 March 2011 13:37] Report message to a moderator
|
|
|
|
Re: Simple SQL question [message #31479 is a reply to message #31475] |
Sat, 05 March 2011 15:11 |
|
koldo
Messages: 3358 Registered: August 2008
|
Senior Veteran |
|
|
Thank you Mirek
Yes, there is a problem in operator| for sqlite. The fix would be in SqlVal.cpp:
SqlVal operator|(const SqlVal& a, const SqlVal& b) {
return SqlVal(a, SqlCase(ORACLE, " || ")(PGSQL, " || ")(SQLITE3, " || ")(" + "), b, SqlS::MUL); // Added (SQLITE3, " || ")
}
In addition, please could you add function coalesce?. The functions could be:
SqlVal Coalesce(const SqlVal& exp1, const SqlVal& exp2) {
return SqlFunc("coalesce", exp1, exp2);
}
SqlVal Coalesce(const SqlVal& exp1, const SqlVal& exp2, const SqlVal& exp3) {
return SqlFunc("coalesce", exp1, exp2, exp3);
}
SqlVal Coalesce(const SqlVal& exp1, const SqlVal& exp2, const SqlVal& exp3, const SqlVal& exp4) {
return SqlFunc("coalesce", exp1, exp2, exp3, exp4);
}
It is compatible with at least sqlite, mysql, postgre, oracle ans sql server.
Best regards
IƱaki
|
|
|
|
Re: Simple SQL question [message #31492 is a reply to message #31489] |
Sun, 06 March 2011 21:31 |
|
koldo
Messages: 3358 Registered: August 2008
|
Senior Veteran |
|
|
mirek wrote on Sun, 06 March 2011 19:00 | Both applied, thanks.
Coalesce is similar Nvl, but that works only for 2 parameters. It looks like by the time we created SqlExp, this was not standardized (e.g. coalesce is not present before Oracle 9i).
|
Thank you Mirek
Best regards
IƱaki
|
|
|
Goto Forum:
Current Time: Fri May 03 00:49:06 CEST 2024
Total time taken to generate the page: 0.07902 seconds
|