SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member mattstrife's Avatar
    Join Date
    Aug 2003
    Location
    Moosetreal, Quebec, Canada, eh!
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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:
    Code SQL:
    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
    Code SQL:
    RIGHT JOIN APP.identifiants AS identifiants1
    may be choking the whole thing.

    Thus I have modified the query like so:
    Code SQL:
    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)
    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.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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...
    Code:
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    p.s. if dossiers.id1 and dossiers.id2 are both NOT NULL, then both joins should be INNER
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member mattstrife's Avatar
    Join Date
    Aug 2003
    Location
    Moosetreal, Quebec, Canada, eh!
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code SQL:
    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
    Code SQL:
    RIGHT JOIN APP.identifiants AS identifiants1
    to
    Code SQL:
    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!

  5. #5
    SitePoint Member mattstrife's Avatar
    Join Date
    Aug 2003
    Location
    Moosetreal, Quebec, Canada, eh!
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking

    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •