Derby -- SQL state 42Y07: Schema 'db_username_here' does not exist

[mattstrife]

Greetings all!

I've been running into the Schema "db_username" does not exist error when query a Derby embedded db.

I've done some research and and it happens only in views, if I understand correctly
(Reference: http://www.jroller.com/gmazza/entry/...ql_state_42y07)

I'm very unfamiliar with Derby, and still rather green with SQL too, most likely, and am not even trying to create as view AFAICT.

My query is this:

SELECT APP.dossiers.id, APP.identifiants1.nom, APP.identifiants1.prenom, APP.identifiants1.no_civique || ', ' || APP.identifiants1.rue AS adresseid1,
APP.identifiants1.appartement, APP.identifiants.nom, APP.identifiants.prenom, APP.identifiants.no_civique || ', ' || APP.identifiants.rue AS adresseid2, APP.identifiants.appartement
FROM APP.dossiers
RIGHT JOIN APP.identifiants AS identifiants1
ON identifiants1.id = dossiers.id1
LEFT JOIN APP.identifiants
ON identifiants.id = dossiers.id2

Originally, the selected columns didn't have APP. prefixing them, yet after adding it I still got the error (that's when I've done the research).
In light of the result of my research, I've concluded that

RIGHT JOIN APP.identifiants AS identifiants1

may be choking the whole thing.

Thus I have modified the query like so:

SELECT APP.dossiers.id, APP.identifiants1.nom, APP.identifiants1.prenom, APP.identifiants1.no_civique || ', ' || APP.identifiants1.rue AS adresseid1,
APP.identifiants1.appartement, APP.identifiants.nom, APP.identifiants.prenom, APP.identifiants.no_civique || ', ' || APP.identifiants.rue AS adresseid2, APP.identifiants.appartement
FROM APP.dossiers
RIGHT JOIN APP.identifiants AS APP.identifiants1
ON identifiants1.id = dossiers.id1
LEFT JOIN APP.identifiants
ON identifiants.id = dossiers.id2

and get the following error (line 3 being 4 in the pasted code)

Error code 30000, SQL state 42X01: Syntax error: Encountered "." at line 3, column 35.
Line 1, column 1

Execution finished after 0 s, 1 error(s) occurred.

I'm sort of convinced it's just a stupid problem. Maybe some bracketing required like in "Ms Access" databases?

Perhaps I'm not even on the right path?

Any help I can get on this is appreciated.

I will keep on on this in the mean time, of course.

[r937]

i've never used derby but in SQL when you assign a table alias in the FROM clause, it has to be a single identifier

so APP.identifiants AS APP.identifiants1 is wrong, it has to be APP.identifiants AS identifiants1

may i ask why you're mixing RIGHT and LEFT joins? that's incredibly confusing, even for me

anyhow, here's how i would write your query, without touching the RIGHT/LEFT issue, just addressing the alias problems...

SELECT d.id
     , i1.nom
     , i1.prenom
     , i1.no_civique || ', ' || i1.rue AS adresseid1
     , i1.appartement
     , i2.nom
     , i2.prenom
     , i2.no_civique || ', ' || i2.rue AS adresseid2
     , i2.appartement
  FROM APP.dossiers AS d
RIGHT 
  JOIN APP.identifiants AS i1
    ON i1.id = d.id1
LEFT 
  JOIN APP.identifiants AS i2
    ON i2.id = d.id2

notice i prefer much shorter alias names, it makes the query more readable

[r937]

p.s. if dossiers.id1 and dossiers.id2 are both NOT NULL, then both joins should be INNER

[mattstrife]

Thanks for you reply r937.

dossiers.id1 is NOT NULL, but dossiers.id2 is often NULL
Thanks for pointing that out on the way.

And yes, I may just follow your example in the future and use short aliases.

(I pre-apologize if this post is perhaps too long...!)

I believe my question wasn't clear enough... and I'm sorry for that.

The first version of the query is as follows:

SELECT dossiers.id,
           identifiants1.nom,
           identifiants1.prenom,
           identifiants1.no_civique || ', ' || identifiants1.rue AS adresseid1,
           identifiants1.appartement,
           identifiants.nom, identifiants.prenom,
           identifiants.no_civique || ', ' || identifiants.rue AS adresseid2,
           identifiants.appartement
 
FROM App.dossiers
 
RIGHT JOIN APP.identifiants AS identifiants1
 
ON identifiants1.id = dossiers.id1
 
LEFT JOIN APP.identifiants
 
ON identifiants.id = dossiers.id2

Now, when ran in sql server express, with schema name (APP.) removed, it works as expected. But when I run it in Derby, it says "SQL state 42Y07: Schema 'db_username_here' does not exist".

So at first, I thought I was supposed to go and rabidly slap the schema everywhere the name of the table was used. When it made no difference, I asked Google about "SQL state 42Y07".
In the results I found a post on a blog that says

In Apache Derby, you may get a "Schema '<database username>' does not exist" error message when trying to SELECT from a database view.

However, I'm not creating a view... so I'm rather confused.

Thus I decided to modify

RIGHT JOIN APP.identifiants AS identifiants1

to

RIGHT JOIN APP.identifiants AS APP.identifiants1

but one is not supposed to slap the schema to an alias... so I'm still stuck with the "SQL state 42Y07" error.

I will however try to recreate the tables without specifying the schema... then redo the query without specifying a schema, maybe that will solve my problem!

[mattstrife]

I did as I mentionned in my previous post: re-created the database without specifying the schema and the problem is solved.

I thank you yet again r937.