Skip to Content (Press Enter)
UserImage.jpg
Thiago Porto
239Views
2Comments
SQL SubQuery problem
Question

Hi, i am facing some problems to run a sql statement. I am trying to use an external Id inside the subquery.

The query is ok, if I put a hard code id inside the subquery, it runs normally.



Thank you!

0
0
18 May 2019
Copy Link
2020-02-28 09-46-54
Eduardo Jauch

Hi, 

Can you show here the executed SQL? 

Cheers


0
0
18 May 2019
Copy Link
2023-02-10 19-42-59
João Melo
 
MVP

Hi Thiago,

I tried, but couldn't reproduce the exact error message in your scenario.

But the query you should probably use is this:

SELECT {Table1}.[Numero]
        , ''
        , {Table1}.[Id]
        , (SELECT {Table2}.[Id]
            FROM {Table2}
            WHERE {Table2}.[Table1Id] = {Table1}.[Id]
              AND {Table2}.[ModifiedOn] = (SELECT MAX({Table2}.[ModifiedOn]) FROM {Table2} WHERE {Table2}.[Table1Id] = {Table1}.[Id])
          )
FROM {Table1}


It will get the last record in Table2 associated with Table1, but way more performatic than using order by.

Hope this helps.

1
0
18 May 2019
Copy Link
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.