Thread

1. Problem with view

   wenjin.zheng <wenjin.zheng@lsbc.com> — 2000-04-03T17:50:29Z

   Hi,
   
   I have got another problem.  I have a table(table tempOne) that has two
   fields, seqid(int) and phredscore(int).  They are one to many relationship,
   e.g. one seqid has many phredscore.  I would like to get the row that has
   the max(phredscore).  So I first created a view as follow:
   "create view maxphred as select seqid, max(phredscore) as phredscore from
   tempOne group by seqid;"
   
   Then I try to get the rows that corresponding to the top phredscore.  I did
   this:
   
   "select tempOne.* from tempOne, maxphred where tempOne.seqid=maxphred.seqid
   and tempOne.phredscore=maxphred.phredscore;"
   
   I got some weird stuff back which obviously is wrong.  However if I create a
   table maxphred rather than a view, I get the correct result.  There might be
   something missing for the view that I did not know of.  Does anyone know
   that my query with view did not work.
   
   Your help will be greatly appreciated.
   
   Wenjin Zheng
   Bioinformatic Analyst
   Biosource Technologies, Inc.
   3333 Vaca Valley Parkway
   Vacaville, CA 95688
   (707)469-2353
   email: wenjin.zheng@lsbc.com 
   
   
   

2. Re: Problem with view

   Jan Wieck <wieck@debis.com> — 2000-04-03T21:10:36Z

   > I have got another problem.  I have a table(table tempOne) that has two
   > fields, seqid(int) and phredscore(int).  They are one to many relationship,
   > e.g. one seqid has many phredscore.  I would like to get the row that has
   > the max(phredscore).  So I first created a view as follow:
   > "create view maxphred as select seqid, max(phredscore) as phredscore from
   > tempOne group by seqid;"
   
       Views  are  known to have severe problems with aggregates and
       grouping.  And once again  these  are  problems  we  want  to
       tackle with the parse-/querytree overhaul.
   
       Oh man - this is one of the most important things I see.
   
   
   Jan
   
   --
   
   #======================================================================#
   # It's easier to get forgiveness for being wrong than for being right. #
   # Let's break this rule - forgive me.                                  #
   #========================================= wieck@debis.com (Jan Wieck) #
   
   
   
   

3. Re: Problem with view

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-04-03T21:42:44Z

   wieck@debis.com (Jan Wieck) writes:
   >     Views  are  known to have severe problems with aggregates and
   >     grouping.  And once again  these  are  problems  we  want  to
   >     tackle with the parse-/querytree overhaul.
   
   >     Oh man - this is one of the most important things I see.
   
   Yup.  Outer joins are waiting on that work, too.
   
   			regards, tom lane
   
   

4. Re: Problem with view

   Don Baccus <dhogaza@pacifier.com> — 2000-04-04T00:43:51Z

   At 05:42 PM 4/3/00 -0400, Tom Lane wrote:
   >wieck@debis.com (Jan Wieck) writes:
   >>     Views  are  known to have severe problems with aggregates and
   >>     grouping.  And once again  these  are  problems  we  want  to
   >>     tackle with the parse-/querytree overhaul.
   >
   >>     Oh man - this is one of the most important things I see.
   >
   >Yup.  Outer joins are waiting on that work, too.
   
   Are you saying outer joins won't happen until the overhaul?  Does
   this mean they're not happening in 7.1 or that 7.1 will just be
   in the far distance?
   
   
   
   - Don Baccus, Portland OR <dhogaza@pacifier.com>
     Nature photos, on-line guides, Pacific Northwest
     Rare Bird Alert Service and other goodies at
     http://donb.photo.net.
   
   

5. Re: Problem with view

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-04-04T01:27:19Z

   Don Baccus <dhogaza@pacifier.com> writes:
   >> Yup.  Outer joins are waiting on that work, too.
   
   > Are you saying outer joins won't happen until the overhaul?
   
   Right.
   
   > Does
   > this mean they're not happening in 7.1 or that 7.1 will just be
   > in the far distance?
   
   7.1 isn't happening next week, no.  I'm guessing maybe late summer.
   
   I don't think we'll release 7.1 until the overhaul is done.
   
   			regards, tom lane