SQL Syntax Issue

Question

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;

Ken White · Accepted Answer · 2011-03-16 02:25:01Z

3

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;

answered Mar 16, 2011 at 2:25

Ken White

126k15 gold badges237 silver badges476 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Eric Over a year ago

@jimconstable : You were both right, thank you. Second minimal question, if owner_no and renter_no contain different values, I should use the union function instead of the inner join correct?

Ken White Over a year ago

@Eric, what connects owner_no and renter_no. Is it a building ID, an address, something else? Perhaps you can post a new question which shows your database schema, some sample data, and the outcome you're looking to reach, and we can help you get there.

jimconstable · Accepted Answer · 2011-03-16 02:23:42Z

2

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;

answered Mar 16, 2011 at 2:23

jimconstable

2,39819 silver badges28 bronze badges

Collectives™ on Stack Overflow

SQL Syntax Issue

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related