Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » U++ Library support » U++ SQL » [sql] Select embeded in join
[sql] Select embeded in join [message #22815] Tue, 18 August 2009 23:08 Go to next message
Zbych is currently offline  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 Go to previous messageGo to next message
Zbych is currently offline  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 #22840 is a reply to message #22815] Fri, 21 August 2009 14:30 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
Zbych wrote on Tue, 18 August 2009 17:08

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.



Would it be possible to use other type as return value?

The problem is that the way SqlId works, all 'ids' are stored *permanently* (and then only integer indexes to the permanent Index are used). Means, using SqlId here could be considered a sort of leak...

Mirek
Re: [sql] Select embeded in join [message #22844 is a reply to message #22840] Fri, 21 August 2009 15:59 Go to previous messageGo to next message
Zbych is currently offline  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 #22872 is a reply to message #22821] Mon, 24 August 2009 10:58 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
Zbych wrote on Wed, 19 August 2009 10:32


String("(") << text << ")"




Note: This does not work in GCC, you need

String("(").Cat() << text << ")"
Re: [sql] Select embeded in join [message #22879 is a reply to message #22872] Mon, 24 August 2009 15:45 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
Well, there does not seem to be ideal solution (w.r.t. parameter type of Join).

Anyway, there already is a precendent with From(SqlSet). Therefore I have added SqlSet variants to Joins. AsTable returns SqlSelect (which has conversion to SqlSet).

Please check, I hope this solves the problem.

Mirek
Re: [sql] Select embeded in join [message #22914 is a reply to message #22879] Fri, 28 August 2009 12:06 Go to previous messageGo to next message
Zbych is currently offline  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

Re: [sql] Select embeded in join [message #22924 is a reply to message #22914] Sat, 29 August 2009 00:18 Go to previous message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
Zbych wrote on Fri, 28 August 2009 06:06

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*/)); }







ok.
Previous Topic: Possible SqlOption Problem [Solved]
Next Topic: Postgresql - execpt vs minus
Goto Forum:
  


Current Time: Thu Mar 28 12:27:25 CET 2024

Total time taken to generate the page: 0.02542 seconds