Hi Tom:

I am not seeing any comments from other readers (names but no comments) so maybe this was covered but even after I fix the column names, the code seems a bit frisky;

scott@SARAH> l
1 select *
2 from ( select deptno, count(*) cnt
3 from emp) emp,
4 dept
5* where dept.deptno = emp.deptno
scott@SARAH> /
from ( select deptno, count(*) cnt
*
ERROR at line 2:
ORA-00937: not a single-group group function

As you usually fix the code when it has an error, I have the feeling that nobody else complained.

Cheers,

Harrison

I am getting the error:

PROCEDURE ECAT_EBO.CAT_WHYNOPARTS
PL/SQL: ORA-00904: "BA"."SUPPLIERID": invalid identifier

BEGIN
SELECT 1
INTO stoo_selcnt
FROM DUAL
WHERE NOT EXISTS (
SELECT 1
FROM cat_buyer_account ba,
cat_supplieraccount sa,
cat_supplierprice sp,
cat_pricecatalog pc,
cat_price price
WHERE ba.supplieraccountid = sa.supplieraccountid
AND sp.supplieraccountid = sa.supplieraccountid
AND sp.pricecatalogid = pc.pricecatalogid
AND pc.pricecatalogid = price.pricecatalogid
AND price.supplierid = ba.supplierid
AND pc.supplierid = ba.supplierid
AND ba.catspaceid = 1
AND sa.supplieraccountid != 0
AND pc.pricecatalogid != 0
AND 0 <
(SELECT include
FROM (SELECT MIN (cf1.rnum) AS minrnum
FROM cat_commfilterimpl cf1
WHERE cf1.catspaceid =
1
AND cf1.rnum >= price.lnum
AND cf1.supplierid IN
(0, ba.supplierid)) cmin2,
cat_commfilterimpl cfb
WHERE cfb.catspaceid = 1
AND cfb.supplierid = 0
AND cfb.lnum <= price.lnum
AND cfb.rnum >= price.lnum
AND cfb.rnum = cmin2.minrnum)
);
END;

The strange thing is, that this code compiles fine on a 8.1.7 server & not on this server (9.2)

Why?

Very hard to supply you with test data.

I am just wondering if in the following it is legal to reference the 'ba' identifier inside the correlated sub-query on line 27:
1 SELECT 1
2 FROM
3 cat_buyer_account ba,
4 cat_supplieraccount sa,
5 cat_supplierprice sp,
6 cat_pricecatalog pc,
7 cat_price price
8 WHERE ba.supplieraccountid = sa.supplieraccountid
9 AND sp.supplieraccountid = sa.supplieraccountid
10 AND sp.pricecatalogid = pc.pricecatalogid
11 AND pc.pricecatalogid = price.pricecatalogid
12 AND price.supplierid = ba.supplierid
13 AND pc.supplierid = ba.supplierid
14 AND ba.catspaceid = 0
15 AND sa.supplieraccountid != 0
16 AND pc.pricecatalogid != 0
17 AND 0 <
18 (
19 SELECT include
20 FROM (
21 SELECT MIN (cf1.rnum) AS minrnum
22 FROM
23 cat_commfilterimpl cf1
24 WHERE cf1.catspaceid = 25cat_whynoparts.catspaceid
26 AND cf1.rnum >= price.lnum
27 AND cf1.supplierid IN ( 0, ba.supplierid ))28cmin2,
29 cat_commfilterimpl cfb
30 WHERE cfb.catspaceid = 0
31 AND cfb.supplierid = 0
32 AND cfb.lnum <= price.lnum
33 AND cfb.rnum >= price.lnum
34 AND cfb.rnum = cmin2.minrnum
35 );
/

This is like my query except shortened:
It give the same error also.

drop table a1;
drop table b1;
drop table c1;

create table a1 (supplier_id number, name varchar2(25));


create table b1 (supplier_id number, id number);



create table c1 (id number);

insert into a1 values (1,'TEST');

insert into a1 values (2,'TEST');

insert into a1 values (3,'TEST');
insert into b1 values (1,1);
insert into c1 values (1);
commit;

SELECT 1
from A1 A, b1 B
where
A.supplier_id = B.supplier_id
and 0<
(select 1 from
(select min(B.id) as minid
from
b1 B
where b.supplier_id = A.supplier_id
) min2,
c1 C
where C.id = min2.minid
)
;

correlation names only go "one level down", this would not work, does not work in 8i either.


"A" does not exist in that subquery - it can exist in the subquery one layer up.

from 8i:

ops$xp8i\tkyte@ORA8IR3W> SELECT 1
  2      from A1 A, b1 B
  3  where
  4  A.supplier_id = B.supplier_id
  5  and 0<
  6      (select 1 from
  7          (select min(B.id) as minid
  8              from
  9                  b1 B
 10              where b.supplier_id = A.supplier_id
 11          ) min2,
 12          c1 C
 13      where C.id = min2.minid
 14      )
 15  /
            where b.supplier_id = A.supplier_id
                                    *
ERROR at line 10:
ORA-00904: invalid column name


It never should have worked. 

If it was embedded in a stored procedure would it compile on either?

I take the identical code & compile it on 8i fine but it fails on 9i. But true as you say, when I strip the query above from the procedure it fails on both.

Does this make sense?

No, I just ran the above example in 8i, the cut and paste is from 8i.


ops$xp8i\tkyte@ORA8IR3W> begin
  2  for x in (
  3  SELECT 1 XXX
  4      from A1 A, b1 B
  5  where
  6  A.supplier_id = B.supplier_id
  7  and 0<
  8      (select 1 from
  9          (select min(B.id) as minid
 10              from
 11                  b1 B
 12              where b.supplier_id = A.supplier_id
 13          ) min2,
 14          c1 C
 15      where C.id = min2.minid
 16      )
 17  )
 18  loop
 19          null;
 20  end loop;
 21  end;
 22  /
begin
*
ERROR at line 1:
ORA-00904: invalid column name
ORA-06512: at line 2

8i in plsql, 8i in "just sql"

do you have an example that runs in 8i but does not in 9i? 

The Procedure is lengthy & proprietary..I could send it to you via e-mail along with the errors when connecting to both versions. Just send me a note to my e-mail
& I can send it along.

Thanks for everything again!

So how would one go about fixing the view so it can reference the table in the upper layer?