Home » U++ Library support » U++ SQL » [sql] Select embeded in join
[sql] Select embeded in join [message #22815] |
Tue, 18 August 2009 23:08 |
Zbych
Messages: 325 Registered: July 2009
|
Senior Member |
|
|
Hi,
I need to embed select inside left join. I've found AsValue function, but I could not find anything that converts SqlSelect to SqlId, so I added this:
SqlId AsTable(SqlId table) const { return SqlId(String("(") << text << ")").As(table); }
to class SqlSelect.
Maybe it doesn't look to good, but it works.
|
|
|
Re: [sql] Select embeded in join [message #22821 is a reply to message #22815] |
Wed, 19 August 2009 16:32 |
Zbych
Messages: 325 Registered: July 2009
|
Senior Member |
|
|
One more thing. Right now, when I use AsValue of Select inside Insert:
sql & Insert(USERS)(USER_OID, Select(OID).From(PG_ROLES).Where(ROLNAME == User.GetData()).AsValue() )(USER_NAME, Name.GetData())(USER_SURNAME, Surname.GetData());
sql string looks like this:
insert into USERS(USER_OID, USER_NAME, USER_SURNAME) values (select oid from pg_roles where rolname = 'aaaaaaaa', 'aaaaa', 'aaaaaaa')
There should be another pair of parentheses around select, so AsValue should look like this:
SqlVal AsValue() const { return SqlVal(String("(") << text << ")", SqlVal::LOW); }
[Updated on: Wed, 19 August 2009 16:33] Report message to a moderator
|
|
|
|
Re: [sql] Select embeded in join [message #22844 is a reply to message #22840] |
Fri, 21 August 2009 15:59 |
Zbych
Messages: 325 Registered: July 2009
|
Senior Member |
|
|
luzr wrote on Fri, 21 August 2009 14:30 |
Would it be possible to use other type as return value?
|
All joins expect SqlId type, so it can be any type that can be converted to SqlId. I tried it with String and it works fine:
String AsTable(SqlId table) const { return String("(") << text << ") as " << table.ToString(); }
But maybe all joins should be overloaded and accept SqlSelect?
Sqlexp.h:
class SqlSelect {
[...]
private:
SqlSelect& LeftJoin0(const char * item);
[...]
public:
SqlSelect& LeftJoin(SqlId table) {return LeftJoin0(~table);}
SqlSelect& LeftJoin(SqlSelect select) {return LeftJoin0(~select);}
[...]
String ToString() const { return text; }
String operator~() const { return ToString(); }
[...]
SqlSelect & AsTable(SqlId table);
[...]
SqlStatement.cpp:
SqlSelect& SqlSelect::LeftJoin0(const char * item) {
text << " left outer join " << item;
return *this;
}
SqlSelect & SqlSelect::AsTable(SqlId table)
{
text = String("(") << text << ") as " << ~table;
return *this;
}
[Updated on: Fri, 21 August 2009 21:57] Report message to a moderator
|
|
|
|
|
Re: [sql] Select embeded in join [message #22914 is a reply to message #22879] |
Fri, 28 August 2009 12:06 |
Zbych
Messages: 325 Registered: July 2009
|
Senior Member |
|
|
luzr wrote on Mon, 24 August 2009 15:45 | Please check, I hope this solves the problem.
|
Sorry for the delay. I've checked new version and it appears that postgres doesn't like double brackets. Query like this one:
select COLUMN
from TABLE
left outer join
((select COLUMN from TABLE1) as TABLE2)
on TABLE.COLUMN = TABLE1.COLUMN1
is not accepted, so I had to remove "+1" from join:
SqlSelect& LeftJoin(const SqlSet& set) { return LeftJoin0(~set(SqlSet::SETOP /*+ 1*/)); }
[Updated on: Fri, 28 August 2009 12:08] Report message to a moderator
|
|
|
|
Goto Forum:
Current Time: Sat Apr 27 18:35:21 CEST 2024
Total time taken to generate the page: 0.05264 seconds
|