Re: Need Help With Query

There are many ways to do this. One would be with the intersect operator

SELECT stuff from today
INTERSECT
SELECT stuff 30 days ago

Another would be with a correlated subquery using EXISTS.

But no matter what I'd dump the hard coded values and use bind variables ... and most importantly ... get the TEST data out of your production database. It does not belong there and will cause endless problems. Especially, as a hospital, where you are subject to audit for accreditation.

Daniel Morgan

Thomas M wrote:

> W2K
> IM 7.0.3 Build 10077
> Oracle 8.0.5
>
> I'm fairly new to SQL and I'm having a tough time with this query.
> I'm hoping that someone here can help me out. I need to create a
> report that will show us all the people who were discharged from the
> hospital, and then re-admitted within 30 days. Here's my query:
>
> SELECT DISTINCT m.member_id,
> m.member_first_name,
> m.member_last_name,
> ea.event_seq,
> ea.event_status,
> ea.authorization_number,
> ea.authorized_admission_date,
> ea.actual_discharge_date,
> ea.benefit_level,
> ea.facility_id,
> ea.facility_name,
> ea.benefit_plan,
> ea.benefit_product,
> ea.benefit_group
> FROM member m,
> event_admission ea
> WHERE m.member_id = ea.member_id
> AND m.member_id NOT LIKE '%TEST%'
> AND ea.benefit_level = '65'
> AND ea.benefit_plan = '1020'
> AND ea.benefit_product = '1053'
> AND ( ea.actual_discharge_date >= :Start_Date
> AND ea.authorized_admission_date <= :End_Date )
> AND m.member_id IN
> (SELECT DISTINCT m1.member_id,
> m1.member_first_name,
> m1.member_last_name,
> ea1.event_seq,
> ea1.event_status,
> ea1.authorization_number,
> ea1.authorized_admission_date,
> ea1.actual_discharge_date,
> ea1.benefit_level,
> ea1.facility_id,
> ea1.facility_name,
> ea1.benefit_plan,
> ea1.benefit_product,
> ea1.benefit_group
> FROM member m1,
> event_admission ea1
> WHERE m1.member_id = ea1.member_id
> AND m1.member_id NOT LIKE '%TEST%'
> AND ea1.benefit_level = '65'
> AND ea1.benefit_plan = '1020'
> AND ea1.benefit_product = '1053'
> AND ea1.actual_discharge_date > ea.actual_discharge_date
> OR ( ea1.actual_discharge_date IS NULL
> AND ea1.authorized_admission_date >= :Start_Date ) )
>
> The error I'm getting is:
>
> Select error: ORA-00913: too many values
>
> Here's what the Oracle Web site says about this error:
>
> "ORA-00913: too many values
> Cause: The SQL statement requires two sets of values equal in number.
> This error occurs when the second set contains more items than the
> first set. For example, the subquery in a WHERE or HAVING clause may
> return too many columns, or a VALUES or SELECT clause may return more
> columns than are listed in the INSERT.
>
> Action: Check the number of items in each set and change the SQL
> statement to make them equal."
>
> I guess I'm not understanding were it is that my item counts don't
> match. The only thing that I can think of is that the problem is with
> the last 3 lines of the nested Select. I've done a lot of fiddling
> with those lines and still can't get it to work. Can anyone spot the
> problem, or give me a better way to accomplish the goal?
>
> --
>
> Please reply to the newsgroup so that all may learn
> from your wisdom.
>
> --Tom
Received on Thu Jan 31 2002 - 14:59:23 CST