U++ forum: Welcome to the forum

Search on this site

Search in forums

Home » U++ Library support » U++ SQL » Simple SQL question

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Simple SQL question [message #31366]

Fri, 25 February 2011 12:43

koldo
Messages: 3356
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

Report message to a moderator

Re: Simple SQL question [message #31373 is a reply to message #31366]

Fri, 25 February 2011 20:00

mirek
Messages: 13975
Registered: November 2005

Ultimate Member

koldo wrote on Fri, 25 February 2011 06:43

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?

http://www.postgresql.org/docs/8.3/static/functions-matching .html

I guess it is even SQL standard.

In SqlExp, use Like.

Mirek

Report message to a moderator

Re: Simple SQL question [message #31386 is a reply to message #31373]

Sun, 27 February 2011 09:19

koldo
Messages: 3356
Registered: August 2008

Senior Veteran

Hello Mirek

For the question it could be used:

SQL.Execute("select *, [Name] || '.' || [Surname] || '.' || [Location] as FULLDATA from PERSONAS where FULLDATA like '%" + pin1 + "%' and FULLDATA like '%" + pin2 + "%'");

The '.' is just to avoid that "aryPop" would match with register MaryPoppinsBruxelles.

Is it possible to do this and use ALIAS with SqlExp or it has to be done with plain SQL?

Best regards
Iñaki

Report message to a moderator

Re: Simple SQL question [message #31463 is a reply to message #31386]

Fri, 04 March 2011 12:00

mirek
Messages: 13975
Registered: November 2005

Ultimate Member

koldo wrote on Sun, 27 February 2011 03:19

Hello Mirek

For the question it could be used:

SQL.Execute("select *, [Name] || '.' || [Surname] || '.' || [Location] as FULLDATA from PERSONAS where FULLDATA like '%" + pin1 + "%' and FULLDATA like '%" + pin2 + "%'");

The '.' is just to avoid that "aryPop" would match with register MaryPoppinsBruxelles.

Is it possible to do this and use ALIAS with SqlExp or it has to be done with plain SQL?

I have to admin I do not know what '[' and ']' mean in [Name] Smile

However, this excluded:

Select((NAME|"."|SURNAME).As(FULLDATA)).Where(Like(FULLDATA, "%"+pin1+"%");

should work.

Mirek

Report message to a moderator

Re: Simple SQL question [message #31464 is a reply to message #31463]

Fri, 04 March 2011 13:36

koldo
Messages: 3356
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 #31475 is a reply to message #31464]

Sat, 05 March 2011 09:10

mirek
Messages: 13975
Registered: November 2005

Ultimate Member

koldo wrote on Fri, 04 March 2011 07:36

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

Then define it locally:

SqlId FULLDATA("FULLDATA");

(perhaps I would rather use something more simple as "F").

Quote:

- NAME|"."|SURNAME is translated to "NAME + '.' + SURNAME". It does not produce any SQL error, but no register is found.
However this works: "NAME || '.' || SURNAME"

That is weird, in SqlExp '|' should be string cat. It could be a bug in SqlExp for specific target DB - what DB is it?

(FYI, it is quite simple, check operator| in SqlExp and see how SqlCase is used to provide different code for different DBs....)

Quote:

- I would like to select all columns plus FULLDATA. How is it done in the Select()?

Select(SqlAll(), FULLDATA)

should work here.

Mirek

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: 3356
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

Report message to a moderator

Re: Simple SQL question [message #31489 is a reply to message #31479]

Sun, 06 March 2011 19:00

mirek
Messages: 13975
Registered: November 2005

Ultimate Member

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).

Report message to a moderator

Re: Simple SQL question [message #31492 is a reply to message #31489]

Sun, 06 March 2011 21:31

koldo
Messages: 3356
Registered: August 2008

Senior Veteran

mirek wrote on Sun, 06 March 2011 19:00

Thank you Mirek

Best regards
Iñaki

Report message to a moderator

Previous Topic:	SQLArray + Like() + Accents problem
Next Topic:	Demo Package of FieldOperator usage

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

] [

]

Current Time: Tue Apr 16 14:11:51 CEST 2024

Total time taken to generate the page: 0.02226 seconds