RTF Woes

[SomersetGraham]

Hi
I have created a custom search which executes perfectly.
When I try to use it from within a model document I get a dialog stating
'The specified field Object_ID could refer to more than one table listed in the FROM clause of your SQL statement.

Here is my query

Code: [Select]

SELECT ea_guid AS CLASSGUID, Object_Type AS CLASSTYPE, t_objectproblems.Problem, t_object.Name
FROM t_objectproblems INNER JOIN t_object ON t_object.Object_ID = t_objectproblems.Object_ID WHERE t_objectproblems.ProblemType = 'Issue'  

Any help appreciated

Graham

[Geert Bellekens]

try this:

Code: [Select]

SELECT o.ea_guid AS CLASSGUID, o.Object_Type  AS CLASSTYPE, op.Problem, o.Name
FROM t_objectproblems op INNER JOIN t_object o ON o.Object_ID = op.Object_ID
WHERE op.ProblemType = 'Issue'
Geert

[SomersetGraham]

Hi Geert

Thanks for that - it works!!
BUT I dont know why, please coild you expain what I did wrong and how you fixed it?

Thanks

Graham

[Geert Bellekens]

I used aliasses, and not tablenames to identify the different Object_ID columns.
Strictly speaking that should not be necesarry, but in any case it is a lot safer to always use an alias.

I think that for some (bizarre sparxian) reason the full tablenames got stripped from the join clause, which would result in the error you saw.

Geert

[SomersetGraham]

Ah I see - thanks for taking the time

BTW - would there be a way to extract the package name associated with element

Graham

[Geert Bellekens]

Code: [Select]

SELECT o.ea_guid AS CLASSGUID, o.Object_Type  AS CLASSTYPE, op.Problem, o.Name as ElementName, p.Name as PackageName
FROM t_objectproblems op
INNER JOIN t_object o ON o.Object_ID = op.Object_ID
INNER JOIN t_package p on o.Package_ID = p.Package_ID
WHERE op.ProblemType = 'Issue'

[SomersetGraham]

Hi Geert
Thanks for trying but I get
'Syntax Error(missing operator) in query expression'o.Object_ID = op.Object_ID INNER JOIN t_package p on o.Package_ID = p.Package_ID'

Graham

[beginner]

SQL is not necessarily SQL. It depends on the server.

b.

[Geert Bellekens]

That sql works on MS-SQL Server. For Access you have to enclose the joins in parentheses. (look on the community server for examples of EA search definitions)

Geert