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 » [SOLVED] How to use aliases with SqlExp?
icon12.gif  [SOLVED] How to use aliases with SqlExp? [message #50281] Wed, 12 September 2018 08:37 Go to next message
Patisab is currently offline  Patisab
Messages: 18
Registered: December 2015
Location: France
Promising Member
Hello,

I have a little problem with SqlExp when i want to use an alias for a table used twice in same request. It's something like this :

SELECT t1.id, t1.col1, t1.col2, t1.father, t2.col1, t2.col2
FROM table1 AS t1
INNER JOIN table1 AS t2 ON t2.id = t1.father

In .sch file i declare this :

TABLE_(TABLE1)
	INT_	(ID)   PRIMARY_KEY AUTO_INCREMENT NOT_NULL
	STRING_	(COL1,10)  UNIQUE NOT_NULL INDEX
	STRING_	(COL2,50)
	INT_	(FATHER)   UNIQUE NOT_NULL
END_TABLE

The SqlExp used is like this :

SQL & Select(ID.Of(TABLE),COL1.Of(TABLE),COL2.Of(TABLE),COL1.Of(T2),COL2.Of(T2))
.From(TABLE)
.InnerJoin(TABLE).AsTable(T2).On(ID.Of(T2) == FATHER.Of(TABLE));

Compiling send this message :

error: 'T2' was not declared in this scope


Could someone have an idea to solve this problem? Well, i know it is possible to take an other way but i want to use SqlExp... as possible it is Wink

Thank very much for your help.

Best regards

[Updated on: Mon, 24 September 2018 13:46]

Report message to a moderator

Re: How to use aliases with SqlExp? [message #50324 is a reply to message #50281] Wed, 19 September 2018 13:50 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 11991
Registered: November 2005
Ultimate Member
Patisab wrote on Wed, 12 September 2018 08:37
Hello,

I have a little problem with SqlExp when i want to use an alias for a table used twice in same request. It's something like this :

SELECT t1.id, t1.col1, t1.col2, t1.father, t2.col1, t2.col2
FROM table1 AS t1
INNER JOIN table1 AS t2 ON t2.id = t1.father

In .sch file i declare this :

TABLE_(TABLE1)
	INT_	(ID)   PRIMARY_KEY AUTO_INCREMENT NOT_NULL
	STRING_	(COL1,10)  UNIQUE NOT_NULL INDEX
	STRING_	(COL2,50)
	INT_	(FATHER)   UNIQUE NOT_NULL
END_TABLE

The SqlExp used is like this :

SQL & Select(ID.Of(TABLE),COL1.Of(TABLE),COL2.Of(TABLE),COL1.Of(T2),COL2.Of(T2))
.From(TABLE)
.InnerJoin(TABLE).AsTable(T2).On(ID.Of(T2) == FATHER.Of(TABLE));

Compiling send this message :

error: 'T2' was not declared in this scope


Could someone have an idea to solve this problem? Well, i know it is possible to take an other way but i want to use SqlExp... as possible it is Wink

Thank very much for your help.

Best regards


Well, obviously, being an identified in C++ code, it has to be declared somewhere.

For schema IDsthe magic trick is done by that '_' after the type name (STRING_). This says "And also declare this as SqlId variable".

If you are using ID that is not in .sch file, you have to declare it yourself, e.g.

SqlId T1("T1");

as there is some repetition (you have to type T1 twice), there is also a macro that can be used for the same purpose

SQLID(T1);

Alternatively, it is even possible to use C literal instead

COL1.Of("T2")

Last hint: There is a relatively recently added alternative to 'As', operator(), which is especially useful it you are reading more columns from single table:

Select(TABLE(ID ,COL1, COL2), T2(COL1, COL2))

(unfortunately, you cannot use "T2" here, you have to declare it as SQLID).

Mirek
Re: How to use aliases with SqlExp? [message #50327 is a reply to message #50324] Fri, 21 September 2018 11:08 Go to previous messageGo to next message
Patisab is currently offline  Patisab
Messages: 18
Registered: December 2015
Location: France
Promising Member
Good morning Mirek,

When i declare the alias T2 for table T1 as an other ID (with SqlId or with SQLID macro i have compiling errors. This

SqlId T2("T1");
SQL & Select(ID.Of(TABLE),COL1.Of(TABLE),COL2.Of(TABLE),COL1.Of(T2),COL2.Of(T2))
.From(TABLE)
.InnerJoin(TABLE).AsTable(T2).On(ID.Of(T2) == FATHER.Of(TABLE));


results

error: 'class Upp::SqlSet' has no member named 'On'


and that

SqlId T2("T1");
SQL & Select(ID.Of(TABLE),COL1.Of(TABLE),COL2.Of(TABLE),COL1.Of(T2),COL2.Of(T2))
.From(TABLE)
.InnerJoin(T2).On(ID.Of(T2) == FATHER.Of(TABLE));


results

note:   crosses initialization of 'Upp::SqlId T2'


Sorry, i probably not understand your explanations Crying or Very Sad

Best regards.
Re: How to use aliases with SqlExp? [message #50328 is a reply to message #50327] Fri, 21 September 2018 11:51 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 11991
Registered: November 2005
Ultimate Member
Patisab wrote on Fri, 21 September 2018 11:08
Good morning Mirek,

When i declare the alias T2 for table T1 as an other ID (with SqlId or with SQLID macro i have compiling errors. This

SqlId T2("T1");
SQL & Select(ID.Of(TABLE),COL1.Of(TABLE),COL2.Of(TABLE),COL1.Of(T2),COL2.Of(T2))
.From(TABLE)
.InnerJoin(TABLE).AsTable(T2).On(ID.Of(T2) == FATHER.Of(TABLE));


results

error: 'class Upp::SqlSet' has no member named 'On'


and that

SqlId T2("T1");
SQL & Select(ID.Of(TABLE),COL1.Of(TABLE),COL2.Of(TABLE),COL1.Of(T2),COL2.Of(T2))
.From(TABLE)
.InnerJoin(T2).On(ID.Of(T2) == FATHER.Of(TABLE));


results

note:   crosses initialization of 'Upp::SqlId T2'


Sorry, i probably not understand your explanations Crying or Very Sad

Best regards.


Ah, sorry I have missed improper use of AsTable. That has a little bit different meaning... (see https://www.ultimatepp.org/src$Sql$SqlExp_en-us.html)

Try this:

SqlId T2("T2");
SQL & Select(ID.Of(TABLE),COL1.Of(TABLE),COL2.Of(TABLE),COL1.Of(T2 ),COL2.Of(T2))
.From(TABLE)
.InnerJoin(TABLE.As(T2)).On(ID.Of(T2) == FATHER.Of(TABLE));

or this:

SqlId T2("T2");
SQL & Select(TABLE(ID ,COL1, COL2), T2(COL1, COL2))
.From(TABLE)
.InnerJoin(TABLE.As(T2).On(ID.Of(T2) == FATHER.Of(TABLE));

BTW, I think you will also need to alias T2.COL1, T2.COL2 to retrieve proper values after fetch...

SqlId T2("T2");
SQL & Select(TABLE(ID ,COL1, COL2), T2(COL1.As("T2_COL1"), COL2.As("T2_COL2")))
.From(TABLE)
.InnerJoin(TABLE.As(T2).On(ID.Of(T2) == FATHER.Of(TABLE));
[SOLVED] How to use aliases with SqlExp? [message #50330 is a reply to message #50328] Mon, 24 September 2018 12:26 Go to previous message
Patisab is currently offline  Patisab
Messages: 18
Registered: December 2015
Location: France
Promising Member
Hi Mirek,

Your ideas are right. I have good results with this :

SqlId T2("T2");
SQL & Select(ID.Of(TABLE),COL1.Of(TABLE),COL2.Of(TABLE),COL1.Of(T2).As("COL1T2"),COL2.Of(T2).As("COL2T2"))
.From(TABLE)
.InnerJoin(TABLE.As(T2)).On(ID.Of(T2) == FATHER.Of(TABLE));


Thank you very much, Mister Fabulous Wink
Previous Topic: Image in QLITE3 database
Next Topic: How to manage the access to a Db on an unstable connection
Goto Forum:
  


Current Time: Tue Aug 20 16:06:08 CEST 2019

Total time taken to generate the page: 0.01093 seconds