Select the fields from different tables

Question

I need to select different fields from different databases. One of the field is selecting the maximum of the LastUpdateOn datetime field from different tables.

{Project},{Document},{Instruction},{Document}, {User}, {ProjectParticipant} are the different tables

SELECT {Project}.[Number],{Project}.[Name],{User}.[Last_Login],{A}.[max_LastUpdatedOn]

From {Project}

INNER JOIN {ProjectParticipant} ON {Project}.[Id] = {ProjectParticipant}.[ProjectId]
INNER JOIN {User} ON {ProjectParticipant}.[UserId] = {User}.[Id] AND {User}.[Username] = @UserId
INNER JOIN {Document} ON {Project}.[Id] = {Document}.[ProjectId]
INNER JOIN {InstructionType} ON {Project}.[Id] = {Instruction}.[ProjectId]
INNER JOIN {Question}  ON {Project}.[Id] = {Question}.[ProjectId]

SELECT MAX(LastUpdatedOn) AS max_LastUpdatedOn
FROM 
(
  SELECT LastUpdatedOn FROM {Question}
  UNION ALL
  SELECT LastUpdatedOn FROM {Document}
  UNION ALL
  SELECT LastUpdatedOn FROM {Instruction}
) A;

GROUP BY {Project}.[Number],
         {Project}.[Name],
         {User}.[Last_Login]
         {A}.[max_LastUpdatedOn]
ORDER BY {Project}.[Number]

But getting an error saying {A} is an unknown entity. I am not sure what is wrong here. Tried using the

SELECT {Project}.[Number],{Project}.[Name],{User}.[Last_Login],
(SELECT MAX(LastUpdatedOn) as max_LastUpdatedOn
 FROM
 (
  SELECT {Question}.[LastUpdatedOn] where {Project}.[Id] = {Question}.[ProjectId]
  UNION ALL
  Select {Document}.[LastUpdatedOn] where {Project}.[Id] = {Document}.[ProjectId]
  UNION ALL
  SELECT {Instruction}.[LastUpdatedOn] where {Project}.[Id] = {Instruction}.[ProjectId]
  )
  A ) [max_LastUpdatedOn]

 FROM {Project}
 INNER JOIN {ProjectParticipant} ON {Project}.[Id] = {ProjectParticipant}.[ProjectId]
 INNER JOIN {User} ON ({ProjectParticipant}.[UserId] = {User}.[Id] AND {User}.[Username] = @UserId)
 INNER JOIN {Question}  ON {Project}.[Id] = {Question}.[ProjectId]
 INNER JOIN {Document} ON {Project}.[Id] = {Document}.[ProjectId]
 INNER JOIN {Instruction} ON {Project}.[Id] = {Instruction}.[ProjectId]

 GROUP BY {Project}.[Number],
          {Project}.[Name],
          {User}.[Last_Login],
          A.[max_LastUpdatedOn]
 ORDER BY {Project}.[Number]

Getting an error like The multi-part identifier "A.max_LastUpdatedOn" could not be bound.

for the love of Pete get rid of the squigglies. What is A. If A is a database how would it even work — Drew, Jun 28 '15 at 15:50
A stores the maximum of the LastUpdatedOn fields from Question Document and Instruction tables — trx, Jun 28 '15 at 15:52
There's quite a few problems; you're using invalid syntax (the `{}` and `[]` which are not valid in MySQL), and just plugging the query that gets the last_updated into another query won't work. It would be best if you specified your tables and how they are connected and what you want to do. — jpw, Jun 28 '15 at 15:53
I am using the Sql Advanced query in Outsystems, I need to use {} for table and [] for field. — trx, Jun 28 '15 at 15:54
A is having the selected maximum of the LastUpdatedOn fields from Question Document and Instruction tables — trx, Jun 28 '15 at 16:02
Maybe you are in the wrong database. Maybe the command is `use !dbname¡` ;) — Drew, Jun 28 '15 at 16:03
How do I select the here the maximum of LastUpdatedOn which are on the different tables here. — trx, Jun 28 '15 at 16:11
Take a look at this: http://pastebin.com/FUQUdYH7 It might be what you want if what you want is to get the lastupdated value per projectid. — jpw, Jun 28 '15 at 16:12

score 0 · Accepted Answer · edited May 23 '17 at 11:43

{A} is not an OutSystems Entity, therefore you should not use curly braces to refer it. Curly braces are used to translate from the meta model name in design time to the physical table name in the database.

Since A is a simple alias of the inner select statement, just using A should be enough to make it work. For example:

SELECT {Project}.[Number],{Project}.[Name],{User}.[Last_Login],A.[max_LastUpdatedOn]

UPDATE

Regarding the error about The multi-part identifier could not be bound, it has to do with grouping by something that is not in the from/join sources of the query.

I haven’t tried this code below, but something like this should do the trick:
(also, see here more ways on how to select the SQL MAX of multiple columns?)

SELECT {Project}.[Number], {Project}.[Name], {User}.[Last_Login], 
    CASE
        WHEN {Question}.[LastUpdatedOn] >= {Document}.[LastUpdatedOn] AND {Question}.[LastUpdatedOn] >= {Instruction}.[LastUpdatedOn]
        THEN {Question}.[LastUpdatedOn]
        WHEN {Document}.[LastUpdatedOn] >= {Instruction}.[LastUpdatedOn] THEN {Document}.[LastUpdatedOn]
        ELSE {Instruction}.[LastUpdatedOn]
    END AS max_LastUpdatedOn
FROM {Project}
INNER JOIN {ProjectParticipant} ON {Project}.[Id] = {ProjectParticipant}.[ProjectId]
INNER JOIN {User} ON ({ProjectParticipant}.[UserId] = {User}.[Id] AND {User}.[Username] = @UserId)
INNER JOIN {Question}  ON {Project}.[Id] = {Question}.[ProjectId]
INNER JOIN {Document} ON {Project}.[Id] = {Document}.[ProjectId]
INNER JOIN {Instruction} ON {Project}.[Id] = {Instruction}.[ProjectId]
GROUP BY {Project}.[Number], 
         {Project}.[Name], 
         {User}.[Last_Login]
ORDER BY {Project}.[Number]

I tried using just A as shown in the question but even that is giving me an error like The multi-part identifier "A.max_LastUpdatedOn" could not be bound. — trx, Jun 30 '15 at 12:53
Ah, but that’s another issue… the error saying “{A} is an unknown entity” is fixed by removing the curly braces. “The multi-part identifier could not be bound” is caused by trying to `GROUP BY` on something that isn’t present in any of the `FROM`/`JOIN` clauses. — Hugo Ferreira, Jun 30 '15 at 19:07

Select the fields from different tables

1 Answers1