Incorrect SQL generated

rbuser

RB 10.4
Delphi 2006

Take a look at the SQL generated by RB below. You will see that the table
PMS_LU_MANAGERS is joined twice to different tables. The second join
correctly gives an alias to the table of PMS_LU_MANAGERS_2. Now look at the
fields listing, specifically the..

PMS_LU_MANAGERS.MANAGER_NAME,
PMS_LU_MANAGERS.MANAGER_NAME MANAGER_NAME_2

... bit; the field alias is correct but the SAME table name is used so in
effect the report is selecting the data from the first table join in both
entries and the report is thus outputing the wrong information.

Any thoughts?

Paul

SELECT TMS_PROJ_HOURS.AiHours,
TMS_PROJ_HOURS.audit,
TMS_PROJ_HOURS."Date" Date_2,
TMS_PROJ_HOURS.HoursWorked,
TMS_PROJ_HOURS.Notes,
TMS_PROJ_HOURS.ProjID,
TMS_PROJ_HOURS.SortOrder,
TMS_PROJ_HOURS.UserID,
TMS_PROJ_HOURS.WeeklyID,
TMS_PROJ_HOURS.WeekNo,
PMS_PROJECT.Audit Audit_2,
PMS_PROJECT.Deleted,
PMS_PROJECT.Monthly_Notes,
PMS_PROJECT.PROJ_ADDR1,
PMS_PROJECT.PROJ_ADDR2,
PMS_PROJECT.PROJ_AGEEND,
PMS_PROJECT.PROJ_AGESTART,
PMS_PROJECT.PROJ_COUNTY,
PMS_PROJECT.PROJ_DISABILITY,
PMS_PROJECT.PROJ_DTORE2,
PMS_PROJECT.PROJ_DURATION,
PMS_PROJECT.PROJ_ETH_ALL,
PMS_PROJECT.PROJ_ETH_ASBRIN,
PMS_PROJECT.PROJ_ETH_ASOTH,
PMS_PROJECT.PROJ_ETH_BLAFRIC,
PMS_PROJECT.PROJ_ETH_BLCARA,
PMS_PROJECT.PROJ_ETH_BLOTH,
PMS_PROJECT.PROJ_ETH_CHINESE,
PMS_PROJECT.PROJ_ETH_GYPSIES,
PMS_PROJECT.PROJ_ETH_OTHER,
PMS_PROJECT.PROJ_ETH_SPECIFY,
PMS_PROJECT.PROJ_ETH_WHITEIR,
PMS_PROJECT.PROJ_ETH_WHITEOTH,
PMS_PROJECT.PROJ_FAI_ALL,
PMS_PROJECT.PROJ_FAI_BUDDHIST,
PMS_PROJECT.PROJ_FAI_CHRISTIAN,
PMS_PROJECT.PROJ_FAI_HINDU,
PMS_PROJECT.PROJ_FAI_JEWISH,
PMS_PROJECT.PROJ_FAI_MUSLIM,
PMS_PROJECT.PROJ_FAI_NONE,
PMS_PROJECT.PROJ_FAI_SPECIFY,
PMS_PROJECT.PROJ_FAITH_OTHER,
PMS_PROJECT.PROJ_FAITH_SPECIFY,
PMS_PROJECT.PROJ_FIN_ACT_CAP_TD,
PMS_PROJECT.PROJ_FIN_ACT_REV_TD,
PMS_PROJECT.PROJ_FIN_FOR_CAP_TD,
PMS_PROJECT.PROJ_FIN_FOR_REV_TD,
PMS_PROJECT.PROJ_GENDER,
PMS_PROJECT.PROJ_GROUP,
PMS_PROJECT.PROJ_INDORG,
PMS_PROJECT.PROJ_LOG,
PMS_PROJECT.PROJ_MANAGER,
PMS_PROJECT.PROJ_NOTES,
PMS_PROJECT.PROJ_OUTCOME_PMI,
PMS_PROJECT.PROJ_OUTPUT_PMI,
PMS_PROJECT.PROJ_POSTCODE,
PMS_PROJECT.PROJ_REF,
PMS_PROJECT.PROJ_RELIGION_SPECIFY,
PMS_PROJECT.PROJ_REP_CLAIMS,
PMS_PROJECT.PROJ_REP_FUNDING,
PMS_PROJECT.PROJ_REP_HUMAN,
PMS_PROJECT.PROJ_REP_MILESTONES,
PMS_PROJECT.PROJ_REP_OTHER,
PMS_PROJECT.PROJ_REP_OUTPUTS,
PMS_PROJECT.PROJ_RTH_WHITEBR,
PMS_PROJECT.PROJ_START,
PMS_PROJECT.PROJ_STAT_FUNDING,
PMS_PROJECT.PROJ_STAT_HUMAN,
PMS_PROJECT.PROJ_STAT_OTHER,
PMS_PROJECT.PROJ_STAT_OUTPUTS,
PMS_PROJECT.PROJ_STORE1,
PMS_PROJECT.PROJ_STORE3,
PMS_PROJECT.PROJ_STORE4,
PMS_PROJECT.PROJ_TEL,
PMS_PROJECT.PROJ_TITLE,
PMS_PROJECT.PROJ_TOWN,
PMS_PROJECT.PROJ_UID,
PMS_PROJECT.PROJECT_END,
TMS_PROJ_WEEKLY.audit audit_3,
TMS_PROJ_WEEKLY.Authorised,
TMS_PROJ_WEEKLY.AuthorisedBy,
TMS_PROJ_WEEKLY.AuthorisedDate,
TMS_PROJ_WEEKLY.Flexitime,
TMS_PROJ_WEEKLY.FlexiTimeOffset,
TMS_PROJ_WEEKLY.Holiday,
TMS_PROJ_WEEKLY.HolidayOffset,
TMS_PROJ_WEEKLY.HoursWorked HoursWorked_2,
TMS_PROJ_WEEKLY.UserID UserID_2,
TMS_PROJ_WEEKLY.WeekDate,
TMS_PROJ_WEEKLY.WeeklyAI,
TMS_PROJ_WEEKLY.WeekNo WeekNo_2,
PMS_LU_MANAGERS.AccessLevel,
PMS_LU_MANAGERS.AccessLevel AccessLevel_2,
PMS_LU_MANAGERS.Active,
PMS_LU_MANAGERS.Active Active_2,
PMS_LU_MANAGERS.AIMANAGER,
PMS_LU_MANAGERS.AIMANAGER AIMANAGER_2,
PMS_LU_MANAGERS.AllowSAPS,
PMS_LU_MANAGERS.AllowSAPS AllowSAPS_2,
PMS_LU_MANAGERS.ContractedWeeklyHours,
PMS_LU_MANAGERS.ContractedWeeklyHours ContractedWeeklyHours_2,
PMS_LU_MANAGERS.email,
PMS_LU_MANAGERS.email email_2,
PMS_LU_MANAGERS.MAN_ADDR1,
PMS_LU_MANAGERS.MAN_ADDR1 MAN_ADDR1_2,
PMS_LU_MANAGERS.MAN_ADDR2,
PMS_LU_MANAGERS.MAN_ADDR2 MAN_ADDR2_2,
PMS_LU_MANAGERS.MAN_COUNTY,
PMS_LU_MANAGERS.MAN_COUNTY MAN_COUNTY_2,
PMS_LU_MANAGERS.MAN_POSTCODE,
PMS_LU_MANAGERS.MAN_POSTCODE MAN_POSTCODE_2,
PMS_LU_MANAGERS.MAN_TEL,
PMS_LU_MANAGERS.MAN_TEL MAN_TEL_2,
PMS_LU_MANAGERS.MAN_TOWN,
PMS_LU_MANAGERS.MAN_TOWN MAN_TOWN_2,
PMS_LU_MANAGERS.MANAGER_NAME,
PMS_LU_MANAGERS.MANAGER_NAME MANAGER_NAME_2,
PMS_LU_MANAGERS.Mobile,
PMS_LU_MANAGERS.Mobile Mobile_2,
Proj_UID < 7 Proj_UID_7,
EXTRACT( YEAR FROM WeekDate ) EXTRACT_YEAR_FROM_WeekDat
FROM TMS_PROJ_HOURS TMS_PROJ_HOURS
INNER JOIN PMS_PROJECT PMS_PROJECT ON
(PMS_PROJECT.PROJ_UID = TMS_PROJ_HOURS.ProjID)
INNER JOIN TMS_PROJ_WEEKLY TMS_PROJ_WEEKLY ON
(TMS_PROJ_WEEKLY.WeeklyAI = TMS_PROJ_HOURS.WeeklyID)
INNER JOIN PMS_LU_MANAGERS PMS_LU_MANAGERS ON
(PMS_LU_MANAGERS.AIMANAGER = TMS_PROJ_HOURS.UserID)
LEFT OUTER JOIN PMS_LU_MANAGERS PMS_LU_MANAGERS_2 ON
(PMS_LU_MANAGERS_2.AIMANAGER = TMS_PROJ_WEEKLY.AuthorisedBy)
WHERE ( TMS_PROJ_HOURS.WeeklyID = 1 )
ORDER BY 120, TMS_PROJ_HOURS.SortOrder

nardmoseley

Is there any way you can construct a similar statement using the DBDemos
data or sample Interbase data, or something we could run here.


--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com

rbuser

I've got to put a project together for Nico to test something else so I'll
try and kill 2 hampsters with 1 rock Might be a few days before I can get
anything to you though as work load is high ATM

rbuser

demo project send to support@digital-metaphors.com>