When I attempt to do a select from the view shown below I get an hour glass and no rows are ever returned. A select from this view has worked for years on this database. I have unload/reloaded the database with no luck. I have also dropped and re-created the view.
TIA
Joe Kack
DROP VIEW SYSADM.POLINES;
CREATE VIEW SYSADM.POLINES (
PO_ID, PO_STATUS,
ORDER_DATE,
WHLOC,
FROM_VENDOR,
MEMO,
REQUIRED_DATE,
SHIP_VIA,
TERMS,
FOB_POINT,
CHARGE_CODE,
TAX_PER,
FREIGHT,
PART,
QUO,
PRICE,
CHGCODE,
TAXABLE,
PART_CLASS,
PART_SUBCLASS,
PART_UNIT,
PART_DESC,
V_NAME,
V_CONTACT,
V_ADDRESS_1,
V_ADDRESS_2,
V_ADDRESS_3,
V_CITY,
V_STATE,
V_ZIP,
V_PHONE,
V_FAX,
VCAT,
SN,
WONUM,
REQBY,
AUTHBY,
ORDBY,
QU,
POD,
ACTPRICE,
RCVSN)
AS
select
a.PO_ID,a.PO_STATUS,a.ORDER_DATE,B.WHLOC,
a.FROM_VENDOR,a.MEMO,a.REQUIRED_DATE,a.SHIP_VIA,a.TERMS,
a.FOB_POINT,a.CHARGE_CODE,a.TAX_PER,a.FREIGHT,
b.PART,b.QUO,b.PRICE,b.CHGCODE,b.TAXABLE,
c.PART_CLASS,c.PART_SUBCLASS,c.PART_UNIT,b.IDESC,
d.V_NAME,d.V_CONTACT,d.V_ADDRESS_1,d.V_ADDRESS_2,d.V_ADDRESS_3,
d.V_CITY,d.V_STATE,d.V_ZIP,d.V_PHONE,d.V_FAX,
e.vendor_cat,b.sn,a.wonum,a.reqby,a.authby,a.ordby,f.qu,f.pod,f.actprice,f.sn
from pobase a,politm b,part_basic c,vendors d,part_vendor e,rcvlitm f
where a.po_id=b.po and b.part=c.part_id and a.from_vendor=d.vendor_id
and b.part=e.part_id and a.from_vendor=e.vendor and b.sn=f.pod(+)
union select
a.PO_ID,a.PO_STATUS,a.ORDER_DATE,B.WHLOC,
a.FROM_VENDOR,a.MEMO,a.REQUIRED_DATE,a.SHIP_VIA,a.TERMS,
a.FOB_POINT,a.CHARGE_CODE,a.TAX_PER,a.FREIGHT,
b.PART,b.QUO,b.PRICE,b.CHGCODE,b.TAXABLE,
c.PART_CLASS,c.PART_SUBCLASS,c.PART_UNIT,b.IDESC,
d.V_NAME,d.V_CONTACT,d.V_ADDRESS_1,d.V_ADDRESS_2,d.V_ADDRESS_3,
d.V_CITY,d.V_STATE,d.V_ZIP,d.V_PHONE,d.V_FAX,
null,b.sn,a.wonum,a.reqby,a.authby,a.ordby,f.qu,f.pod,f.actprice,f.sn
from pobase a,politm b,part_basic c,vendors d,rcvlitm f
where a.po_id=b.po and b.part=c.part_id and a.from_vendor=d.vendor_id
and b.sn=f.pod(+)
and not exists (select E.PART_ID,E.VENDOR,E.VENDOR_CAT,E.UNIT_COST,E.RANK from part_vendor e where
b.part=e.part_id and a.from_vendor=e.vendor)
union select
a.PO_ID,a.PO_STATUS,a.ORDER_DATE,B.WHLOC,
a.FROM_VENDOR,a.MEMO,a.REQUIRED_DATE,a.SHIP_VIA,a.TERMS,
a.FOB_POINT,a.CHARGE_CODE,a.TAX_PER,a.FREIGHT,
b.PART,b.QUO,b.PRICE,b.CHGCODE,b.TAXABLE,
null,null,null,b.IDESC,
d.V_NAME,d.V_CONTACT,d.V_ADDRESS_1,d.V_ADDRESS_2,d.V_ADDRESS_3,
d.V_CITY,d.V_STATE,d.V_ZIP,d.V_PHONE,d.V_FAX,
null,b.sn,a.wonum,a.reqby,a.authby,a.ordby,f.qu,f.pod,f.actprice,f.sn
from pobase a,politm b,vendors d,rcvlitm f
where a.po_id=b.po and b.sn=f.pod(+)
and b.part is null and a.from_vendor=d.vendor_id;
GRANT SELECT ON SYSADM.POLINES TO GUEST;
CREATE PUBLIC SYNONYM POLINES FOR SYSADM.POLINES;
TIA
Joe Kack
DROP VIEW SYSADM.POLINES;
CREATE VIEW SYSADM.POLINES (
PO_ID, PO_STATUS,
ORDER_DATE,
WHLOC,
FROM_VENDOR,
MEMO,
REQUIRED_DATE,
SHIP_VIA,
TERMS,
FOB_POINT,
CHARGE_CODE,
TAX_PER,
FREIGHT,
PART,
QUO,
PRICE,
CHGCODE,
TAXABLE,
PART_CLASS,
PART_SUBCLASS,
PART_UNIT,
PART_DESC,
V_NAME,
V_CONTACT,
V_ADDRESS_1,
V_ADDRESS_2,
V_ADDRESS_3,
V_CITY,
V_STATE,
V_ZIP,
V_PHONE,
V_FAX,
VCAT,
SN,
WONUM,
REQBY,
AUTHBY,
ORDBY,
QU,
POD,
ACTPRICE,
RCVSN)
AS
select
a.PO_ID,a.PO_STATUS,a.ORDER_DATE,B.WHLOC,
a.FROM_VENDOR,a.MEMO,a.REQUIRED_DATE,a.SHIP_VIA,a.TERMS,
a.FOB_POINT,a.CHARGE_CODE,a.TAX_PER,a.FREIGHT,
b.PART,b.QUO,b.PRICE,b.CHGCODE,b.TAXABLE,
c.PART_CLASS,c.PART_SUBCLASS,c.PART_UNIT,b.IDESC,
d.V_NAME,d.V_CONTACT,d.V_ADDRESS_1,d.V_ADDRESS_2,d.V_ADDRESS_3,
d.V_CITY,d.V_STATE,d.V_ZIP,d.V_PHONE,d.V_FAX,
e.vendor_cat,b.sn,a.wonum,a.reqby,a.authby,a.ordby,f.qu,f.pod,f.actprice,f.sn
from pobase a,politm b,part_basic c,vendors d,part_vendor e,rcvlitm f
where a.po_id=b.po and b.part=c.part_id and a.from_vendor=d.vendor_id
and b.part=e.part_id and a.from_vendor=e.vendor and b.sn=f.pod(+)
union select
a.PO_ID,a.PO_STATUS,a.ORDER_DATE,B.WHLOC,
a.FROM_VENDOR,a.MEMO,a.REQUIRED_DATE,a.SHIP_VIA,a.TERMS,
a.FOB_POINT,a.CHARGE_CODE,a.TAX_PER,a.FREIGHT,
b.PART,b.QUO,b.PRICE,b.CHGCODE,b.TAXABLE,
c.PART_CLASS,c.PART_SUBCLASS,c.PART_UNIT,b.IDESC,
d.V_NAME,d.V_CONTACT,d.V_ADDRESS_1,d.V_ADDRESS_2,d.V_ADDRESS_3,
d.V_CITY,d.V_STATE,d.V_ZIP,d.V_PHONE,d.V_FAX,
null,b.sn,a.wonum,a.reqby,a.authby,a.ordby,f.qu,f.pod,f.actprice,f.sn
from pobase a,politm b,part_basic c,vendors d,rcvlitm f
where a.po_id=b.po and b.part=c.part_id and a.from_vendor=d.vendor_id
and b.sn=f.pod(+)
and not exists (select E.PART_ID,E.VENDOR,E.VENDOR_CAT,E.UNIT_COST,E.RANK from part_vendor e where
b.part=e.part_id and a.from_vendor=e.vendor)
union select
a.PO_ID,a.PO_STATUS,a.ORDER_DATE,B.WHLOC,
a.FROM_VENDOR,a.MEMO,a.REQUIRED_DATE,a.SHIP_VIA,a.TERMS,
a.FOB_POINT,a.CHARGE_CODE,a.TAX_PER,a.FREIGHT,
b.PART,b.QUO,b.PRICE,b.CHGCODE,b.TAXABLE,
null,null,null,b.IDESC,
d.V_NAME,d.V_CONTACT,d.V_ADDRESS_1,d.V_ADDRESS_2,d.V_ADDRESS_3,
d.V_CITY,d.V_STATE,d.V_ZIP,d.V_PHONE,d.V_FAX,
null,b.sn,a.wonum,a.reqby,a.authby,a.ordby,f.qu,f.pod,f.actprice,f.sn
from pobase a,politm b,vendors d,rcvlitm f
where a.po_id=b.po and b.sn=f.pod(+)
and b.part is null and a.from_vendor=d.vendor_id;
GRANT SELECT ON SYSADM.POLINES TO GUEST;
CREATE PUBLIC SYNONYM POLINES FOR SYSADM.POLINES;