Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Adding subquery result to rows of table, hopefully on a metadata level
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Adding subquery result to rows of table, hopefully on a metadata level
 Login/Join
 
Silver Member
posted
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, 2018Reply With QuoteReport This Post
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: 1428 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
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, 2018Reply With QuoteReport This Post
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: 1428 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Guru
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.



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,


Hallway
WF(Prod):8202M
WF(Test):8202M
OS/Platform:Win 10
Outputs:All
 
Posts: 281 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Adding subquery result to rows of table, hopefully on a metadata level

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.