ADO and "Incorrect syntax near" in auto-generated SQL

rbuser

Can anyone explain why a DADE designed query might cause ADO problems,
when taking the exact same SQL and using it in "edit SQL mode" works fine?

I set up a query, as detailed in the RPT file segment below, and when I do
a preview it stops in (Delphi 7 Pro, no SP) ADODB.pas line 3472 with an
EOleExeption "line 15: Incorrect syntax near 'AssessmentPOC'". I took the
SQL and ran it in MS SQL Server Query Analyser, and it gets the results
expected. The designer is set for MS SQL, and other options make no
difference if I change them. This is the 6th query I created, and the
others are working fine.

It looks as though there is something else happening therefore between the
SQL shown (and stored) and the use of it at display time. I'd welcome any
pointers on this, as I've lost a day fiddling so far. Many thanks.

/Matthew Jones/


object daADOQueryDataView6: TdaADOQueryDataView
UserName = 'Query_AssessmentPOC'
Height = 211
Left = 371
NameColumnWidth = 105
SizeColumnWidth = 35
SortMode = 0
Top = 462
TypeColumnWidth = 52
Width = 352
AutoSearchTabOrder = 0
MasterDataViewUserName = 'Query_AssessmentMaster'
object AssessmentPOC: TppChildDBPipeline
AutoCreateFields = False
SkipWhenNoRecords = False
UserName = 'AssessmentPOC'
MasterDataPipelineName = 'AssessmentMaster'
object ppField1: TppField
FieldAlias = 'AP_AssignmentLinkID'
FieldName = 'AP_AssignmentLinkID'
FieldLength = 0
DataType = dtInteger
DisplayWidth = 10
Position = 0
TableName = 'AssessmentPOC'
end
object ppField2: TppField
FieldAlias = 'PI_PersonInfoID'
FieldName = 'PI_PersonInfoID'
FieldLength = 0
IsDetail = True
DataType = dtInteger
DisplayWidth = 10
Position = 1
TableName = 'PersonInfo'
end
object ppField3: TppField
FieldAlias = 'TI_Abbreviation'
FieldName = 'TI_Abbreviation'
FieldLength = 200
IsDetail = True
DisplayWidth = 200
Position = 2
TableName = 'TitleInfo'
end
object ppField4: TppField
FieldAlias = 'PI_FirstName'
FieldName = 'PI_FirstName'
FieldLength = 50
IsDetail = True
DisplayWidth = 50
Position = 3
TableName = 'PersonInfo'
end
object ppField5: TppField
FieldAlias = 'PI_LastName'
FieldName = 'PI_LastName'
FieldLength = 50
IsDetail = True
DisplayWidth = 50
Position = 4
TableName = 'PersonInfo'
end
object ppField6: TppField
FieldAlias = 'RI_Name'
FieldName = 'RI_Name'
FieldLength = 50
IsDetail = True
DisplayWidth = 50
Position = 5
TableName = 'RoleInfo'
end
object ppField7: TppField
FieldAlias = 'AP_Analyst'
FieldName = 'AP_Analyst'
FieldLength = 0
DataType = dtBoolean
DisplayWidth = 5
Position = 6
TableName = 'AssessmentPOC'
end
object ppField8: TppField
FieldAlias = 'AP_AssessmentMasterID'
FieldName = 'AP_AssessmentMasterID'
FieldLength = 0
DataType = dtInteger
DisplayWidth = 10
Position = 7
TableName = 'AssessmentPOC'
end
object TppMasterFieldLink
MasterFieldName = 'AM_FinishDate'
DetailFieldName = 'AM_FinishDate'
DetailSortOrder = soAscending
IsCaseSensitive = True
end
object TppMasterFieldLink
MasterFieldName = 'AM_AssessmentMasterID'
DetailFieldName = 'TI_Abbreviation'
DetailSortOrder = soAscending
IsCaseSensitive = True
end
end
object daSQL6: TdaSQL
DatabaseName = 'ADOConnection'
DatabaseType = dtMSAccess
DataPipelineName = 'AssessmentPOC'
Distinct = True
IsCaseSensitive = True
LinkColor = clMaroon
MaxSQLFieldAliasLength = 25
SkipWhenNoRecords = False
SQLText.Strings = (
'SELECT DISTINCT '
' AssessmentPOC.AP_AssignmentLinkID, '
' PersonInfo.PI_PersonInfoID, '
' TitleInfo.TI_Abbreviation, '
' PersonInfo.PI_FirstName, '
' PersonInfo.PI_LastName, '
' RoleInfo.RI_Name, '
' AssessmentPOC.AP_Analyst, '
' AssessmentPOC.AP_AssessmentMasterID'
'FROM (((((AssessmentPOC AssessmentPOC'
' INNER JOIN AssessmentMaster AssessmentMaster ON '

' (AssessmentMaster.AM_AssessmentMasterID = AssessmentPOC
.AP_' +
'AssessmentMasterID) )'
' INNER JOIN AssignmentLink AssignmentLink ON '

' (AssignmentLink.AL_AssignmentLinkID = AssessmentPOC.
AP_Assi' +
'gnmentLinkID) )'
' INNER JOIN PersonInfo PersonInfo ON '

' (PersonInfo.PI_PersonInfoID = AssignmentLink.
AL_PersonInfoI' +
'D) )'
' LEFT OUTER JOIN RoleInfo RoleInfo ON '
' (RoleInfo.RI_RoleInfoID = AssignmentLink.AL_RoleInfoID) )'
' LEFT OUTER JOIN TitleInfo TitleInfo ON '
' (TitleInfo.TI_TitleInfoID = PersonInfo.PI_TitleInfoID) )'
'ORDER BY PersonInfo.PI_LastName')
SQLType = sqSQL2
object daField1: TdaField
Alias = 'AP_AssignmentLinkID'
DataType = dtInteger
DisplayWidth = 10
FieldAlias = 'AP_AssignmentLinkID'
FieldLength = 0
FieldName = 'AP_AssignmentLinkID'
SQLFieldName = 'AP_AssignmentLinkID'
TableAlias = 'AssessmentPOC'
TableName = 'AssessmentPOC'
TableSQLAlias = 'AssessmentPOC'
end
object daField2: TdaField
Alias = 'PI_PersonInfoID'
DataType = dtInteger
DisplayWidth = 10
FieldAlias = 'PI_PersonInfoID'
FieldLength = 0
FieldName = 'PI_PersonInfoID'
IsDetail = True
SQLFieldName = 'PI_PersonInfoID'
TableAlias = 'PersonInfo'
TableName = 'PersonInfo'
TableSQLAlias = 'PersonInfo'
end
object daField3: TdaField
Alias = 'TI_Abbreviation'
DisplayWidth = 200
FieldAlias = 'TI_Abbreviation'
FieldLength = 200
FieldName = 'TI_Abbreviation'
IsDetail = True
SQLFieldName = 'TI_Abbreviation'
TableAlias = 'TitleInfo'
TableName = 'TitleInfo'
TableSQLAlias = 'TitleInfo'
end
object daField4: TdaField
Alias = 'PI_FirstName'
DisplayWidth = 50
FieldAlias = 'PI_FirstName'
FieldLength = 50
FieldName = 'PI_FirstName'
IsDetail = True
SQLFieldName = 'PI_FirstName'
TableAlias = 'PersonInfo'
TableName = 'PersonInfo'
TableSQLAlias = 'PersonInfo'
end
object daField5: TdaField
Alias = 'PI_LastName'
DisplayWidth = 50
FieldAlias = 'PI_LastName'
FieldLength = 50
FieldName = 'PI_LastName'
IsDetail = True
SQLFieldName = 'PI_LastName'
TableAlias = 'PersonInfo'
TableName = 'PersonInfo'
TableSQLAlias = 'PersonInfo'
end
object daField6: TdaField
Alias = 'RI_Name'
DisplayWidth = 50
FieldAlias = 'RI_Name'
FieldLength = 50
FieldName = 'RI_Name'
IsDetail = True
SQLFieldName = 'RI_Name'
TableAlias = 'RoleInfo'
TableName = 'RoleInfo'
TableSQLAlias = 'RoleInfo'
end
object daField7: TdaField
Alias = 'AP_Analyst'
DataType = dtBoolean
DisplayWidth = 5
FieldAlias = 'AP_Analyst'
FieldLength = 0
FieldName = 'AP_Analyst'
SQLFieldName = 'AP_Analyst'
TableAlias = 'AssessmentPOC'
TableName = 'AssessmentPOC'
TableSQLAlias = 'AssessmentPOC'
end
object daField8: TdaField
Alias = 'AP_AssessmentMasterID'
DataType = dtInteger
DisplayWidth = 10
FieldAlias = 'AP_AssessmentMasterID'
FieldLength = 0
FieldName = 'AP_AssessmentMasterID'
SQLFieldName = 'AP_AssessmentMasterID'
TableAlias = 'AssessmentPOC'
TableName = 'AssessmentPOC'
TableSQLAlias = 'AssessmentPOC'
end
object daField9: TdaField
ChildType = 2
Alias = 'PI_LastName'
DisplayWidth = 50
FieldAlias = 'PI_LastName'
FieldLength = 50
FieldName = 'PI_LastName'
SQLFieldName = 'PI_LastName'
TableAlias = 'PersonInfo'
TableName = 'PersonInfo'
TableSQLAlias = 'PersonInfo'
end
object daTable1: TdaTable
ChildType = 5
Alias = 'AssessmentPOC'
JoinType = dajtNone
SQLAlias = 'AssessmentPOC'
TableAlias = 'AssessmentPOC'
TableName = 'AssessmentPOC'
end
object daTable2: TdaTable
ChildType = 5
Alias = 'AssessmentMaster'
SQLAlias = 'AssessmentMaster'
TableAlias = 'AssessmentMaster'
TableName = 'AssessmentMaster'
object daTableJoin1: TdaTableJoin
ChildType = 6
object daField11: TdaField
ChildType = 3
Alias = 'AM_AssessmentMasterID'
DataType = dtInteger
DisplayWidth = 10
FieldAlias = 'AM_AssessmentMasterID'
FieldLength = 0
FieldName = 'AM_AssessmentMasterID'
SQLFieldName = 'AM_AssessmentMasterID'
TableAlias = 'AssessmentMaster'
TableName = 'AssessmentMaster'
TableSQLAlias = 'AssessmentMaster'
end
object daField10: TdaField
ChildType = 4
Alias = 'AP_AssessmentMasterID'
DataType = dtInteger
DisplayWidth = 10
FieldAlias = 'AP_AssessmentMasterID'
FieldLength = 0
FieldName = 'AP_AssessmentMasterID'
SQLFieldName = 'AP_AssessmentMasterID'
TableAlias = 'AssessmentPOC'
TableName = 'AssessmentPOC'
TableSQLAlias = 'AssessmentPOC'
end
end
end
object daTable3: TdaTable
ChildType = 5
Alias = 'AssignmentLink'
SQLAlias = 'AssignmentLink'
TableAlias = 'AssignmentLink'
TableName = 'AssignmentLink'
object daTableJoin2: TdaTableJoin
ChildType = 6
object daField13: TdaField
ChildType = 3
Alias = 'AL_AssignmentLinkID'
DataType = dtInteger
DisplayWidth = 10
FieldAlias = 'AL_AssignmentLinkID'
FieldLength = 0
FieldName = 'AL_AssignmentLinkID'
SQLFieldName = 'AL_AssignmentLinkID'
TableAlias = 'AssignmentLink'
TableName = 'AssignmentLink'
TableSQLAlias = 'AssignmentLink'
end
object daField12: TdaField
ChildType = 4
Alias = 'AP_AssignmentLinkID'
DataType = dtInteger
DisplayWidth = 10
FieldAlias = 'AP_AssignmentLinkID'
FieldLength = 0
FieldName = 'AP_AssignmentLinkID'
SQLFieldName = 'AP_AssignmentLinkID'
TableAlias = 'AssessmentPOC'
TableName = 'AssessmentPOC'
TableSQLAlias = 'AssessmentPOC'
end
end
end
object daTable4: TdaTable
ChildType = 5
Alias = 'PersonInfo'
SQLAlias = 'PersonInfo'
TableAlias = 'PersonInfo'
TableName = 'PersonInfo'
object daTableJoin3: TdaTableJoin
ChildType = 6
object daField15: TdaField
ChildType = 3
Alias = 'PI_PersonInfoID'
DataType = dtInteger
DisplayWidth = 10
FieldAlias = 'PI_PersonInfoID'
FieldLength = 0
FieldName = 'PI_PersonInfoID'
SQLFieldName = 'PI_PersonInfoID'
TableAlias = 'PersonInfo'
TableName = 'PersonInfo'
TableSQLAlias = 'PersonInfo'
end
object daField14: TdaField
ChildType = 4
Alias = 'AL_PersonInfoID'
DataType = dtInteger
DisplayWidth = 10
FieldAlias = 'AL_PersonInfoID'
FieldLength = 0
FieldName = 'AL_PersonInfoID'
SQLFieldName = 'AL_PersonInfoID'
TableAlias = 'AssignmentLink'
TableName = 'AssignmentLink'
TableSQLAlias = 'AssignmentLink'
end
end
end
object daTable5: TdaTable
ChildType = 5
Alias = 'RoleInfo'
JoinType = dajtLeftOuter
SQLAlias = 'RoleInfo'
TableAlias = 'RoleInfo'
TableName = 'RoleInfo'
object daTableJoin4: TdaTableJoin
ChildType = 6
object daField17: TdaField
ChildType = 3
Alias = 'RI_RoleInfoID'
DataType = dtInteger
DisplayWidth = 10
FieldAlias = 'RI_RoleInfoID'
FieldLength = 0
FieldName = 'RI_RoleInfoID'
SQLFieldName = 'RI_RoleInfoID'
TableAlias = 'RoleInfo'
TableName = 'RoleInfo'
TableSQLAlias = 'RoleInfo'
end
object daField16: TdaField
ChildType = 4
Alias = 'AL_RoleInfoID'
DataType = dtInteger
DisplayWidth = 10
FieldAlias = 'AL_RoleInfoID'
FieldLength = 0
FieldName = 'AL_RoleInfoID'
SQLFieldName = 'AL_RoleInfoID'
TableAlias = 'AssignmentLink'
TableName = 'AssignmentLink'
TableSQLAlias = 'AssignmentLink'
end
end
end
object daTable6: TdaTable
ChildType = 5
Alias = 'TitleInfo'
JoinType = dajtLeftOuter
SQLAlias = 'TitleInfo'
TableAlias = 'TitleInfo'
TableName = 'TitleInfo'
object daTableJoin5: TdaTableJoin
ChildType = 6
object daField19: TdaField
ChildType = 3
Alias = 'TI_TitleInfoID'
DataType = dtInteger
DisplayWidth = 10
FieldAlias = 'TI_TitleInfoID'
FieldLength = 0
FieldName = 'TI_TitleInfoID'
SQLFieldName = 'TI_TitleInfoID'
TableAlias = 'TitleInfo'
TableName = 'TitleInfo'
TableSQLAlias = 'TitleInfo'
end
object daField18: TdaField
ChildType = 4
Alias = 'PI_TitleInfoID'
DataType = dtInteger
DisplayWidth = 10
FieldAlias = 'PI_TitleInfoID'
FieldLength = 0
FieldName = 'PI_TitleInfoID'
SQLFieldName = 'PI_TitleInfoID'
TableAlias = 'PersonInfo'
TableName = 'PersonInfo'
TableSQLAlias = 'PersonInfo'
end
end
end
object TdaSQLLink
ChildType = 9
object daField20: TdaField
ChildType = 10
Alias = 'AM_AssessmentMasterID'
DataType = dtInteger
DisplayWidth = 10
FieldAlias = 'AM_AssessmentMasterID'
FieldLength = 0
FieldName = 'AM_AssessmentMasterID'
SQLFieldName = 'AM_AssessmentMasterID'
TableAlias = 'AssessmentMaster'
TableName = 'AssessmentMaster'
TableSQLAlias = 'AssessmentMaster'
end
object daField21: TdaField
ChildType = 11
Alias = 'TI_Abbreviation'
DisplayWidth = 200
FieldAlias = 'TI_Abbreviation'
FieldLength = 200
FieldName = 'TI_Abbreviation'
IsDetail = True
SQLFieldName = 'TI_Abbreviation'
TableAlias = 'TitleInfo'
TableName = 'TitleInfo'
TableSQLAlias = 'TitleInfo'
end
end
end
end

nardmoseley

When the DataView is a detail query that is linked to a master query special
linking SQL is generated. See article below. From looking at your query, I
can see that it is indeed linked to a master dataview. I can also see that
you are using Distinct and OuterJoin. The linking SQL generation does not
support queries that use those SQL features. Options are to create a custom
dataview (See RBuilder\Demos\EndUser\Custom DataViews) or write code to
define the linking relationship so that the linking SQL will not be
generated. For an example download

www.digital-metaphors.com/tips/LinkDADEPipelines.zip


-------------------------------------------
Tech Tip: Linking DataViews
-------------------------------------------

Overview
---------

Linking DataViews in the ReportBuilder Data workspace (DADE), requires that
the detail data be sorted by the linking fields. When DataViews are linked,
DADE generates special linking SQL for the detail DataView. The linking SQL
(Magic SQL) can be viewed by positioning the mouse over the DataView window
and pressing Ctrl + Left Mouse button. The results of the Magic SQL query
can be viewed by pressing Shift + Left Mouse button.

ReportBuilder's DataPipeline architecture includes support for linked data
traversal. Whenver the record position of the master data changes, the
traversal logic compares the master and detail field link values and then
determines where to locate the record position for the related detail data.
The linking logic will only work properly if the Database engine's collation
of the detail query result set is in sync with ReportBuilder's internaly
field link comparison logic.

--

rbuser

Thanks - not quite as easy as I was hoping, but at least there is a
solution! I may try to remove the DISTINCT etc, but I'm not sure I can - I
was trying to re-create a query known to work in ColdFusion.

I may be back! 8-)

/Matthew Jones/

rbuser

Thanks - it took me a while to find the magic SQL, but knowing that it was
the outer joins that mattered (and Distinct), I removed that part and
added another linked query to get the info, and all seems to be working
well. Much easier than writing my own view!

/Matthew Jones/