CODEWITHSUNDEEP
javasqlderby
Susheel Singh
Susheel Singh

Reputation: 3854

issue with derby database on where condition

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 protected]

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.

Upvotes: 1

Views: 2703

Answers (4)

Shorin
Shorin

Reputation: 549

I couldnt get the create statements for the table, and the describe table said nothing of the sort indicating my problem, but I had to surround all table and column names with double quotes, and values with single quotes.

SELECT "name" FROM "THING_TABLE" WHERE "name"='Environments';

Funny thing is that when i used the ID column I didnt need quotes. Dunno why.

Could be the way the table was created. The above says single quotes mean table names case sensitive?

Upvotes: 1

Martijn Burger
Martijn Burger

Reputation: 7543

In my case this error occurred because I was using double quotes in my WHERE statement instead of single quotes

Upvotes: 2

Luke Woodward
Luke Woodward

Reputation: 64959

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', '[email protected]');
1 row inserted/updated/deleted
ij> select * from REGISTRATION where USERNAME='susheel61';
ID         |FIRSTNAME                                         |LASTNAME                                          |USERNAME               |PASSWORD                                          |EMAIL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1          |susheel                                           |singh                                             |susheel61              |password                                          |[email protected]

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.

Upvotes: 3

Susheel Singh
Susheel Singh

Reputation: 3854

This was then script I used to create the registration table and it was created successfully but got issues while querying the table with where condition.

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)
);

Now I changed it to this syntax for creating the table and everything works fine. Not sure about the reason.

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) unique
);

Upvotes: 0

Related Questions