Re: Returning number of values from sub-query

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)