[CLOSED] Adding subquery result to rows of table, hopefully on a metadata level

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,

Windows 8203 All output formats

Posts: 47 | Registered: November 30, 2018

FP Mod Chuck

Virtuoso

posted

Hide Post

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...

Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats

Posts: 2127 | Location: Customer Support | Registered: April 12, 2005

Ignored post by FP Mod Chuck posted

Show Post

Sean Sweeney

Silver Member

posted

Hide Post

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.

Windows 8203 All output formats

Posts: 47 | Registered: November 30, 2018

Ignored post by Sean Sweeney posted

Show Post

FP Mod Chuck

Virtuoso

posted

Hide Post

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.

Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats

Posts: 2127 | Location: Customer Support | Registered: April 12, 2005

Ignored post by FP Mod Chuck posted

Show Post

Hallway

Master

posted

Hide Post

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.