Re: Complex Query

From: joel garry <joel-garry_at_home.com>
Date: Tue, 12 May 2009 13:53:56 -0700 (PDT)
Message-ID: <f9b90955-a92e-4260-8844-6528d369a26a_at_v23g2000pro.googlegroups.com>



On May 12, 9:17 am, xhos..._at_gmail.com wrote:
> a..._at_unsu.com wrote:
> > On May 8, 6:28=A0pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> > > On May 8, 6:30=A0pm, Shakespeare <what..._at_xs4all.nl> wrote:
> > > > a..._at_unsu.com schreef:
>
> > > > > I am trying to make a query with virtual tables to apply 2
> > > > > different sets of criteria and get 2 columns from 1 table. =A0It
> > > > > almost works. But if you look at the query, the issue is that the
> > > > > column ORDER_DATE appears in both virtual tables, so I get an
> > > > > error.
>
> > > > > But it is the ORDER_DATE which is the key here, as I want one date
> > > > > per
> > > > > line with the different figures:
>
> In your first query, you were doing an unrestricted Cartesian join on both
> tables.
>
> ....
>
>
>
> > Well, the issue is with that 'order date' column.  Seems if I use it
> > in my order by, depending on which one I use, it changes the output of
> > the query.
>
> Since you are now using "where rnum=1", it should hardly be a surprise that
> changing the order of an inner part changes the output.
>
> > So, I know I can prefix the column.  That has nothing to
> > do with my question.  See what I've done and what my output is:
>
> You don't have a question, you just have a statement of perplexity.
> Whether prefixing the columns has anything to do with your "question",
> it certainly might have something to do with the solution.  (Although I
> suppose you could circumvent this by adding a plethora of extra selects
> which serve no purpose other than renaming columns)
>
> > If I change the query to this, and use PARTITION BY order_date_1 in
> > line #3, I'll get the following output, which is wrong for the
> > 'annual' column:
>
> > SELECT order_date_1, trials, order_date_2, annuals, rnum
> > FROM (SELECT order_date_1, trials, order_date_2, annuals, ROW_NUMBER()
> >       OVER (PARTITION BY order_date_1 ORDER BY order_date_1) rnum
>
> ...
>
> > WHERE rnum = 1;
>
> So here you break the Cartesian join by taking an arbitrary row from
> the second table.  Obviously what you need to do is do an equijoin
> on the two tables.
>
>   where prefix1.order_date = prefix2.order_date
>
> or
>
>   where order_date_1=order_date_2
>
> Of course, rows where either count would be 0 will be missing.
> So you might need to do a outer join, or something else.

Oddly enough, some languages have an implicit domain created when something is named the same between two tables. Of course, this language isn't one of them, but it makes me wonder where the OP is coming from (virtual table? That's used in a number of places, sometimes different concepts with same name), it might be more a matter of semantics and translation than ignorance of concepts.

jg

--
_at_home.com is bogus.
It's enough to make Spock scream.
Received on Tue May 12 2009 - 15:53:56 CDT

Original text of this message