I've created a View called VICOMPANY. With this view I want to display
all information related to company in one row (speed not considered too
much). Maybe some one have better suggestion. FYI I'm using FB 1.5 Beta
2 Win, Win2000 Server.

But when I select VICOMPANY based on COM_ID, it will raise invalid
request handle : The cursor identified in a FETCH or CLOSE statement is
not open. It only happen if I select using COM_ID.

Select * from VICOMPANY where COM_ID=1

How to fix this view (What was wrong?) so I can select it using above
statement.

TIA, sorry for my English


Here is my SQL for this view.

------------------------------------------------------------------------
---
CREATE VIEW VICOMPANY (
COM_ID,
CSA_ID,
CTI_ID,
CNE_ID,
CTY_ID,
CAS_ID,
CRE_ID,
COM_NAME,
COM_WEBSITE,
COM_ASSETVALUE,
COM_SALESVALUE,
COM_REVENUEVALUE,
COM_NOEVALUE,
COM_FAKNAME,
COM_FAKADDRESS1,
COM_FAKADDRESS2,
COM_FAKADDRESS3,
COM_FAKADDRESS4,
COM_NPWP,
COM_ENTRYDATE,
COM_LASTUPDATE,
COM_OPERATOR,
CSA_NAME,
CRE_NAME,
CTI_NAME,
CNE_NAME,
CTY_NAME,
CAS_NAME,
CMA_ID,
ADD_ID,
CMA_NAME,
CMA_MAINADDR,
CIT_ID,
PVI_ID,
COU_ID,
ADD_NAME,
ADD_ADDRESS1,
ADD_ADDRESS2,
ADD_ADDRESS3,
ADD_ADDRESS4,
ADD_ZIP,
ADD_PHONE1,
ADD_PHONE2,
ADD_FAX,
COU_NAME,
COU_TELP,
PVI_NAME,
CIT_NAME,
CIT_TELP,
ADD_ADDRESS,
ADD_PHONECODE,
ADD_PHONE,
CLB_NAME,
LB2_ID,
LB1_ID,
CLB_ID,
LOB_ID,
CLB_MAINLOB,
LB1_NAME,
LOB_NAME,
LB2_NAME,
CLB_LOB)
AS
SELECT TBCOMPANY.COM_ID, TBCOMPANY.CSA_ID, TBCOMPANY.CTI_ID,
TBCOMPANY.CNE_ID, TBCOMPANY.CTY_ID, TBCOMPANY.CAS_ID, TBCOMPANY.CRE_ID,
TBCOMPANY.COM_NAME, TBCOMPANY.COM_WEBSITE, TBCOMPANY.COM_ASSETVALUE,
TBCOMPANY.COM_SALESVALUE, TBCOMPANY.COM_REVENUEVALUE,
TBCOMPANY.COM_NOEVALUE, TBCOMPANY.COM_FAKNAME,
TBCOMPANY.COM_FAKADDRESS1, TBCOMPANY.COM_FAKADDRESS2,
TBCOMPANY.COM_FAKADDRESS3, TBCOMPANY.COM_FAKADDRESS4,
TBCOMPANY.COM_NPWP, TBCOMPANY.COM_ENTRYDATE, TBCOMPANY.COM_LASTUPDATE,
TBCOMPANY.COM_OPERATOR, TBCOSALES.CSA_NAME, TBCOREVENUE.CRE_NAME,
TBCOTIER.CTI_NAME, TBCONOE.CNE_NAME, TBCOTYPE.CTY_NAME,
TBCOASSET.CAS_NAME, VICOMAINADDR.CMA_ID, VICOMAINADDR.ADD_ID,
VICOMAINADDR.CMA_NAME, VICOMAINADDR.CMA_MAINADDR, VICOMAINADDR.CIT_ID,
VICOMAINADDR.PVI_ID, VICOMAINADDR.COU_ID, VICOMAINADDR.ADD_NAME,
VICOMAINADDR.ADD_ADDRESS1, VICOMAINADDR.ADD_ADDRESS2,
VICOMAINADDR.ADD_ADDRESS3, VICOMAINADDR.ADD_ADDRESS4,
VICOMAINADDR.ADD_ZIP, VICOMAINADDR.ADD_PHONE1, VICOMAINADDR.ADD_PHONE2,
VICOMAINADDR.ADD_FAX, VICOMAINADDR.COU_NAME, VICOMAINADDR.COU_TELP,
VICOMAINADDR.PVI_NAME, VICOMAINADDR.CIT_NAME, VICOMAINADDR.CIT_TELP,
VICOMAINADDR.ADD_ADDRESS, VICOMAINADDR.ADD_PHONECODE,
VICOMAINADDR.ADD_PHONE, VICOMAINLOB.CLB_NAME, VICOMAINLOB.LB2_ID,
VICOMAINLOB.LB1_ID, VICOMAINLOB.CLB_ID, VICOMAINLOB.LOB_ID,
VICOMAINLOB.CLB_MAINLOB, VICOMAINLOB.LB1_NAME, VICOMAINLOB.LOB_NAME,
VICOMAINLOB.LB2_NAME, VICOMAINLOB.CLB_LOB
FROM VICOMAINLOB
RIGHT OUTER JOIN TBCOMPANY ON (VICOMAINLOB.COM_ID = TBCOMPANY.COM_ID)
LEFT OUTER JOIN VICOMAINADDR ON (TBCOMPANY.COM_ID =
VICOMAINADDR.COM_ID)
LEFT OUTER JOIN TBCOASSET ON (TBCOMPANY.CAS_ID = TBCOASSET.CAS_ID)
LEFT OUTER JOIN TBCOREVENUE ON (TBCOMPANY.CRE_ID =
TBCOREVENUE.CRE_ID)
LEFT OUTER JOIN TBCOTIER ON (TBCOMPANY.CTI_ID = TBCOTIER.CTI_ID)
LEFT OUTER JOIN TBCONOE ON (TBCOMPANY.CNE_ID = TBCONOE.CNE_ID)
LEFT OUTER JOIN TBCOTYPE ON (TBCOMPANY.CTY_ID = TBCOTYPE.CTY_ID)
LEFT OUTER JOIN TBCOSALES ON (TBCOMPANY.CSA_ID = TBCOSALES.CSA_ID)

------------------------------------------------------------------------
---
CREATE VIEW VICOMAINADDR (
CMA_ID,
COM_ID,
ADD_ID,
CMA_NAME,
CMA_MAINADDR,
CIT_ID,
PVI_ID,
COU_ID,
ADD_NAME,
ADD_ADDRESS1,
ADD_ADDRESS2,
ADD_ADDRESS3,
ADD_ADDRESS4,
ADD_ZIP,
ADD_PHONE1,
ADD_PHONE2,
ADD_FAX,
COU_NAME,
COU_TELP,
PVI_NAME,
CIT_NAME,
CIT_TELP,
ADD_ADDRESS,
ADD_PHONECODE,
ADD_PHONE)
AS
SELECT CMA_ID,
COM_ID,
ADD_ID,
CMA_NAME,
CMA_MAINADDR,
CIT_ID,
PVI_ID,
COU_ID,
ADD_NAME,
ADD_ADDRESS1,
ADD_ADDRESS2,
ADD_ADDRESS3,
ADD_ADDRESS4,
ADD_ZIP,
ADD_PHONE1,
ADD_PHONE2,
ADD_FAX,
COU_NAME,
COU_TELP,
PVI_NAME,
CIT_NAME,
CIT_TELP,
ADD_ADDRESS,
ADD_PHONECODE,
ADD_PHONE FROM VICOADDR WHERE CMA_MAINADDR=1

------------------------------------------------------------------------
---
CREATE VIEW VICOMAINLOB (
CLB_ID,
LOB_ID,
LB1_ID,
LB2_ID,
COM_ID,
CLB_NAME,
CLB_MAINLOB,
LB1_NAME,
LOB_NAME,
LB2_NAME,
CLB_LOB)
AS
SELECT * FROM VICOLOB WHERE VICOLOB.CLB_MAINLOB = 1

------------------------------------------------------------------------
---
CREATE VIEW VICOADDR (
CMA_ID,
COM_ID,
ADD_ID,
CMA_NAME,
CMA_MAINADDR,
CIT_ID,
PVI_ID,
COU_ID,
ADD_NAME,
ADD_ADDRESS1,
ADD_ADDRESS2,
ADD_ADDRESS3,
ADD_ADDRESS4,
ADD_ZIP,
ADD_PHONE1,
ADD_PHONE2,
ADD_FAX,
COU_NAME,
COU_TELP,
PVI_NAME,
CIT_NAME,
CIT_TELP,
ADD_ADDRESS,
ADD_PHONECODE,
ADD_PHONE)
AS
SELECT TBCOADDR.CMA_ID, TBCOADDR.COM_ID, TBCOADDR.ADD_ID,
TBCOADDR.CMA_NAME, TBCOADDR.CMA_MAINADDR, VIADDRESS.CIT_ID,
VIADDRESS.PVI_ID, VIADDRESS.COU_ID, VIADDRESS.ADD_NAME,
VIADDRESS.ADD_ADDRESS1, VIADDRESS.ADD_ADDRESS2, VIADDRESS.ADD_ADDRESS3,
VIADDRESS.ADD_ADDRESS4, VIADDRESS.ADD_ZIP, VIADDRESS.ADD_PHONE1,
VIADDRESS.ADD_PHONE2, VIADDRESS.ADD_FAX, VIADDRESS.COU_NAME,
VIADDRESS.COU_TELP, VIADDRESS.PVI_NAME, VIADDRESS.CIT_NAME,
VIADDRESS.CIT_TELP, VIADDRESS.ADD_ADDRESS, VIADDRESS.ADD_PHONECODE,
VIADDRESS.ADD_PHONE
FROM VIADDRESS
RIGHT OUTER JOIN TBCOADDR ON (VIADDRESS.ADD_ID = TBCOADDR.ADD_ID)

------------------------------------------------------------------------
---

CREATE VIEW VICOLOB (
CLB_ID,
LOB_ID,
LB1_ID,
LB2_ID,
COM_ID,
CLB_NAME,
CLB_MAINLOB,
LB1_NAME,
LOB_NAME,
LB2_NAME,
CLB_LOB)
AS
SELECT TBCOLOB.CLB_ID, TBCOLOB.LOB_ID, TBCOLOB.LB1_ID, TBCOLOB.LB2_ID,
TBCOLOB.COM_ID, TBCOLOB.CLB_NAME, TBCOLOB.CLB_MAINLOB,
TBSUBLOB1.LB1_NAME, TBLOB.LOB_NAME, TBSUBLOB2.LB2_NAME, (SELECT LOB FROM
SPGETLOB(TBLOB.LOB_NAME,TBSUBLOB1.LB1_NAME, TBSUBLOB2.LB2_NAME)) CLB_LOB
FROM TBSUBLOB2
RIGHT OUTER JOIN TBCOLOB ON (TBSUBLOB2.LB2_ID = TBCOLOB.LB2_ID)
LEFT OUTER JOIN TBLOB ON (TBCOLOB.LOB_ID = TBLOB.LOB_ID)
LEFT OUTER JOIN TBSUBLOB1 ON (TBCOLOB.LB1_ID = TBSUBLOB1.LB1_ID)