Re: SQL problem

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 24 Jun 1998 16:47:22 GMT
Message-ID: <359829a0.13391475@192.86.155.100>

A copy of this was sent to "Phil R Lawrence" <prl2_at_lehigh.edu> (if that email address didn't require changing) On Wed, 24 Jun 1998 09:44:45 -0400, you wrote:

>Hello, I have two SQL statements which *should* return the same count. I can't
>figure out why they don't. Can anyone tell me why?
>
>Number 1:
>SELECT count(saradap_pidm)
>FROM saturn.saradap
>WHERE saradap_term_code_entry = '199840'
> and saradap_admt_code in ('IN', 'NA', 'ED', 'DA')
> and exists(select 'x' from saturn.sarappd
> where sarappd_apdc_code in ('Z', 'AZ', 'DZ')
> and saradap_appl_no = sarappd_appl_no
> and sarappd_pidm = saradap_pidm)
> and not exists(select 'x' from saturn.sarappd
> where sarappd_apdc_code in ('SF', 'SH')
> and saradap_appl_no = sarappd_appl_no
> and sarappd_pidm = saradap_pidm)
>
>Number 2:
>SELECT count(saradap_pidm)
>FROM saturn.saradap, saturn.sarappd
>WHERE sarappd_pidm = saradap_pidm
> and saradap_appl_no = sarappd_appl_no
> and saradap_term_code_entry = '199840'
> and saradap_admt_code in ('IN', 'NA', 'ED', 'DA')
> and sarappd_apdc_code in ('Z', 'AZ', 'DZ')
> and sarappd_apdc_code not in ('SF', 'SH')

Well, one is a join and one is not. That could do it.

If

select sarappd_pidm, sarappd_appl_no, count(*) from sarappd
group by sarappd_pidm, sarappd_appl_no
having count(*) > 1

returns any rows, the the effect of joining the 2 tables is 'adding' rows to it. If the relationship between saradap and sarappd is 1:M, then you are counting rows in sarappd, not saradap.

If saradap_pidm is UNIQUE in saradap, then select count( distinct saradap_pidm ) should fix it up.

And instead of coding:

> and exists(select 'x' from saturn.sarappd
> where sarappd_apdc_code in ('Z', 'AZ', 'DZ')
> and saradap_appl_no = sarappd_appl_no
> and sarappd_pidm = saradap_pidm)
> and not exists(select 'x' from saturn.sarappd
> where sarappd_apdc_code in ('SF', 'SH')
> and saradap_appl_no = sarappd_appl_no
> and sarappd_pidm = saradap_pidm)

in the first query, why not just code:

> and exists(select 'x' from saturn.sarappd
> where sarappd_apdc_code in ('Z', 'AZ', 'DZ')

             and sarappd_apdc_code not in ( 'SF', 'SH' )


>            and saradap_appl_no = sarappd_appl_no


>            and sarappd_pidm = saradap_pidm)

that would be a little faster...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Jun 24 1998 - 11:47:22 CDT