Re: RE:SQLPLUS

In article <2u4ki7$233_at_podsun17.cc.binghamton.edu>, bpanko_at_podsun17.cc.binghamton.edu (bpanko) writes:
|>
|> Hi,everyone
|>
|> I am new to this newsgroup and new to ORACLE .
|> I have this simple query in sqlplus
|>
|> SQL>insert into subscrib(department_number)
|> 2> select department_number from deptmstr
|> 3> where subscrib.last_name=deptmstr.department_name;
|> *
|> ERROR:ORA__00904: invalid column name
|>
|> The explanation provided in ORACLE ERROR MESSAGES does not help my cause.
|> NOTE:both last_name and department_name are defined char(20).
|>
|> Thanks.
|>
|>

Just remember you have 2 separate SQL statements here even though they are being combined into one logical statement. The problem appears to be in the SELECT portion of the statement:

	SELECT deptmstr.department_number
	FROM   deptmstr, subscrib
	WHERE  subscrib.last_name = deptmstr.department_name
	;

You can't reference a table.column in the WHERE clause unless the table has been included in the FROM clause.

This SELECT will return 0, 1 or more department_number's from the deptmstr table to be inserted into the department_number column in the subscrib table without any other information to distinguish those new department numbers.

Also, are you sure you want to be comparing last_name = department_name? The above SELECT should be syntactically correct, but what I read is somewhat confusing.

Hope this helps.

                                Teresa Larson

+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+
| Teresa A. Larson - Hughes STX Corporation                            |
| NASA/GSFC Code 933.0                        voice:  (301) 286-7867   |
| Greenbelt, Maryland  20771                  fax:    (301) 286-1777   |
| Teresa.Larson_at_gsfc.nasa.gov                                          |
 +~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+