How to use substitution variable in SQL to prompt user to enter customer name?

Question

The question is as below:

Determine which books a particular customer has purchased. Perform the search using the customer name, not the customer number. Using substitution variable and enter Jack Lucas as customer name. If he has purchased multiple copies of the same book, unduplicate the results.

---

So basically I have 4 tables to join, including Customers, Orders, Orderitems, and Book. I want to prompt user to enter Jack Lucas. And display the result of book title that Jack Lucas has purchased.

SELECT b.title
FROM customers c
JOIN orders o
ON c.customerid = o.customerid
JOIN orderitems oi
ON o.orderno = oi.orderno
JOIN books b
ON oi.isbn = b.isbn
WHERE c.firstname = &firstname
AND c.firstname = 'JAKE';

I have tried to run the code above, but after I keyed in 'JAKE', it shows me this error,

ORA-00904: "JAKE": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause:
*Action: Error at Line: 9 Column: 21

I wonder where I did wrong. Please advise. Thank you in advance.

Answer 1

Add ' single quotes around the & variable.

In WHERE clause instead of two condition check for the same column name with AND, use any one. So the below code will work.

SELECT b.title
FROM customers c
JOIN orders o ON c.customerid = o.customerid
JOIN orderitems oi ON o.orderno = oi.orderno
JOIN books b ON oi.isbn = b.isbn
WHERE c.firstname = '&firstname'