Home » U++ Library support » U++ SQL » [SOLVED] How to use aliases with SqlExp?
[SOLVED] How to use aliases with SqlExp? [message #50281] |
Wed, 12 September 2018 08:37 |
Patisab
Messages: 21 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
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 |
|
mirek
Messages: 13975 Registered: November 2005
|
Ultimate Member |
|
|
Patisab wrote on Wed, 12 September 2018 08:37Hello,
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
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 |
Patisab
Messages: 21 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
Best regards.
|
|
|
Re: How to use aliases with SqlExp? [message #50328 is a reply to message #50327] |
Fri, 21 September 2018 11:51 |
|
mirek
Messages: 13975 Registered: November 2005
|
Ultimate Member |
|
|
Patisab wrote on Fri, 21 September 2018 11:08Good 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
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));
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 16:00:42 CEST 2024
Total time taken to generate the page: 0.02672 seconds
|