U++ forum: Welcome to the forum

Search on this site

Search in forums

Home » U++ Library support » U++ SQL » Select from two temporary tables

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

Select from two temporary tables [message #38027]

Wed, 28 November 2012 19:47

Zbych
Messages: 327
Registered: July 2009

Senior Member

Hi,

I need to select columns from two temporary tables, but there is no version of From that takes two SqlSets.

Can you Mirek add this to Sqlexp.h and SqlStatement.cpp:

SqlSelect& From(const SqlSet& set1, const SqlSet& set2);

SqlSelect& SqlSelect::From(const SqlSet& table1, const SqlSet& table2) {
	String ts1 = table1(SqlSet::SETOP + 1);
	String ts2 = table2(SqlSet::SETOP + 1);
	text = "select " + text + " from " + ts1 + ", " + ts2;
	tables << ',' << Filter(ts1, CharFilterNotWhitespace);
	tables << ',' << Filter(ts2, CharFilterNotWhitespace);
	on = false;
	return *this;
}

BTW. Postgres doesn't like parentheses around embeded select, so
AsTable should look like this:

SqlSet SqlSelect::AsTable(const SqlId& tab) const
{
	StringBuffer t;
	t << SqlCase(MSSQL|PGSQL, "")("(") //<---PGSQL added
	<< "(" << text << ") as \t" << tab.ToString() << '\t'
	<< SqlCase(MSSQL|PGSQL, "")(")");  //<---PGSQL added
	return SqlSet(String(t), SqlSet::HIGH);
}

Report message to a moderator

Re: Select from two temporary tables [message #38075 is a reply to message #38027]

Thu, 29 November 2012 17:42

mirek
Messages: 14255
Registered: November 2005

Ultimate Member

OK, I might, but why not just use some form of join instead?

Mirek

Report message to a moderator

Re: Select from two temporary tables [message #38078 is a reply to message #38075]

Thu, 29 November 2012 19:29

Zbych
Messages: 327
Registered: July 2009

Senior Member

Maybe you are right.

[Updated on: Fri, 30 November 2012 08:16]

Report message to a moderator

Re: Select from two temporary tables [message #39300 is a reply to message #38078]

Mon, 11 March 2013 08:53

Zbych
Messages: 327
Registered: July 2009

Senior Member

Hi,

Mirek, I just want to remind you that AsTable still has wrong postgres syntax:

Current syntax:
select COLUMN from ((select COLUMN from TABLE1) as TABLE2)

Correct syntax:
select COLUMN from (select COLUMN from TABLE1) as TABLE2

Fix:

SqlSet SqlSelect::AsTable(const SqlId& tab) const
{
	StringBuffer t;
	t << SqlCase(MSSQL|PGSQL, "")("(") //<---PGSQL added
	<< "(" << text << ") as \t" << tab.ToString() << '\t'
	<< SqlCase(MSSQL|PGSQL, "")(")");  //<---PGSQL added
	return SqlSet(String(t), SqlSet::HIGH);
}

Report message to a moderator