multiple subquery error "invalid identifier"

Question

Im working on this code but i keep getting invalid identifer for t2.nabp_num

 `with t1 as (query1),
   t2 as (query2)
   Select t1.*, t2.device_count, 
   d.* from t1 
   inner join t2 on
   t1.nabp_num = t2.nabp_num and
   t1.dt = t2.dt and
   t1.d_member = t2.d_member
   inner join drug_product d on
   t1.d_product_id = d.product_id

   order by claim_count desc;`

i get invalid identifier

Answer 1

As already commented, you can't reference something that doesn't exist.

It would certainly help if you posted actual query instead of invalid

with t1 as (query1          --> what is "query1"?
     Order by ...

---

Anyway: as NABP_NUM is referenced here:

inner join t2 on t1.nabp_num = t2.nabp_num

it means that it has to be part of both t1 and t2. However, as t2 CTEs result is derived from t1, maybe you don't need t1 at all ...

If you add all columns that are currently missing in either select column list or group by clause, query would look like this (see comments within code):

WITH
   t1
   AS
      (SELECT d_member_id,
              dt,
              device_type,
              claim_id,
              nabp_num,          --> add NABP_NUM
              d_member_hq_id     --> add D_MEMBER_HQ_ID
              d_drug_product_id  --> add D_DRUG_PRODUCT_ID
         FROM some_table         --> which table?
      ),       --> remove ORDER BY, it is useless here
   t2
   AS
      (  SELECT d_member_id,
                dt,
                nabp_num,        --> add NABP_NUM
                d_member_hq_id,  --> add D_MEMBER_HQ_ID
                COUNT (DISTINCT device_type) AS device_count,
                COUNT (DISTINCT claim_ID) AS claim_count
           FROM t1
       GROUP BY d_member_id, dt, nabp_num, d_member_hq_id)   --> add NABP_NUM and D_MEMBER_HQ_ID
  SELECT t1.*, t2.device_count, d.*
    FROM t1
         INNER JOIN t2
            ON     t1.nabp_num = t2.nabp_num
               AND t1.dt = t2.dt
               AND t1.d_member_hq_id = t2.d_member_hq_id
         INNER JOIN vmd_drug_product d
            ON t1.d_drug_product_id = d.d_drug_product_id
ORDER BY t2.claim_count DESC;

Even though this shouldn't return any syntax errors any more (presuming columns used here really exist in some_table), I can't tell whether this will - or will not - return desired result.