I have the following query:
select distinct encoder.encoder enc, src_ip, encoder.datacenter
from logical_service, unnest(tags) enc, encoder, mpeg_out
where enc like 'encoder:%'
and substring(enc,9) = encoder.encoder
and logical_service.lid=mpeg_out.lid
and encoder.datacenter=mpeg_out.datacenter
order by encoder.encoder
I would like to give this query a name so that I can later use it. I was trying to do this, but this ultimately fails:
with encoder_alias as (
select distinct encoder.encoder enc, src_ip, encoder.datacenter
from logical_service, unnest(tags) enc, encoder, mpeg_out
where enc like 'encoder:%'
and substring(enc,9) = encoder.encoder
and logical_service.lid=mpeg_out.lid
and encoder.datacenter=mpeg_out.datacenter
order by encoder.encoder
)
It does not give any output as to why this is not working. Any idea why I can't use the with as to give it the name?
A common table expression needs to be followed by a final SELECT statement:
with encoder_alias as (
select distinct encoder.encoder enc, src_ip, encoder.datacenter
from logical_service
cross join unnest(tags) enc
join encoder on substring(enc,9) = encoder.encoder
join mpeg_out on logical_service.lid = mpeg_out.lid
and encoder.datacenter = mpeg_out.datacenter
where enc like 'encoder:%'
order by encoder.encoder
)
select * ---<<< here
from encoder_alias;
(note that I replaced your ancient, outdated and fragile implicit joins with "modern" (30 years old) explicit JOIN
operators)