summary: Broblem with joins and shorting in View (with FB2.x, works with FB1.x) => Problem with joins and shorting in View (with FB2.x, works with FB1.x)

description: Hello,

I sent the message below to the Newsgroup, but received none replies, so try this way. I can send backup (under "NDA"" of the DB with which, someone could check this with data.

Just contact me to email by my name below, with domain http://ecomond.com... (I think this was tested with 2.1 and 2.0.x not sure which actually, most likely recent one...)

-Tommi Prami-
-------------------------------------------------------

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

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

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

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

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

C.SHORTNAME AS CONTRACTORSHORTNAME, CA.CURRENTCONTRACTORID, http://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

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

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

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

C.SHORTNAME AS CONTRACTORSHORTNAME, CA.CURRENTCONTRACTORID, http://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, http://RP.NAME, http://DIST.NAME AS DISTRICTNAME, RP.DISTRICTID, RP.INUSE,

RP.ROUTEPOINTGROUPID, http://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,

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

http://CUST.NAME AS CUST_NAME, INVCUST.FIRSTNAME AS INVCUST_FIRSTNAME, INVCUST.LASTNAME AS INVCUST_LASTNAME,

http://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, http://RP.NAME, http://DIST.NAME AS DISTRICTNAME, RP.DISTRICTID, RP.INUSE,

RP.ROUTEPOINTGROUPID, http://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,

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

http://CUST.NAME AS CUST_NAME, INVCUST.FIRSTNAME AS INVCUST_FIRSTNAME, INVCUST.LASTNAME AS INVCUST_LASTNAME,

http://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.

=>

Hello,

I sent the message below to the Newsgroup, but received none replies, so try this way. I can send backup (under "NDA"" of the DB with which, someone could check this with data.

Just contact me to email by my name below, with domain http://ecomond.com... (I think this was tested with 2.1 and 2.0.x not sure which actually, most likely recent one...)

-Tommi Prami-
-------------------------------------------------------

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

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
http://D.NAME AS DISTRICTNAME, CT.CONTRACTORID, CT.CUSTOMERID, CT.MAINCUSTOMERID, CT.BUSINESSID, CT.LINEOFBUSINESSID,
CT.CUSTOMERGROUPID, CT.INUSE, CT.BACKOFFICEID, CT.FIRSTNAME, CT.LASTNAME, http://CT.NAME, CT.ADDRESS, CT.POSTNUMBER, CT.POSTOFFICE,
CT.DISTRICTID, CT.PHONE, CT.PHONE2, CT.MOBILEPHONE, CT.EMAIL, CT.COMMENT, http://CG.NAME AS CUSTOMERGROUPNAME,
C.SHORTNAME AS CONTRACTORSHORTNAME, CA.CURRENTCONTRACTORID, http://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
http://D.NAME AS DISTRICTNAME, CT.CONTRACTORID, CT.CUSTOMERID, CT.MAINCUSTOMERID, CT.BUSINESSID, CT.LINEOFBUSINESSID,
CT.CUSTOMERGROUPID, CT.INUSE, CT.BACKOFFICEID, CT.FIRSTNAME, CT.LASTNAME, http://CT.NAME, CT.ADDRESS, CT.POSTNUMBER, CT.POSTOFFICE,
CT.DISTRICTID, CT.PHONE, CT.PHONE2, CT.MOBILEPHONE, CT.EMAIL, CT.COMMENT, http://CG.NAME AS CUSTOMERGROUPNAME,
C.SHORTNAME AS CONTRACTORSHORTNAME, CA.CURRENTCONTRACTORID, http://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, http://RP.NAME, http://DIST.NAME AS DISTRICTNAME, RP.DISTRICTID, RP.INUSE,
RP.ROUTEPOINTGROUPID, http://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,
http://CRTT.NAME AS CRTT_NAME, http://C.NAME AS C_NAME, CUST.FIRSTNAME AS CUST_FIRSTNAME, CUST.LASTNAME AS CUST_LASTNAME,
http://CUST.NAME AS CUST_NAME, INVCUST.FIRSTNAME AS INVCUST_FIRSTNAME, INVCUST.LASTNAME AS INVCUST_LASTNAME,
http://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, http://RP.NAME, http://DIST.NAME AS DISTRICTNAME, RP.DISTRICTID, RP.INUSE,
RP.ROUTEPOINTGROUPID, http://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,
http://CRTT.NAME AS CRTT_NAME, http://C.NAME AS C_NAME, CUST.FIRSTNAME AS CUST_FIRSTNAME, CUST.LASTNAME AS CUST_LASTNAME,
http://CUST.NAME AS CUST_NAME, INVCUST.FIRSTNAME AS INVCUST_FIRSTNAME, INVCUST.LASTNAME AS INVCUST_LASTNAME,
http://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.

assignee: Dmitry Yemanov [ dimitr ]

Link: This issue duplicates CORE1089 [ CORE1089 ]

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Duplicate [ 3 ]

status: Resolved [ 5 ] => Closed [ 6 ]

QA Status: No test

status: Closed [ 6 ] => Closed [ 6 ]

QA Status: No test => Not enough information