Refer to a Derived Table in a SELECT Subquery

Question

In the code below, the resulting error is "object t does not exist". T is the derived table defined in FROM. Rather than refiltering in the SELECT subquery, I would like to use the derived table to save on processing. Since FROM is processed first in the order of operations, I feel that there should be a way for me to refer to "t" in the SELECT.

(I am in Teradata in case that matters)

SELECT (100000/(SELECT COUNT(DISTINCT EXTRACT(DAY FROM t.saledate))
        FROM t
        WHERE EXTRACT(MONTH FROM t.saledate) = 11)) as "NOVEMBER"
FROM (SELECT sprice, quantity, sku, store, saledate
        FROM trnsact
        WHERE (saledate BETWEEN '2004-11-01' AND '2004-12-31')
            ) as t

Please tag your question with the database you are using. – Gordon Linoff Apr 13 '16 at 23:04 — Gordon Linoff, Apr 13 '16 at 23:04

score 0 · Answer 1 · answered Apr 13 '16 at 22:52

0

If you're using SQL Server you'd do:

;WITH t as
SELECT sprice, quantity, sku, store, saledate
        FROM trnsact
        WHERE (saledate BETWEEN '2004-11-01' AND '2004-12-31')
SELECT (100000/(SELECT COUNT(DISTINCT EXTRACT(DAY FROM t.saledate))
        FROM t
        WHERE EXTRACT(MONTH FROM t.saledate) = 11)) as "NOVEMBER"

I think there are a lot more improvements that can be made to the query, but this is a good start.

answered Apr 13 '16 at 22:52

jhilden

12,207
5
53
76

Thanks for the comment and kind formatting. I actually need "t" as a table because I am joining the derived results to other things (removed all of the extra code to focus on this issue here). I am reading up on "WITH" now to understand if this would work for me. – Michael Nguyen Apr 13 '16 at 22:56

score 0 · Accepted Answer · answered Apr 14 '16 at 00:04

We can achieve the result (it seems like you are going for) with an inline view, or a common table expression. As an example of a using an inline view:

SELECT ( 100000
       / COUNT(DISTINCT
           CASE WHEN EXTRACT(MONTH FROM t.saledate) = 11 
                THEN EXTRACT(DAY FROM t.saledate)
                ELSE NULL
           END
         )
       ) AS "NOVEMBER"
FROM ( SELECT r.sprice
            , r.quantity
            , r.sku
            , r.store
            , r.saledate
         FROM trnsact r
        WHERE r.saledate BETWEEN '2004-11-01' AND '2004-12-31'
     ) t

I'd also want to avoid any potential "divide by zero" error, so I'd wrap that COUNT() expression in a NULLIFZERO function.

We will note that for that resultset, we wouldn't actually need an inline view or CTE. It could be achieved with a much simpler query:

SELECT ( 100000
       / NULLIFZERO(COUNT(DISTINCT EXTRACT(DAY FROM t.saledate)))
       ) AS "NOVEMBER"
  FROM trnsact t
 WHERE t.saledate >= '2004-11-01'
   AND t.saledate <  '2004-12-01'

Thanks! That top example helped a lot. I am curious about why I need the NULLIFZERO (you were right, the divide by zero error did occur with me) - shouldn't the issue only occur if COUNT (in aggregation) turns out to be zero? But since the actual count was not zero, why did I see the issue? — Michael Nguyen, Apr 14 '16 at 06:16
The expression in the SELECT list is doing a wonky division operation. If the trnsact table has rows with saledate in December, but not in November, the COUNT() expression in the example query would return 0. The query is a bit odd. The predicates in the inline view (derived table) query specifies saledate in November and December. The CASE expression in the outer query returns non-NULL values only for saledate in November, that's what makes it possible for the COUNT() aggregate to return 0. (The COUNT doesn't get incremented when the expression being counted evaluates to NULL.) — spencer7593, Apr 14 '16 at 14:09

Refer to a Derived Table in a SELECT Subquery

2 Answers2