After many experiment at last I found a fastest way to join this table(
my version of course ), maybe someone have better solution, suggestion,
comment, I'll appreciate it very much.

FYI, if I run the same query (without view) it will crash Firebird
server (I used ibexpert personal 2.5.0.22) but after I create a view of
this query and select it using select * from VICOMPANY it works fine.
Strange, but I don't know if it's a FB bug, ibexpert bug, or maybe my
mistake?

I try this query using isql it works but I get a lot of '=' in the
middle of query result (length of this '=' variably, seems representing
length of field width). I never use isql before so I don't know if this
character is common or something. I don't have an idea since this
character not found if I use view. Someone can give me a light?

This view does not work as I hope in Firebird 1 (never completed).
Thanks to great Firebird 1.5 optimization. I really really love this
thing. Keep up a good work.

Sorry for my English, TIA,
Wasith


Here is my view (and query) :

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, COM_ACTIVE,
CAS_MIN, CAS_MAX, CRE_NAME, CRE_MIN, CRE_MAX,
CTY_NAME, CNE_NAME, CNE_MIN, CNE_MAX
CTI_NAME, CSA_NAME, CSA_MIN, CSA_MAX, CLB_ID, LOB_ID,
LB1_ID, LB2_ID, CLB_NAME, LOB_NAME,
LB1_NAME, LB2_NAME, CMA_ID, ADD_ID, CMA_NAME,
CMA_MAINADDR, COU_ID, PVI_ID, CIT_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_LOB)
AS
SELECT A.COM_ID, A.CSA_ID, A.CTI_ID, A.CNE_ID, A.CTY_ID, A.CAS_ID,
A.CRE_ID, A.COM_NAME, A.COM_WEBSITE, A.COM_ASSETVALUE, A.COM_SALESVALUE,
A.COM_REVENUEVALUE, A.COM_NOEVALUE, A.COM_FAKNAME, A.COM_FAKADDRESS1,
A.COM_FAKADDRESS2, A.COM_FAKADDRESS3, A.COM_FAKADDRESS4, A.COM_NPWP,
A.COM_ENTRYDATE, A.COM_LASTUPDATE, A.COM_OPERATOR, A.COM_ACTIVE,
B.CAS_MIN, B.CAS_MAX, H.CRE_NAME, H.CRE_MIN, H.CRE_MAX, I.CTY_NAME,
J.CNE_NAME, J.CNE_MIN, J.CNE_MAX, K.CTI_NAME, L.CSA_NAME, L.CSA_MIN,
L.CSA_MAX, M.CLB_ID, M.LOB_ID, M.LB1_ID, M.LB2_ID, M.CLB_NAME,
N.LOB_NAME, O.LB1_NAME, P.LB2_NAME, C.CMA_ID, C.ADD_ID, C.CMA_NAME,
C.CMA_MAINADDR, D.COU_ID, D.PVI_ID, D.CIT_ID, D.ADD_NAME,
D.ADD_ADDRESS1, D.ADD_ADDRESS2, D.ADD_ADDRESS3, D.ADD_ADDRESS4,
D.ADD_ZIP, D.ADD_PHONE1, D.ADD_PHONE2, D.ADD_FAX, E.COU_NAME,
E.COU_TELP, F.PVI_NAME, G.CIT_NAME, G.CIT_TELP, (SELECT ADDRESS FROM
SPGETADDRESS(D.ADD_ADDRESS1, D.ADD_ADDRESS2,D.ADD_ADDRESS3,
D.ADD_ADDRESS4)) ADD_ADDRESS, (SELECT PHONECODE FROM
SPGETPHONECODE(E.COU_TELP,G.CIT_TELP))ADD_PHONECODE,(SELECT PHONE FROM
SPGETPHONE(D.ADD_PHONE1,D.ADD_PHONE2)) ADD_PHONE, (SELECT LOB FROM
SPGETLOB(N.LOB_NAME,O.LB1_NAME, P.LB2_NAME)) CLB_LOB
FROM TBCOASSET B
RIGHT OUTER JOIN TBCOMPANY A ON (B.CAS_ID = A.CAS_ID)
LEFT OUTER JOIN TBCOADDR C ON (A.COM_ID = C.COM_ID AND
C.CMA_MAINADDR=1)
LEFT OUTER JOIN TBADDRESS D ON (C.ADD_ID = D.ADD_ID)
LEFT OUTER JOIN TBCOUNTRY E ON (D.COU_ID = E.COU_ID)
LEFT OUTER JOIN TBPROVINCE F ON (D.PVI_ID = F.PVI_ID)
LEFT OUTER JOIN TBCITY G ON (D.CIT_ID = G.CIT_ID)
LEFT OUTER JOIN TBCOREVENUE H ON (A.CRE_ID = H.CRE_ID)
LEFT OUTER JOIN TBCOTYPE I ON (A.CTY_ID = I.CTY_ID)
LEFT OUTER JOIN TBCONOE J ON (A.CNE_ID = J.CNE_ID)
LEFT OUTER JOIN TBCOTIER K ON (A.CTI_ID = K.CTI_ID)
LEFT OUTER JOIN TBCOSALES L ON (A.CSA_ID = L.CSA_ID)
LEFT OUTER JOIN TBCOLOB M ON (A.COM_ID = M.COM_ID AND
M.CLB_MAINLOB=1)
LEFT OUTER JOIN TBLOB N ON (M.LOB_ID = N.LOB_ID)
LEFT OUTER JOIN TBSUBLOB1 O ON (M.LB1_ID = O.LB1_ID)
LEFT OUTER JOIN TBSUBLOB2 P ON (M.LB2_ID = P.LB2_ID);