Re: Intersect used in stored procedure

Geoff,

The full intersect query runs fine in the SQL Worksheet. Although the intersect portion only returns two elements and the main query five elements. Two of the five are string constants, 'Both' "Type" and 'Eligible' and another is USER, which is always(?) valid. The main query then groups the result of the intersect by Cohort and performs a Count(*).

I'm going back at it again today and I'm going to strip down the query element by element to see what may be causing the compilation error.

Geoff White wrote:

> An obvious problem I can see is that you have an unequal number of elements
> in your select lists.
> That is, you have:
> select user,cohort, 'Both' "Type", 'Eligible', count(*) from
> that's 5 elements compared to
> select substr(a.id,7,2) cohort, substr(a.id,1,5) subid
> which is 2 elements. Have you run the full INTERSECT query from SQL*plus?
>
> HTH
> Geoff
>
> Peter Wendell wrote:
>
> > I am really stuck and confused. I've written a pl/sql procedure which
> > executes a series of insert statements. All of the queries run fine from
> > the worksheet, but when I try to compile I receive a MSG-00072 error
> > that the package body has compilation errors. If I paste the contents of
> > the package body into the the SQL worksheet, all the queries run without
> > error. Two of the insert queries use INTERSECT to combine two result
> > sets. If they are removed, the package body compiles without complaint.
> > <snip>
Received on Fri Apr 09 1999 - 08:20:01 CDT