Re: Complex Query
Date: 12 May 2009 16:17:50 GMT
> 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
Of course, rows where either count would be 0 will be missing. So you might need to do a outer join, or something else.
-- -------------------- http://NewsReader.Com/ -------------------- The costs of publication of this article were defrayed in part by the payment of page charges. This article must therefore be hereby marked advertisement in accordance with 18 U.S.C. Section 1734 solely to indicate this fact.Received on Tue May 12 2009 - 11:17:50 CDT