Re: SQL help
- From: "GovindanK" <gkatteri@xxxxxxxxxxx>
- To: dubey.sandeep@xxxxxxxxx
- Date: Tue, 11 Oct 2005 09:47:37 -0700
If the values are contiguous then you can adopt this approach. Otherwise
the exists option as suggested by another member on this list would be
the way to go.
SQL9206>select * from t;
ID
----------
1
2
3
3 rows selected.
SQL9206>select a.kount , b.range_diff
2 from
3 (select count(*) kount from t) a
4 ,(select ( &end_value - &begin_value ) + 1 range_diff
5 from t) b
6 where a.kount = b.range_diff;
Enter value for end_value: 3
Enter value for begin_value: 1
old 4: ,(select ( &end_value - &begin_value ) + 1 range_diff
new 4: ,(select ( 3 - 1 ) + 1 range_diff
KOUNT RANGE_DIFF
---------- ----------
3 3
3 3
3 3
3 rows selected.
SQL9206>delete from t where id = 3;
1 row deleted.
SQL9206>select a.kount , b.range_diff
2 from
3 (select count(*) kount from t) a
4 ,(select ( &end_value - &begin_value ) + 1 range_diff
5 from t) b
6 where a.kount = b.range_diff;
Enter value for end_value: 3
Enter value for begin_value: 1
old 4: ,(select ( &end_value - &begin_value ) + 1 range_diff
new 4: ,(select ( 3 - 1 ) + 1 range_diff
no rows selected
SQL9206>
HTH
GovindanK
>
> On 10/10/05, Sandeep Dubey <dubey.sandeep@xxxxxxxxx> wrote:
> >
> > Hi,
> >
> > I am having weekend hangover with seemingly simple sql requirement.
> >
> > create table t(id number);
> > insert into t values(1);
> > insert into t values(2);
> > commit;
> >
> > I want to query this with an Id set. All values in the set should be
> > there to return me any row.
> > e.g.
> > select * from t where id in (1,2); return 1 and 2
> >
> > If am serching for 1,2,3 if any one value is missing I should not get any
> > data.
> > e.g.
> > select * from t where id in (1,2,3) should not return any row.
> > How to rewrite the above query with (1,2,3) that should not return me any
> > row.
> > Thanks
> >
> > Sandeep
> > --
> > //www.freelists.org/webpage/oracle-l
> >
>
>
>
> --
> ------------------------------
> select standard_disclaimer from company_requirements where category =
> 'MANDATORY';
--
//www.freelists.org/webpage/oracle-l
Other related posts:
