Basic SQL mechanics, create table, then call that table from another script-CodePudding

I have two scripts that work in isolation, but I don't know how to stitch them together to do this all at once.

The first script:

SELECT *
FROM PROD_ANALYTIC.SRC_MVC_AU_DBO.BCAPPLICATIONQUOTES bq 
JOIN PROD_ANALYTIC.SRC_MVC_AU_DBO.PRODUCT p ON bq.ProductId = p.Id 
JOIN PROD_ANALYTIC.SRC_MVC_AU_DBO.BCAPPLICATIONQUOTEFEES F ON F.ApplicationQuoteId = bq.Id 
LEFT JOIN PROD_ANALYTIC.SRC_MVC_AU_DBO.COVERAGESECTIONTYPE CST ON F.CoverageSectionTypeID = CST.SECTIONID

This produces a table (is that the right word?). Suppose I could save that table as 'appQuote'. I then want to run this script on it:

SELECT
    ApplicationId, 
    STRING_AGG(YEARLYPAYAMOUNT) allPremium,
    STRING_AGG(SECTIONID) allSection,
    STRING_AGG(SHORTNAME) allSectionName,
FROM
    appQuote 
GROUP BY
    ApplicationId

For storage reasons I don't want to actually save the table from the first script, I just want to get the result from the first script and immediately apply the second script to it.

This is very basic, so any guidance would help.

CodePudding user response：

The relevant term is known as a derived table.

In SQL you can use the result of a query as the input to another. The "nested query" produces the derived table which is then consumed by the outer query:

Select <columns>
from (
  select <columns> from table join table etc
)as TableAliasName

CodePudding user response：

To get the nomenclature right, what you have are not scripts or tables, but rather two queries. When a query runs it produces a result set.

To accomplish your goal, either of these queries will work:

SELECT
    ApplicationId, 
    STRING_AGG(YEARLYPAYAMOUNT) allPremium,
    STRING_AGG(SECTIONID) allSection,
    STRING_AGG(SHORTNAME) allSectionName,
FROM (
    SELECT *
    FROM PROD_ANALYTIC.SRC_MVC_AU_DBO.BCAPPLICATIONQUOTES bq 
    JOIN PROD_ANALYTIC.SRC_MVC_AU_DBO.PRODUCT p ON bq.ProductId = p.Id 
    JOIN PROD_ANALYTIC.SRC_MVC_AU_DBO.BCAPPLICATIONQUOTEFEES F ON F.ApplicationQuoteId = bq.Id 
    LEFT JOIN PROD_ANALYTIC.SRC_MVC_AU_DBO.COVERAGESECTIONTYPE CST ON F.CoverageSectionTypeID = CST.SECTIONID
) appQuote 
GROUP BY
    ApplicationId

Here, the inner nested query is formally called a derived table, but people will often use the phrase "subquery".

The other option is a Common Table Expression (CTE):

With appQuote As (
    SELECT *
    FROM PROD_ANALYTIC.SRC_MVC_AU_DBO.BCAPPLICATIONQUOTES bq 
    JOIN PROD_ANALYTIC.SRC_MVC_AU_DBO.PRODUCT p ON bq.ProductId = p.Id 
    JOIN PROD_ANALYTIC.SRC_MVC_AU_DBO.BCAPPLICATIONQUOTEFEES F ON F.ApplicationQuoteId = bq.Id 
    LEFT JOIN PROD_ANALYTIC.SRC_MVC_AU_DBO.COVERAGESECTIONTYPE CST ON F.CoverageSectionTypeID = CST.SECTIONID
)
SELECT
    ApplicationId, 
    STRING_AGG(YEARLYPAYAMOUNT) allPremium,
    STRING_AGG(SECTIONID) allSection,
    STRING_AGG(SHORTNAME) allSectionName,
FROM
    appQuote 
GROUP BY
    ApplicationId

Finally, you could also create a View from the first query:

CREATE View AppQuote As
    SELECT *
    FROM PROD_ANALYTIC.SRC_MVC_AU_DBO.BCAPPLICATIONQUOTES bq 
    JOIN PROD_ANALYTIC.SRC_MVC_AU_DBO.PRODUCT p ON bq.ProductId = p.Id 
    JOIN PROD_ANALYTIC.SRC_MVC_AU_DBO.BCAPPLICATIONQUOTEFEES F ON F.ApplicationQuoteId = bq.Id 
    LEFT JOIN PROD_ANALYTIC.SRC_MVC_AU_DBO.COVERAGESECTIONTYPE CST ON F.CoverageSectionTypeID = CST.SECTIONID

A view is not just a query; it will actually become part of the database, such that you can use it in many of the same ways you would use a table saved on disk, and then the second query in the original question will run unmodified.