SQL Search in Model Document RTF generation fails

[Ajay]

RTF document generation for Model Document with SQL Search fails with the following error....

Code: [Select]

---------------------------
EA
---------------------------
DAO.Database [3061]

Too few parameters. Expected 1.
---------------------------
OK  
---------------------------

Followed by the following error...

Code: [Select]

---------------------------
EA
---------------------------
Document generation failed
---------------------------
OK  
---------------------------

Please note that this search does not take any parameters. (Actually I had a search with tool parameters and wanted to use that one, but since it was failing with the same error in spite of me specifying the parameter in the SearchValue field I tried one without a parameter.).

The following is the SQL search...

Code: [Select]

SELECT ea_guid AS CLASSGUID, 'Package' AS CLASSTYPE, Name
FROM t_package
where package_id in
(
    SELECT t_object.Package_ID  
    FROM t_objectproperties, t_object
    WHERE t_object.Object_ID=t_objectproperties.Object_ID
    AND t_objectproperties.Property='Interface_Parameterization'
)
union
SELECT t_object.ea_guid AS CLASSGUID, t_object.Object_Type AS CLASSTYPE, Name  
FROM t_objectproperties, t_object
WHERE t_object.Object_ID=t_objectproperties.Object_ID
AND t_objectproperties.Property='Interface_Parameterization'

The above search works with the "Search Model" command.

The version details of EA that I'm using are...
Program Version: 7.5.850 (Build: 850) - Unicode
Database Details: 4.01
Version Date: Jan-31-2004

Please let me know if I'm making some mistake while using this feature.

[Eve]

You're doing something wrong.  EA is just passing your sql on the database you are running on and returning the error it gives.

I've noticed two potential errors.

• package_id may be case sensitive depending on your database.  (This would mean that you have an unknown field name, and this is where I usually see this error.)
• In your second query you are selecting Name.  You may need to make it t_object.Name.

[Geert Bellekens]

I've had issues with unions before.
No problem with an SQL Server backend, but MS Access doesn't seem to like it.

Geert

[Ajay]

Hi Simon,

Thanks for your quick response.

I tried the changes suggested by you. But I get the same error again.

The query I used now is...

Code: [Select]

SELECT t_package.ea_guid AS CLASSGUID, 'Package' AS CLASSTYPE, t_package.Name
FROM t_package
where t_package.Package_ID in
(
    SELECT t_object.Package_ID  
    FROM t_objectproperties, t_object
    WHERE t_object.Object_ID=t_objectproperties.Object_ID
    AND t_objectproperties.Property='Interface_Parameterization'
)
union
SELECT t_object.ea_guid AS CLASSGUID, t_object.Object_Type AS CLASSTYPE, t_object.Name
FROM t_objectproperties, t_object
WHERE t_object.Object_ID=t_objectproperties.Object_ID
AND t_objectproperties.Property='Interface_Parameterization'


Please note that this (and the earlier query) works when I run them using the "Search Model" functionality. So it has something to do with the Model Document stuff.

[Ajay]

@Geert Bellekens:
This query works with the "Search Model" / "Find In Model" option (CTRL+F). It fails when I integrate it with the Model Document Tagged Value "SearchName".

[Ajay]

An update...

There is no problem with the second part of the union. There is an issue with the 1st query...

Code: [Select]

SELECT t_package.ea_guid AS CLASSGUID, 'Package' AS CLASSTYPE, t_package.Name
FROM t_package
where t_package.Package_ID in
(
    SELECT t_object.Package_ID  
    FROM t_objectproperties, t_object
    WHERE t_object.Object_ID=t_objectproperties.Object_ID
    AND t_objectproperties.Property='Interface_Parameterization'
)

Can someone suggest what the issue is?

(This query does not fail in "Search Model" either.)

[Geert Bellekens]

Try this query:

Code: [Select]

SELECT o.ea_guid AS CLASSGUID, o.Object_Type AS CLASSTYPE, o.Name
FROM  (t_object o
left outer join t_objectproperties op on o.Object_ID = op.Object_ID)
where
op.Property='Interface_Parameterization'
OR
o.PDATA1 = (select cstr(package.PDATA1) from ( (t_object package
inner join t_object o2 on cstr(o2.Package_ID) = package.PDATA1)
inner join t_objectproperties op2 on o2.Object_ID = op2.Object_ID)
where op2.Property='Interface_Parameterization')
I'm still betting on the union operation being the issue here;

Geert

[Ajay]

@Geert Bellekens
I get the following error on execution of the query...

Code: [Select]

---------------------------
EA
---------------------------
DAO.Database [3354]

At most one record can be returned by this subquery.
---------------------------
OK  
---------------------------

The error occurs in the "Search Model" function. Hence have not reached the stage where I can try it in the Model Document part.

[Geert Bellekens]

Oop   , this:

Code: [Select]

SELECT o.ea_guid AS CLASSGUID, o.Object_Type AS CLASSTYPE, o.Name
FROM  (t_object o
left outer join t_objectproperties op on o.Object_ID = op.Object_ID)
where
op.Property='Interface_Parameterization'
OR
o.PDATA1 in (select cstr(package.PDATA1) from ( (t_object package
inner join t_object o2 on cstr(o2.Package_ID) = package.PDATA1)
inner join t_objectproperties op2 on o2.Object_ID = op2.Object_ID)
where op2.Property='Interface_Parameterization')should work a bit better then

Geert

[Ajay]

@Geert Bellekens

I get the following error...

Code: [Select]

---------------------------
EA
---------------------------
DAO.Database [94]

Invalid use of Null
---------------------------
OK  
---------------------------


[Geert Bellekens]

last try, this works when there are multiple elements with the tagged value

Code: [Select]

SELECT o.ea_guid AS CLASSGUID, o.Object_Type AS CLASSTYPE, o.Name
FROM  (t_object o
left outer join t_objectproperties op on o.Object_ID = op.Object_ID)
where
op.Property='Interface_Parameterization'
OR
o.PDATA1 is not null
AND
o.PDATA1 in (select package.PDATA1 from ( (t_object package
inner join t_object o2 on  o2.Package_ID= cint(package.PDATA1))
inner join t_objectproperties op2 on o2.Object_ID = op2.Object_ID)
where op2.Property='Interface_Parameterization'
and package.PDATA1 is not null)


[Ajay]

@Geert Bellekens

I get the following error...

Code: [Select]

---------------------------
EA
---------------------------
DAO.Recordset [3071]

This expression is typed incorrectly, or it is too complex to be evaluated.  For example, a numeric expression may contain too many complicated elements.  Try simplifying the expression by assigning parts of the expression to variables.
---------------------------
OK  
---------------------------


But before we try to find an alternate query lets try to see if we are heading in the right direction?
Based on what Simon said the query should work as all EA is supposed to do is send the query to the Database engine.
The query does work in the model search functionality hence it should work from the Model Document search query functionality as well.

Can someone from EA confirm if there are any restrictions on the way this works?

[Geert Bellekens]

Strange,

That query works on here on a eap model with version 8.0.862  :-/

Geert

[Ajay]

I have reported this as a bug.
Will update the post when I get some feedback.

[Ajay]

Hi All,

An update from my side...

1. I have not got any response on my bug report.

2. I tried the issue on the latest version 8.0.863 of EA. The issue still exists.