Sql to get its info from fields of a model element

[amacara1]

The problem I am trying to solve is to write an sql query that depends on 'runtime' - specifically, it gets one of its conditions from another model element.

Say I have an element called 'special info' that has its Keywords field (PData5) set to '0.2', '0.3' and I would like an sql query like:

select Obj.Name, Obj.Alias from t_object as Obj where
Obj.Object_Type='requirement' and Obj.Phase in
(
 select k.PData5 from t_object as k where k.Name='special info'
)

If PData5 is set exactly to 0.2 (no qoutes around it), my sql works fine, but if I set Pdata5 to '0.2' OR 0.2, 0.3 OR '0.2', '0.3' then sql no longer works - it returns empty result.

What am I doing wrong, please?

[Geert Bellekens]

looks like you want to use a like operator

Code: [Select]

select Obj.Name, Obj.Alias
from t_object  Obj
inner join t_object  k on k.Name='special info'
                                 and k.PData5 like '%' + Obj.Phase +'%'
where
Obj.Object_Type='requirement' and Obj.Phase in
Geert

[amacara1]

Thank you, Geert, it works and I understand why it does.
Still - if anyone knows why my initial query (using 'in' clause) does not work, please let me know - as there could be cases when an 'early' join clause could prove not practical

[Geert Bellekens]

because the "in" statement checks for an exact match in a list of values, unlike the "like" operator.

So it will only return those instances where it matches exactly.

Geert

[amacara1]

because the "in" statement checks for an exact match in a list of values, unlike the "like" operator.

So it will only return those instances where it matches exactly.

Geert

Why do you think this is the root cause, please?

PData5 of 'special info' is set to '0.2', '0.3' (this is what I get if I query its 'select' clause only) and my original query has been
... and Obj.Phase in
(
 select k.PData5 from t_object as k where k.Name='special info'
)

Reminder: my original query works fine 'in' only if PData5 of 'special info' is set to 0.2 (without quotes)

[Geert Bellekens]

because the value 0.2 is not equal to the value '0.2','0.3'
If you wanted the "in" to work, you would need to have multiple element with name 'special info' where each of them has a single possible value. e.g.
- 0.2
- 0.3

SQL will not parse and evaluate the string that is returned by the subquery; it tests if it matches one of the returned strings exactly; and in this case it doesn't.

Geert