Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2014
    Posts
    5

    Unanswered: Proper formatted query returns empty set

    Hello,

    I have this query which works well when applied to an msaccess database, returning 18 records:

    SELECT W1.cod_area1, W1.name_area1, COUNT (W4.id_vist)
    FROM
    area1 W1, area2 W2, person W3, visitation W4, evaluation W5
    WHERE W1.cod_area1=W2.cod_area1
    AND W2.cod_area2=W3.cod_area2
    AND W3.id_person=W4.id_person
    AND W4.cod_eval=W5.cod_eval
    AND W5.cod_evaltype=1
    GROUP BY W1.cod_area1, W1.name_area1

    However, when I query the same database deployed in postgresql, this query always returns an empty set when I run it with pgAdminIII or even a third-party app like SQL Manager Lite for PostgreSQL.
    What can be happening here? I would appreciate some help here

    Best Regards,
    aol

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Wow, I had no clue that PostgreSQL still supported the ANSI-89 SQL syntax!

    MS-Access has supported the ANSI-92 syntax since Access 2007, and probably for a few versions before that (I don't have any earlier versions to test). Please try running the following code in both MS-Access and in PostgreSQL and see if it produces the same results in both!
    Code:
    SELECT W1.cod_area1, W1.name_area1, COUNT (W4.id_vist)
       FROM area1 W1
       JOIN area2 W2
          ON (W1.cod_area1 = W2.cod_area1)
       JOIN person W3
          ON (W2.cod_area2 = W3.cod_area2)
       JOIN visitation W4
          ON (W3.id_person = W4.id_person)
       JOIN evaluation W5
          ON (W4.cod_eval = W5.cod_eval)
       WHERE  W5.cod_evaltype = 1 
       GROUP BY W1.cod_area1, W1.name_area1
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Pat Phelan View Post
    Wow, I had no clue that PostgreSQL still supported the ANSI-89 SQL syntax!
    Every DBMS that supports SQL will (actually: must) support that.

    Just because a DBMS supports the modern explicit JOIN syntax doesn't mean it can not support the old implicit joins in the where clause - in fact I would consider that a serious bug in the product if implicit joins weren't working.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  4. #4
    Join Date
    Oct 2014
    Posts
    5

    Empty set problem (reloaded)

    Quote Originally Posted by Pat Phelan View Post
    Wow, I had no clue that PostgreSQL still supported the ANSI-89 SQL syntax!

    MS-Access has supported the ANSI-92 syntax since Access 2007, and probably for a few versions before that (I don't have any earlier versions to test). Please try running the following code in both MS-Access and in PostgreSQL and see if it produces the same results in both!
    Code:
    SELECT W1.cod_area1, W1.name_area1, COUNT (W4.id_vist)
       FROM area1 W1
       JOIN area2 W2
          ON (W1.cod_area1 = W2.cod_area1)
       JOIN person W3
          ON (W2.cod_area2 = W3.cod_area2)
       JOIN visitation W4
          ON (W3.id_person = W4.id_person)
       JOIN evaluation W5
          ON (W4.cod_eval = W5.cod_eval)
       WHERE  W5.cod_evaltype = 1 
       GROUP BY W1.cod_area1, W1.name_area1
    -PatP
    Pat,
    Many thanks for the suggestions, they made perfect sense.

    I tried the syntax you provided, didnt work on access (error in from clause) and returned an empty set on postgre as well;

    Next I tried some "reverse engineering" by launching the query in graphical mode in access, pgAdminIII and the EMS tool and got the forllowing code when reversing to SQL:

    In Access:

    SELECT area1.cod_area1, area1.name_area1, Count(visitation.id_visit) AS CountVisit

    FROM

    ((area1 INNER JOIN area2 ON area1.cod_area1 = area2.cod_area1)

    INNER JOIN person ON area2.cod_area2 = person.area2)

    INNER JOIN (evaluation INNER JOIN visitation ON evaluation.cod_eval = visitation.cod_eval)

    ON person.id_person = visitation.id_person

    GROUP BY area1.cod_area1, area1.name_area1

    ORDER BY area1.name_area1;

    In pgAdminIII and EMS:

    SELECT

    COUNT(public.visitation.id_visit) AS Countvisit,

    public.area1.cod_area1,

    public.area1.name_area1

    FROM

    public.evaluation
    INNER JOIN public.visitation ON (public.evaluation.cod_eval = public.visitation.cod_eval)
    INNER JOIN public.person ON (public.visitation.id_person = public.person.id_person)
    INNER JOIN public.area2 ON (public.person.cod_area2 = public.area2.cod_area2)
    INNER JOIN public.area1 ON (public.area2.cod_area1 = public.area1.cod_area1)
    WHERE
    public.evaluation.cod_eval = 1
    GROUP BY
    public.area1.cod_area1,
    public.area1.name_area1
    ORDER BY
    public.area1.name_area1

    However, the final results remain the same, with access returning records but postgresql returning an empty set

    What can I do next? I am at a loss here...

    Best Regards,
    aol

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Are the difference of column name between In Access and In pgAdminIII and EMS right?
    In Access:

    ON area2.cod_area2 = person.area2
    In pgAdminIII and EMS:

    public.person.cod_area2 = public.area2.cod_area2

  6. #6
    Join Date
    Oct 2014
    Posts
    5
    Hello Tonkuma,

    You're right, but it was just a clerical mistake on my part, I posted a previous block.
    The results are effectively the ones reported in the last post.

    Best Regards,
    aol

  7. #7
    Join Date
    Oct 2014
    Posts
    5

    empty set done, but other problem arises

    Hi,

    Well, guess I found the problem (at least for now). As shammat implied, the problem (at least at
    this point) was not with ansi-89 compliance.

    I noticed that in table evaluation, cod_eval is a very complex alphanumeric mix, so what I did was to create
    new integer PK and FK columns in tables evaluation and visitation, and the query finally worked well.

    However, the final query is a little bit more complex, and it is now working BUT the count values in returned records are all zero:

    SELECT area1.cod_area1, name_area1 AS area1,

    round (count(visitation0.id_visit)/area1.poparea1*10000) AS ratevisit

    FROM area1, area2, person, visitation0, evaluation0

    WHERE area1.cod_area1 = area2.cod_area1

    AND area2.cod_area2 = person.cod_area2

    AND person.id_person = visitation0.id_person

    AND visitation0.codevalfk = evaluation0.codevalpk

    AND evaluation0.codstatus=1

    AND (round (date (visitation0.date_visit)- date (person.date_birth))/365) < 20

    GROUP BY area1.cod_area1, area1.name_area1

    ORDER BY ratevisit;

    Again, this is working properly in access (slightly different in handling dates but the same principle)

    What can I change now to make this (finally) work?

    Thanks,
    aol

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Please post the DDL needed to create your tables, and enough data to create three rows that should be returned and three rows that should NOT be returned. Once we can use your data to confirm the results, I would expect that we'll make better progress at solving your problem for you.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Oct 2014
    Posts
    5

    Smile DDL and data

    Hello Pat,

    As you suggested, I am sending the DDL as .txt files and the required data in accdb tables.

    Thanks a lot for helping

    Best Regards,
    aol



    ddl_and_data.zip

Posting Permissions

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