What is the most graceful way to add a subquery to a table beyond adding a subquery to the synonym? I'll explain:

I have two tables. The first table is the main table I am querying on, (let's say it's ID is MainID). The second table is something like the following, (Scores:MainID relates to the MainID in my main table):

CREATE TABLE [dbo].[Scores]
(
[ScoreID] [uniqueidentifier] NOT NULL,
[Date] [datetime] NOT NULL,
[Score] [int] NOT NULL,
[MainID] [uniqueidentifier] NULL
)

I would like the result of the following query to be easily accessible on every row of the first table:

SELECT TOP 1 Score
FROM Scores WHERE MainID = &MainID
ORDER BY Date DESC;

My first shot at this was the synonym/subquery route but I see it requires an aggregate and I need to be able to order. I then looked at DB_EXPR and tried adding the following as a define:

WITH MainID DB_EXPR(SELECT TOP 1 "Score" FROM Scores ORDER BY "Date" DESC)

But no dice, says the element is not recognized.

Thoughts? Thanks!

This message has been edited. Last edited by: FP Mod Chuck,

Sean

It would be best if you post the entire query not just the sub-query. Do you reference the proper data base connection where scores is located in your query. ie ENGINE SQLMSS SET DEFAULT CONNECTION...

Hi Chuck,

Not sure what you mean, I'm trying to add this subquery at the synonym level so I'm really not dealing with a query yet at that point.

Yes I think everything is fine w.r.t. the DB connection. I have synonyms for my main table and for the table involved in the subquery and both can sample data appropriately.

Thanks for your help.

Sean

Sorry I got confused I thought you were doing the DB_EXPR in a query. The .acx file has the CONNECTION attribute to the database and if they are both the same connection then I don't know why it would fail. You may need to open a case with techsupport for this one.

What I would suggest is to write the SQL query in the actual database, in my case SQL Server Management Studio or Azure DevOps, getting the answer set you would expect, and save it as an .sql file. Maybe something like this:

  
SELECT m.MainID
     , ( SELECT TOP 1 s.Score
         FROM Scores s
         WHERE s.MainID=m.MainID
         ORDER BY s.[Date] DESC ) AS 'fieldName'
FROM dbo.mainTable m;

Then copy that .sql file into the same app path that you are going to make the synonym in.

Then create the synonym from the .sql file. Just change the Restrict object type to setting to External SQL Scripts.



You will need to select the folder (Base Location) that has your .sql file in it.


Then when you click on next, you can select the .sql file and create a master file from it.

This message has been edited. Last edited by: Hallway,