lbrndnr lbrndnr - 2 years ago 57
SQL Question

How to use a WITH clause in conjuction with a WHERE clause

I've got the following query which is not running:

with countf as (
select nationid, count(*) as c from customer
group by nationid
),
maxf as ( select max(nationid) from customer )

select c.customerid, c.nationid from customer c, countf cf, maxf m
where c.nationid = cf.nationid
and cf.c = m


The problem seems to be that
m
is a record instead of an Integer. However, if I run it as a subquery as follows:

cf.c = ( select max(nationid) from customer )


it works as expected. I presume that I'm using the with statement just not the intended way. Trying

cf.c in maxf


makes me assume that a table generated using
WITH
is just not supposed to be used in a
WHERE
clause.

I know that there are other ways to get the same query using
all
for example. I'm really only interested how I'm supposed to use the with statement. Can I only use it to
SELECT
from it later on?

Thanks in advance for any kind of help.

Answer Source

It's because of the condition and cf.c = m which should be like below

with countf as (
    select nationid, count(*) as c from customer
    group by nationid
),
maxf as ( select max(nationid) as max_nationid from customer )

select c.customerid, c.nationid from customer c, countf cf, maxf m
where c.nationid = cf.nationid
and cf.c = m.max_nationid

Sidenote: Use a proper ANSI style JOIN syntax which is more readable like

select c.customerid, 
c.nationid from customer c
join  countf cf on c.nationid = cf.nationid
join maxf m on cf.c = m.max_nationid
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download