0

So I followed the directions that I was given to me in my live lecture word for word and also researched this in my textbook and I've done everything (So I think) properly but I keep getting a error Which I have looked up to see what it means and it didn't help me at all and I've been working on it for a whole day so I'm reaching out to you guys to see if you can see my mistake anywheres in my code

This is the question that I am trying to complete:

Using the BOOK_CUSTOMER table and the NVL function, create a query that will return a list containing the customer number, first name, last name, and the characters ‘NOT REFERRED’ if the customer was not referred by another customer. Give the derived column an alias of REFERRED BY. Do not list any customers that were referred by another customer.

MY CODE = 

SELECT CutomerID, FirstName, LastName,
NVL(TO_CHAR(Referred), 'Not Referred'))
FROM Book_Customer;

I also realize that I haven't completed the whole question. I'm just trying to get my NVL to work first and then go onto the Alias and the last part of the question because I do not know how to do either of those yet, any tips on that would be greatly appreciated as well

shree.pat18
  • 21,449
  • 3
  • 43
  • 63
Nicolle Chase
  • 21
  • 2
  • 7

2 Answers2

0

There's an extra (unmatched) closing paren in your SQL text, and that's going to throw a syntax error.

NVL(TO_CHAR(Referred), 'Not Referred'))
                                      ^

There's no matching opening paren for that last paren.

To assign an alias to an expression in the SELECT list, follow the expression with the keyword AS and an alias.

 SELECT t.foo AS bar

The resultset will contain a column named bar.

SELECT b.CustomerID
     , b.FirstName
     , b.LastName
     , NVL(TO_CHAR(b.Referred),'Not Referred') AS ReferredBy
  FROM Book_Customer b
 WHERE b.Referred IS NULL
spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • So I took away the ) and added the alias name but I get an error still stating that the FROM keyword is not found in the right place. I get this error all the time and I've looked it up and it doesn't give much help on what it is I need to fix. Any ideas why my code won't work now when all I did was add a simple alias ? SELECT CustomerID, FirstName, LastName, NVL(TO_CHAR(Referred), 'Not Referred') AS 'Referred By' FROM Book_Customer; – Nicolle Chase Feb 13 '15 at 05:34
  • You have a string literal where you need an identifier. It's invalid to put a string literal after the `AS` keyword. Identifiers are *not* enclosed in single quotes, a value enclosed in single quotes is interpreted as a string literal. In Oracle SQL, identifiers can be enclosed in double quotes, e.g. `AS "REFERRED BY"`. – spencer7593 Feb 13 '15 at 06:40
  • Thanks so much spencer7593 ! That worked. Now I'm stuck on the part where I need to NOT list any of the customers that were referred by other customers. I'm so bad with word problems and i can't quite wrap my head around exactly what I need to do to accomplish this. I was thinking a subquery but I don't even know what to put inside of it. Like I said I'm awful at word/math problems. – Nicolle Chase Feb 13 '15 at 18:03
  • @NicolleChase: What does the `Referred` column contain? The `NVL(a,b)` function is equivalent to `CASE WHEN a IS NULL THEN b ELSE a END`. In the example, you returned the string `'Not Referred'` as an alias `ReferredBy`, when the `Referred` column had a value of `NULL`. Seems like all you would need to do is add a predicate (condition) in the WHERE clause that excludes any rows where the `Referred` column has a value of NULL. Unless there's some other check that needs to be done, on the contents of the `Referred` column. But I don't know what that column contains, so I'm just speculating. – spencer7593 Feb 13 '15 at 19:14
  • @NicolleChase: The query in my answer includes that condition... `WHERE b.Referred IS NULL`. – spencer7593 Feb 13 '15 at 19:18
  • So the column referred is the BOOK_CUSTOMER table and the column has whether a customer has referred a book to another customer or whether they have not. For the customer's who HAVE referred a book to another customer their customer ID shows up in the Referred column other wise it shows up NULL which is then transformed into Not Referred. I need to NOT LIST any of the customers that were referred by other customers. So I guess I just need to show the Not Referred ones from what I gather? – Nicolle Chase Feb 13 '15 at 20:20
  • @NIcolleChase: That seems like a reasonable interpretation of the specification, assuming that a row in this table represents a unique "customer" (i.e. the same customer isn't represented multiple times in this table; that is, if this table implements the "customer" entity.) The "referred" column in the table allows for only a single value (or NULL), which means a customer can be referredby at most one other customer. This seems to imply that a NULL value means the customer was *not* referred, and a non-NULL value means the customer *was* referred. – spencer7593 Feb 13 '15 at 20:38
  • Professional software development is mostly "word problems." – shawnt00 Feb 14 '15 at 06:53
0

I finally figured it out! The proper code was:

SELECT CustomerID, FirstName, LastName,
NVL(TO_CHAR(Referred), 'Not Referred') AS "Referred By"
FROM Book_Customer
WHERE Referred IS NULL;

Thank you everyone for all your helpful comments that helped me figure out this problem.

philipxy
  • 14,867
  • 6
  • 39
  • 83
Nicolle Chase
  • 21
  • 2
  • 7
  • Two notes: 1) including white space and special characters in identifiers is allowed, but it's common practice to *avoid* doing that. (In Oracle, an identifier like that has to be enclosed in double quotes; but this also makes the identifier case-sensitive.) 2) qualifying column references with an alias assigned to the table is also a very common practice when the statement involves more than one table, when the statement is part of a PL/SQL block. The benefits of this pattern become apparent in more complex statements, but it's a pattern that we tend to follow, even in simple statements. – spencer7593 Feb 14 '15 at 14:50
  • I'm sorry I'm confused as to what you're talking about, forgive me. Where did I make whitespace? and is that just like blank spaces behind my words? Again .. very basic course ... also you said that the identifier has to be enclosed in double quotes but if you enclose it in double quotes then you get an error in SQL plus, You have to have it in single quotes(I'm talking about the 'Referred' unless you're talking about something totally different. Thank you for your tips and I'll keep them in mind going forward in my course which ends in 2 more weeks :) You've been very helpful @spencer7593 – Nicolle Chase Feb 14 '15 at 15:09
  • There's a white space character in the column name... `"Referred By"`. The space character is why the column name had to be enclosed in double quotes. In SQL, an *identifier* is the name of a column, or name of a table, view, function, procedure, etc. There are rules as to what constitutes a valid identifier, what characters are allowed, the maximum length, etc. In Oracle, an *identifier* can be enclosed in double quotes, and that makes it case sensitive, and allows us to use characters that aren't otherwise allowed, like a space, a dash, et al. – spencer7593 Feb 14 '15 at 15:16
  • In SQL, a *string literal* is a value. And string literals have to be enclosed in *single* quotes. In the example statement, `'Not Referred'` is a string literal. (It's a string value. It's not the name of a table or column.) The query in my answer is equivalent to the query in your answer, except mine did not include a space in the column alias. So the column name did not need to be enclosed in double quotes. My query assigned an alias `b` to the `Book_Customer` table, and then all references to columns from the `Book_Customer` table were *qualified* with the alias, e.g. `b.Referred`. – spencer7593 Feb 14 '15 at 15:25