Re: Returning number of values from sub-query

On Jan 21, 7:38 pm, trp..._at_gmail.com wrote:
> On Jan 21, 5:20 pm, Chen Shapira <csh..._at_gmail.com> wrote:
> > On Jan 21, 3:16 pm, trp..._at_gmail.com wrote:
>
> > > I have the following query that returns 1 row:
>
> > > SELECT REPORT_DATE, STARTS FROM SUBSCRIPTIONSUMMARYDATA WHERE
> > > REPORT_DATE IN (TO_DATE('01/20/2008','MM/DD/YYYY'),
> > > TO_DATE('01/21/2008','MM/DD/YYYY'))
>
> > > REPORT_DATE    STARTS
> > > 01/20/2008               100
>
> > > What I am afer is to have 2 rows returned based on the values in the
> > > subquery, so I really want results like this:
>
> > > REPORT_DATE    STARTS
> > > 01/20/2008               100
> > > 01/21/2008
>
> > > So I want 1 row returned for each row of the subquery. Any ideas, or
> > > how can I accomplish ths better.
>
> > Your example code doesn't have a subquery, so you can't.
> > If you really had a subquery that returns the dates you want to see,
> > you could use an outer join:
>
> > SELECT subquery.REPORT_DATE, t.STARTS
> > FROM SUBSCRIPTIONSUMMARYDATA t
> > right outer join
> >   (select TO_DATE('01/20/2008','MM/DD/YYYY') report_date from dual
> >   union all
> >   select TO_DATE('01/21/2008','MM/DD/YYYY') report_date from dual
> >   ) subquery
> > on t.report_date = subquery.report_date- Hide quoted text -
>
> > - Show quoted text -
>
> My fault, here is the subquery, but it returns the same results as if
> I had just used the 2 dates:
>
> SELECT REPORT_DATE, STARTS FROM SUBSCRIPTIONSUMMARYDATA WHERE
> REPORT_DATE IN (SELECT
>         TO_DATE('01/20/2008','MM/DD/YYYY')-(ROWNUM-1)*7 END_DATE
>       FROM
>         DUAL
>       CONNECT BY
>         LEVEL<=2)

Convert the sub-query into an inline view, and then create a left outer join between the view and your table. After the changes, the query might look like this:
SELECT
  I.END_DATE REPORT_DATE,
  S.STARTS
FROM
  (SELECT
    TO_DATE('01/20/2008','MM/DD/YYYY')-(ROWNUM-1)*7 END_DATE   FROM
    DUAL
  CONNECT BY
    LEVEL<=2) I,
  SUBSCRIPTIONSUMMARYDATA S
WHERE
  I.END_DATE=S.REPORT_DATE(+); Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Tue Jan 22 2008 - 04:52:44 CST