Re: sql script which doesn't find everything

Hi,

Ok, let us see what is on the database for the period in question... try...

set null *

select

           subscription_duration,
           subscription_start_date,
           nvl(po_expiry_date, SYSDate + 1000)  po_expiry_date, 
           Max(number)  mxnum,
           count(*)     Cnt  

         po_expiry_date          > to_date( '1-jan-98', 'DD-MON-YY')
 and     subscription_start_date < to_date( '1-jan-98', 'DD-MON-YY')
group by
           subscription_duration,
           subscription_start_date,
           nvl(po_expiry_date, SYSDate + 1000)
order by 
           subscription_duration,
           subscription_start_date,
           nvl(po_expiry_date, SYSDate + 1000)

This will show you all the information you require to explain your results assuming not null values in the date fields.

graham

On Fri, 09 Apr 1999 14:43:25 GMT, sweinberg_at_callmax.nl (sandy weinberg) wrote:

>I'm a beginner with SQL queries and this is driving me crazy. I've
>created the following query:
>
>select number, subscription_duration, po_expiry_date,
>subscription_start_date
>from handles
>where subscription_duration not like 'F'
>and po_expiry_date > '1-jan-98'
>and subscription_start_date < '1-jan-98'
>order by po_expiry_date, maxer_number;
>
>The problem is if I do a query WITHOUT subscription_duration not like
>'F', I get a total of 19990 records.
>
>If I do a query with subscription_duration like 'F', I get a total of
>412.
>
>So, I should have 19478 records? Or not???
>
>I hope somebody can help me to sharpen this query!
>
>Thanks in advance for your help!
>
>Sincerely yours,
>
>Sandy.
Received on Sun Apr 11 1999 - 17:30:56 CEST