Below is my table structure.
create table "APP".REGISTRATION
(
"id" INT not null primary key GENERATED ALWAYS AS IDENTITY(START WITH 1,INCREMENT BY 1),
"firstname" VARCHAR(50),
"lastname" VARCHAR(50),
"username" VARCHAR(50),
"password" VARCHAR(50),
"email" VARCHAR(50)
);
I inserted one row with:
firstname=susheel lastname=singh username=susheel61 password=password email=test@gmail.com
Now when I try to query as
select * from REGISTRATION where USERNAME='susheel61';
i get an error saying:
> Error code 0, SQL state 42X04: Column 'USERNAME' is either not in any > table in the FROM list or appears within a join specification and is > outside the scope of the join specification or appears in a HAVING > clause and is not in the GROUP BY list. If this is a CREATE or ALTER > TABLE statement then 'USERNAME' is not a column in the target table.
Advertisement
Answer
Sorry, I can’t reproduce this one. Here’s a quick session in ij:
ij> create table "APP".REGISTRATION
> (
> id INTEGER generated by default as identity (start with 1, increment by 1) not null primary key,
> firstname VARCHAR(50),
> lastname VARCHAR(50),
> username VARCHAR(50),
> password VARCHAR(50),
> email VARCHAR(50)
> );
0 rows inserted/updated/deleted
ij> insert into "APP".registration (firstname, lastname, username, password, email) values ('susheel', 'singh', 'susheel61', 'password', 'test@gmail.com');
1 row inserted/updated/deleted
ij> select * from REGISTRATION where USERNAME='susheel61';
ID |FIRSTNAME |LASTNAME |USERNAME |PASSWORD |EMAIL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 |susheel |singh |susheel61 |password |test@gmail.com
1 row selected
ij>
Note that I’ve had to change your CREATE TABLE statement in order to create the table. The CREATE TABLE statement in your question is not valid; I get a ‘Syntax error’ when I attempt to run it.
EDIT: now that you’ve provided the actual SQL script used to create the table, I can explain the difference.
The difference is that you have specified double-quotes around the column names. Doing that makes the column names case-sensitive. You then have to use double-quotes when querying the table, unless the column names are all upper-case. If you don’t specify double-quotes around a name, the name is treated as if it was all upper-case.
Here’s how to query your table as originally specified:
ij> create table "APP".REGISTRATION > ( > "id" INT not null primary key GENERATED ALWAYS AS IDENTITY(START WITH 1,INCREMENT BY 1), > "firstname" VARCHAR(50), > "lastname" VARCHAR(50), > "username" VARCHAR(50), > "password" VARCHAR(50), > "email" VARCHAR(50) > ); 0 rows inserted/updated/deleted ij> select * from "APP".registration where username = 'susheel61'; ERROR 42X04: Column 'USERNAME' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'USERNAME' is not a column in the target table. ij> select * from "APP".registration where "username" = 'susheel61'; id |firstname |lastname |username |password |email -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0 rows selected ij>
(I haven’t bothered to insert any data this time, but hopefully you should still get the point: the query completes without error.)
Note that the column headers this time are in lower-case, whereas the column headers in the first section of output above were in upper-case.