Hello,

There was discussion on similar problem in which I send this messgage below
already. I received any reply(s9 so I'll resend this in case of it was too
cryptic to send new message into old thread of conversation.

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

********************************************************************

I made a following query using view called view_customer_8:



SELECT ADDRESS,BACKOFFICEID,BUSINESSID,COMMENT,CONTRACTORID,

CONTRACTORSHORTNAME,CURRENTCONTRACTORID,CUSTOMERGROUPID,

CUSTOMERGROUPNAME,CUSTOMERID,C_NAME,DISTRICTID,DISTRICTNAME,

EMAIL,FIRSTNAME,INUSE,LASTNAME,LINEOFBUSINESSID,MAINCUSTOMERID,

MOBILEPHONE,NAME,PHONE,PHONE2,POSTNUMBER,POSTOFFICE FROM

VIEW_CUSTOMER_8 WHERE (INUSE = 1) AND (UPPER(NAME COLLATE FI_FI)

LIKE 'I%') ORDER BY NAME ASC

********************************************************************











/* View: VIEW_CUSTOMER_8, Owner: SYSDBA */



CREATE VIEW "VIEW_CUSTOMER_8" (

"DISTRICTNAME",

"CONTRACTORID",

"CUSTOMERID",

"MAINCUSTOMERID",

"BUSINESSID",

"LINEOFBUSINESSID",

"CUSTOMERGROUPID",

"INUSE",

"BACKOFFICEID",

"FIRSTNAME",

"LASTNAME",

"NAME",

"ADDRESS",

"POSTNUMBER",

"POSTOFFICE",

"DISTRICTID",

"PHONE",

"PHONE2",

"MOBILEPHONE",

"EMAIL",

"COMMENT",

"CUSTOMERGROUPNAME",

"CONTRACTORSHORTNAME",

"CURRENTCONTRACTORID",

"C_NAME"

) AS



SELECT DISTINCT

D.NAME AS DISTRICTNAME, CT.CONTRACTORID, CT.CUSTOMERID, CT.MAINCUSTOMERID,
CT.BUSINESSID, CT.LINEOFBUSINESSID,

CT.CUSTOMERGROUPID, CT.INUSE, CT.BACKOFFICEID, CT.FIRSTNAME, CT.LASTNAME,
CT.NAME, CT.ADDRESS, CT.POSTNUMBER, CT.POSTOFFICE,

CT.DISTRICTID, CT.PHONE, CT.PHONE2, CT.MOBILEPHONE, CT.EMAIL, CT.COMMENT,
CG.NAME AS CUSTOMERGROUPNAME,

C.SHORTNAME AS CONTRACTORSHORTNAME, CA.CURRENTCONTRACTORID, C.NAME AS C_NAME

FROM CUSTOMER CT

LEFT OUTER JOIN CONTRACT CRT ON CT.CONTRACTORID = CRT.CONTRACTORID AND
(CT.CUSTOMERID = CRT.INVOICECUSTOMERID OR CT.CUSTOMERID = CRT.CUSTOMERID)
AND CRT.INUSE = CT.INUSE

LEFT OUTER JOIN CONTRACTORROUTE CR ON CRT.CONTRACTORID = CR.CONTRACTORID AND
CRT.CONTRACTID = CR.CONTRACTID AND CRT.INUSE = CR.INUSE

LEFT OUTER JOIN CONTRACTAREA CA ON CA.CONTRACTORID = CR.CONTRACTORID AND
CA.CONTRACTAREAID = CR.CONTRACTAREAID

LEFT OUTER JOIN CUSTOMERGROUP CG ON CT.CUSTOMERGROUPID = CG.CUSTOMERGROUPID

LEFT OUTER JOIN CONTRACTOR C ON CT.CONTRACTORID = C.CONTRACTORID

LEFT OUTER JOIN DISTRICT D ON CT.DISTRICTID = D.DISTRICTID

;



Plan is



PLAN SORT (SORT (JOIN (JOIN (JOIN (JOIN (SORT (JOIN (VIEW_CUSTOMER_8 CT
INDEX (IX_CUSTOMER), VIEW_CUSTOMER_8 CRT INDEX (FK_CUSTOMERINV_CONTRACT,
FK_CUSTOMER_CONTRACT)), VIEW_CUSTOMER_8 CR INDEX
(FK_CONTRACT_CNRTORROUTE)), VIEW_CUSTOMER_8 CA INDEX (PK_CONTRACTAREA)),
VIEW_CUSTOMER_8 CG INDEX (PK_CUSTOMERGROUPID)), VIEW_CUSTOMER_8 C INDEX
(PK_CONTRACTOR)), VIEW_CUSTOMER_8 D INDEX (PK_DISTRICT))))



And resulting data set is not ordered by name





but when I made following change to the view_customer_8







/* View: VIEW_CUSTOMER_8, Owner: SYSDBA */



CREATE VIEW "VIEW_CUSTOMER_8" (

"DISTRICTNAME",

"CONTRACTORID",

"CUSTOMERID",

"MAINCUSTOMERID",

"BUSINESSID",

"LINEOFBUSINESSID",

"CUSTOMERGROUPID",

"INUSE",

"BACKOFFICEID",

"FIRSTNAME",

"LASTNAME",

"NAME",

"ADDRESS",

"POSTNUMBER",

"POSTOFFICE",

"DISTRICTID",

"PHONE",

"PHONE2",

"MOBILEPHONE",

"EMAIL",

"COMMENT",

"CUSTOMERGROUPNAME",

"CONTRACTORSHORTNAME",

"CURRENTCONTRACTORID",

"C_NAME"

) AS



SELECT DISTINCT

D.NAME AS DISTRICTNAME, CT.CONTRACTORID, CT.CUSTOMERID, CT.MAINCUSTOMERID,
CT.BUSINESSID, CT.LINEOFBUSINESSID,

CT.CUSTOMERGROUPID, CT.INUSE, CT.BACKOFFICEID, CT.FIRSTNAME, CT.LASTNAME,
CT.NAME, CT.ADDRESS, CT.POSTNUMBER, CT.POSTOFFICE,

CT.DISTRICTID, CT.PHONE, CT.PHONE2, CT.MOBILEPHONE, CT.EMAIL, CT.COMMENT,
CG.NAME AS CUSTOMERGROUPNAME,

C.SHORTNAME AS CONTRACTORSHORTNAME, CA.CURRENTCONTRACTORID, C.NAME AS C_NAME

FROM CUSTOMER CT

LEFT OUTER JOIN CONTRACT CRT ON CT.CONTRACTORID = CRT.CONTRACTORID AND
(CT.CUSTOMERID = CRT.INVOICECUSTOMERID OR CT.CUSTOMERID = CRT.CUSTOMERID)
AND CRT.INUSE = CT.INUSE

LEFT OUTER JOIN CONTRACTORROUTE CR ON CRT.CONTRACTORID = CR.CONTRACTORID AND
CRT.CONTRACTID = CR.CONTRACTID AND CRT.INUSE = CR.INUSE

LEFT OUTER JOIN CONTRACTAREA CA ON CA.CONTRACTORID = CR.CONTRACTORID AND
CA.CONTRACTAREAID = CR.CONTRACTAREAID

LEFT OUTER JOIN CUSTOMERGROUP CG ON CT.CUSTOMERGROUPID = CG.CUSTOMERGROUPID

INNER JOIN CONTRACTOR C ON CT.CONTRACTORID = C.CONTRACTORID /* old line was
LEFT OUTER JOIN CONTRACTOR C ON CT.CONTRACTORID = C.CONTRACTORID */

LEFT OUTER JOIN DISTRICT D ON CT.COUNTRYID= D.COUNTRYID AND CT.DISTRICTID =
D.DISTRICTID /* old line was LEFT OUTER JOIN DISTRICT D ON CT.DISTRICTID =
D.DISTRICTID */

;





plan is



PLAN SORT (SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (VIEW_CUSTOMER_8 CT
INDEX (IX_CUSTOMER), VIEW_CUSTOMER_8 CRT INDEX (FK_CUSTOMERINV_CONTRACT,
FK_CUSTOMER_CONTRACT)), VIEW_CUSTOMER_8 CR INDEX (FK_CONTRACT_CNRTORROUTE)),
VIEW_CUSTOMER_8 CA INDEX (PK_CONTRACTAREA)), VIEW_CUSTOMER_8 CG INDEX
(PK_CUSTOMERGROUPID)), VIEW_CUSTOMER_8 C INDEX (PK_CONTRACTOR)),
VIEW_CUSTOMER_8 D INDEX (PK_DISTRICT))))





And resulting data set is ordered by name.









Another problem that I could not resolve:



I use following query:



SELECT ADDRESS,BACKOFFICEID,COMMENT,CONTRACTID,CONTRACTORID,

CONTRACTORSHORTNAME,COORDINATEDATE,COORDINATEQUALITY,

COORDINATE_X,COORDINATE_Y,COORDINATE_Z,CRTT_NAME,

CURRENTCONTRACTORID,CUST_FIRSTNAME,CUST_LASTNAME,CUST_NAME,

C_NAME,DISTANCE,DISTANCEUNITID,DISTRICTID,DISTRICTNAME,INUSE,

INVCUST_FIRSTNAME,INVCUST_LASTNAME,INVCUST_NAME,LOADINGPOINTID,

MAPDATA,NAME,PROPERTYID,ROUTEID,ROUTEPOINTGROUPID,

ROUTEPOINTGROUPNAME,ROUTEPOINTID,VEHICLEGROUPID FROM

VIEW_ROUTEPOINT_11_NEW WHERE (INUSE = 1) AND (UPPER(ADDRESS COLLATE

FI_FI) LIKE 'R%') ORDER BY ADDRESS ASC













With following view:



/* View: VIEW_ROUTEPOINT_11_NEW, Owner: SYSDBA */



CREATE VIEW "VIEW_ROUTEPOINT_11_NEW" (

"ROUTEPOINTID",

"CONTRACTORID",

"ADDRESS",

"NAME",

"DISTRICTNAME",

"DISTRICTID",

"INUSE",

"ROUTEPOINTGROUPID",

"ROUTEPOINTGROUPNAME",

"BACKOFFICEID",

"CONTRACTORSHORTNAME",

"COORDINATEQUALITY",

"CURRENTCONTRACTORID",

"COORDINATE_X",

"COORDINATE_Y",

"MAPDATA",

"COMMENT",

"CONTRACTID",

"LOADINGPOINTID",

"VEHICLEGROUPID",

"DISTANCE",

"DISTANCEUNITID",

"COORDINATE_Z",

"COORDINATEDATE",

"PROPERTYID",

"CRTT_NAME",

"C_NAME",

"CUST_FIRSTNAME",

"CUST_LASTNAME",

"CUST_NAME",

"INVCUST_FIRSTNAME",

"INVCUST_LASTNAME",

"INVCUST_NAME",

"ROUTEID"

) AS

SELECT DISTINCT

RP.ROUTEPOINTID, RP.CONTRACTORID, RP.ADDRESS, RP.NAME, DIST.NAME AS
DISTRICTNAME, RP.DISTRICTID, RP.INUSE,

RP.ROUTEPOINTGROUPID, RPG.NAME AS ROUTEPOINTGROUPNAME, RP.BACKOFFICEID,
C.SHORTNAME AS CONTRACTORSHORTNAME,

RP.COORDINATEQUALITY, CA.CURRENTCONTRACTORID, RP.COORDINATE_X,
RP.COORDINATE_Y, RP.MAPDATA, RP.COMMENT, RP.CONTRACTID,

RP.LOADINGPOINTID, RP.VEHICLEGROUPID, RP.DISTANCE, RP.DISTANCEUNITID,
RP.COORDINATE_Z, RP.COORDINATEDATE, RP.PROPERTYID,

CRTT.NAME AS CRTT_NAME, C.NAME AS C_NAME, CUST.FIRSTNAME AS CUST_FIRSTNAME,
CUST.LASTNAME AS CUST_LASTNAME,

CUST.NAME AS CUST_NAME, INVCUST.FIRSTNAME AS INVCUST_FIRSTNAME,
INVCUST.LASTNAME AS INVCUST_LASTNAME,

INVCUST.NAME AS INVCUST_NAME, CR.ROUTEID

FROM ROUTEPOINT RP

INNER JOIN CONTRACTOR C ON

RP.CONTRACTORID = C.CONTRACTORID

LEFT OUTER JOIN CONTRACTORROUTE CR ON

RP.CONTRACTORID = CR.CONTRACTORID AND

RP.ROUTEPOINTID = CR.ROUTEPOINTID AND

RP.INUSE = CR.INUSE

INNER JOIN CONTRACTAREA CA ON

CR.CONTRACTORID = CA.CONTRACTORID AND

CR.CONTRACTAREAID = CA.CONTRACTAREAID

LEFT OUTER JOIN ROUTEPOINTGROUP RPG ON

RP.ROUTEPOINTGROUPID = RPG.ROUTEPOINTGROUPID

LEFT OUTER JOIN DISTRICT DIST ON

RP.COUNTRYID = DIST.COUNTRYID AND

RP.DISTRICTID = DIST.DISTRICTID

LEFT OUTER JOIN CONTRACT CRT ON

RP.CONTRACTORID = CRT.CONTRACTORID AND

RP.CONTRACTID = CRT.CONTRACTID

LEFT OUTER JOIN CONTRACTTYPE CRTT ON

CRT.CONTRACTTYPEID = CRTT.CONTRACTTYPEID

LEFT OUTER JOIN CUSTOMER CUST ON

CRT.CONTRACTORID = CUST.CONTRACTORID AND

CRT.CUSTOMERID = CUST.CUSTOMERID

LEFT OUTER JOIN CUSTOMER INVCUST ON

CRT.CONTRACTORID = INVCUST.CONTRACTORID AND

CRT.INVOICECUSTOMERID = INVCUST.CUSTOMERID

;



I get correct sorting, not correct amount of rows because it makes also LEFT
OUTER JOIN CONTRACTAREA CA into INNER JOIN,

meaning it drops every routepoint that doesn't have contractorroute, and I
don't want that.







On the other hand, with following view:



/* View: VIEW_ROUTEPOINT_11_NEW, Owner: SYSDBA */



CREATE VIEW "VIEW_ROUTEPOINT_11_NEW" (

"ROUTEPOINTID",

"CONTRACTORID",

"ADDRESS",

"NAME",

"DISTRICTNAME",

"DISTRICTID",

"INUSE",

"ROUTEPOINTGROUPID",

"ROUTEPOINTGROUPNAME",

"BACKOFFICEID",

"CONTRACTORSHORTNAME",

"COORDINATEQUALITY",

"CURRENTCONTRACTORID",

"COORDINATE_X",

"COORDINATE_Y",

"MAPDATA",

"COMMENT",

"CONTRACTID",

"LOADINGPOINTID",

"VEHICLEGROUPID",

"DISTANCE",

"DISTANCEUNITID",

"COORDINATE_Z",

"COORDINATEDATE",

"PROPERTYID",

"CRTT_NAME",

"C_NAME",

"CUST_FIRSTNAME",

"CUST_LASTNAME",

"CUST_NAME",

"INVCUST_FIRSTNAME",

"INVCUST_LASTNAME",

"INVCUST_NAME",

"ROUTEID"

) AS

SELECT DISTINCT

RP.ROUTEPOINTID, RP.CONTRACTORID, RP.ADDRESS, RP.NAME, DIST.NAME AS
DISTRICTNAME, RP.DISTRICTID, RP.INUSE,

RP.ROUTEPOINTGROUPID, RPG.NAME AS ROUTEPOINTGROUPNAME, RP.BACKOFFICEID,
C.SHORTNAME AS CONTRACTORSHORTNAME,

RP.COORDINATEQUALITY, CA.CURRENTCONTRACTORID, RP.COORDINATE_X,
RP.COORDINATE_Y, RP.MAPDATA, RP.COMMENT, RP.CONTRACTID,

RP.LOADINGPOINTID, RP.VEHICLEGROUPID, RP.DISTANCE, RP.DISTANCEUNITID,
RP.COORDINATE_Z, RP.COORDINATEDATE, RP.PROPERTYID,

CRTT.NAME AS CRTT_NAME, C.NAME AS C_NAME, CUST.FIRSTNAME AS CUST_FIRSTNAME,
CUST.LASTNAME AS CUST_LASTNAME,

CUST.NAME AS CUST_NAME, INVCUST.FIRSTNAME AS INVCUST_FIRSTNAME,
INVCUST.LASTNAME AS INVCUST_LASTNAME,

INVCUST.NAME AS INVCUST_NAME, CR.ROUTEID

FROM ROUTEPOINT RP

INNER JOIN CONTRACTOR C ON

RP.CONTRACTORID = C.CONTRACTORID

LEFT OUTER JOIN CONTRACTORROUTE CR ON

RP.CONTRACTORID = CR.CONTRACTORID AND

RP.ROUTEPOINTID = CR.ROUTEPOINTID AND

RP.INUSE = CR.INUSE

LEFT OUTER JOIN CONTRACTAREA CA ON

CR.CONTRACTORID = CA.CONTRACTORID AND

CR.CONTRACTAREAID = CA.CONTRACTAREAID

LEFT OUTER JOIN ROUTEPOINTGROUP RPG ON

RP.ROUTEPOINTGROUPID = RPG.ROUTEPOINTGROUPID

LEFT OUTER JOIN DISTRICT DIST ON

RP.COUNTRYID = DIST.COUNTRYID AND

RP.DISTRICTID = DIST.DISTRICTID

LEFT OUTER JOIN CONTRACT CRT ON

RP.CONTRACTORID = CRT.CONTRACTORID AND

RP.CONTRACTID = CRT.CONTRACTID

LEFT OUTER JOIN CONTRACTTYPE CRTT ON

CRT.CONTRACTTYPEID = CRTT.CONTRACTTYPEID

LEFT OUTER JOIN CUSTOMER CUST ON

CRT.CONTRACTORID = CUST.CONTRACTORID AND

CRT.CUSTOMERID = CUST.CUSTOMERID

LEFT OUTER JOIN CUSTOMER INVCUST ON

CRT.CONTRACTORID = INVCUST.CONTRACTORID AND

CRT.INVOICECUSTOMERID = INVCUST.CUSTOMERID

;



I get correct amount of rows, but sorting doesn't work.