2
select
  t.col1,
  t.col2,
  a.col1,
  b.somecolumn
  VQ.a,
  VQ.b,
  VQ.e,
  VQ.d,
  VQ.f,  
  (select
      t.status as a,
      p.id as b,
      p.permit as c,
      p.des as d,
      p.error_code as e,
      p.cause as f
   from table_A t 
   inner join table_B p on t.a = p.a 
   where p.c = 'license' and t.status = 'Fail'
  ) as VQ
from table_A t 
join table_C a on t.col1 = a.asset_id 
join table_b b on t.somecolumn = b on somecolumn;
   
   

When I execute the above code, I encounter the error

SQL Error [42601]: ERROR: navigation on column "vq" is not allowed as it is not SUPER type

I am trying to do a select inside select.

3
  • 3
    You can't use a whole select inside another one, unless it is a single value. And it's anyway rather strange, as the subquery is not correlated to the outer query. What are you actually trying to do? Sep 12, 2021 at 3:56
  • Im trying to get a table with all the values. joining table A and B based on a condition and combining with the remaining columns from the other join Sep 12, 2021 at 5:38
  • I don't get how you want to do that, given that you are not correlating it. A cross-join? Perhaps you just need another join to table_B on the outer query and remove the subquery completely. Sample data and expected results would help Sep 12, 2021 at 5:53

2 Answers 2

0

I think you want this:

select
  t.col1,
  t.col2,
  a.col1,
  b.somecolumn
  VQ.a,
  VQ.b,
  VQ.e,
  VQ.d,
  VQ.f  
FROM (select
      t.status as a,
      p.id as b,
      p.permit as c,
      p.des as d,
      p.error_code as e,
      p.cause as f
   from table_A t 
   inner join table_B p on t.a = p.a 
   where p.c = 'license' and t.status = 'Fail'
  ) as VQ
, table_A t 
join table_C a on t.col1 = a.asset_id 
join table_b b on t.somecolumn = b on somecolumn;
   
   

or something like that, perhaps.

0

We can't select FROM a table that doesn't live in the FROM part of the query.

Current, table VQ lives inside of a single row of the results. So the rest of the query cannot select from it.

One solution would be to refactor and move your VQ table into the FROM section of the query as needed.

EDIT: Additionally, we cannot select multiple fields back into a single row. So yes, this query is broken in multiple ways.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.