Question: SQL Custfirstname invalid identifier

Question

SQL Custfirstname invalid identifier

Answers 2
Added at 2017-09-06 17:09
Tags
Question

I have 2 questions:

  1. Is this code right for what it's supposed to do? (explained below)
  2. What's wrong with custfirstname that's making it be an invalid identifier?

This is the error message: ORA-00904: "CUSTFIRSTNAME": invalid identifier 00904. 00000 - "%s: invalid identifier"

This is what the code is supposed to do: "Identify all customers living in the most populous area code. Display their name in Last Name, First Name format, the city and state in which they live, and their telephone number."

SELECT custlastname || ', ' || custfirstname as custname, postalcode, city, state, phone
FROM (SELECT custlastname || ', ' || custfirstname as custname, postalcode, city, state, phone FROM customer
      ORDER BY  postalcode desc, custname)
WHERE rownum = 1; 

Here's a screenshot of the tables in the database: http://imgur.com/a/HESbS

So I took out "as subquery" and I got a result, so that's progress! This is the result: Spaller, Kimber 99836 Sitka AK 878-119-5448

Looking at the result above, it doesn't seem quite what the question is asking (Identify all customers living in the most populous area code). How can I change my code so that it does that?

Answers to

SQL Custfirstname invalid identifier

nr: #1 dodano: 2017-09-06 17:09

Your custfirstname and custlastname fields are not in context in the outer query. You already created the custname field in your subquery, so you must use that in our outer query:

SELECT custname, postalcode, city, state, phone
FROM (SELECT custlastname || ', ' || custfirstname as custname, postalcode, city, state, phone FROM customer
      ORDER BY  postalcode desc, custname) as subquery
WHERE rownum = 1;

The subquery also requires an Alias. Here I've named it "Subquery". The subquery here acts as a derived table, so the outer query is referring a derived table that has the custname, postalcode, city, state, and phone columns in it (as defined by the subquery), which is why the custfirstname and custlastname fields are out of context and giving you an error in the outer query.

nr: #2 dodano: 2017-09-06 17:09

Your code is not correct. For instance, it will only return one row.

If I interpret the question as "what all all customers in the postal code with the most customers", then the following would work and it makes use of window functions:

SELECT custlastname || ', ' || custfirstname as custname, postalcode, city, state, phone
FROM (SELECT c.*,
             MAX(num_customers) OVER () as max_num_customers
      FROM (SELECT c.*,
                   COUNT(*) OVER (PARTITION BY postalcode) as num_customers
            FROM customer c
           ) c
     ) c
WHERE max_num_customers = num_customers;
Source Show
◀ Wstecz