CODEWITHSUNDEEP
joinsql
Eric
Eric

Reputation: 601

SQL Syntax Issue

I'm joining two tables and making a simple count, but I can't seem to rename the joined key variable into something more appropriate for the two tables, I keep getting the error '"CUSTOMER_NO" is not valid in the context where it is used.' I'm sure it's just a little syntax error, but I can't see it...

SELECT owner_no AS customer_no,

CASE
WHEN customer_no BETWEEN 5000 and 5999 THEN 'RENTER'
WHEN customer_no BETWEEN 6000 and 6999 THEN 'OWNER'
END AS customer_type

FROM owner_phone AS op
INNER JOIN renter_phone AS rp ON op.owner_no = rp.renter_no

GROUP BY customer_no
HAVING COUNT(*) > 1;

Upvotes: 0

Views: 66

Answers (2)

Ken White
Ken White

Reputation: 125708

Use the actual column name in your CASE and GROUP BY, not the aliased column name.

CASE
WHEN owner_no BETWEEN 5000 and 5999 THEN 'RENTER'
WHEN owner_no BETWEEN 6000 and 6999 THEN 'OWNER'
END AS customer_type

FROM owner_phone AS op
INNER JOIN renter_phone AS rp ON op.owner_no = rp.renter_no
GROUP BY owner_no
HAVING Count(*) > 1;

Upvotes: 3

jimconstable
jimconstable

Reputation: 2388

You have to use OWNER_NO through the rest of your query but leave the AS CUSTOMER_NO to make that the column name. 

SELECT owner_no AS customer_no,
CASE
    WHEN owner_no BETWEEN 5000 and 5999 THEN 'RENTER'
    WHEN owner_no BETWEEN 6000 and 6999 THEN 'OWNER'
END AS customer_type
FROM owner_phone AS op
INNER JOIN renter_phone AS rp ON op.owner_no = rp.renter_no
GROUP BY owner_no 
HAVING COUNT(*) > 1;

Upvotes: 2

Related Questions