CODEWITHSUNDEEP
enterprise-architect
vascobnunes
vascobnunes

Reputation: 213

Is it possible to add sql functions to an sql query?

I need to concatenate the result of a subquery in a file based model (i.e. with ms access) and following the suggestions I found here, I could define a function to use in the query.

I try that in the SQL scratch pad but nothing happens...

Function Coalsce(strSQL As String, strDelim, ParamArray NameList() As Variant)
Dim db As Database
Dim rs As DAO.Recordset
Dim strList As String

Set db = CurrentDb

If strSQL <> "" Then
    Set rs = db.OpenRecordset(strSQL)

    Do While Not rs.EOF
        strList = strList & strDelim & rs.Fields(0)
        rs.MoveNext
    Loop

    strList = Mid(strList, Len(strDelim))
Else

    strList = Join(NameList, strDelim)
End If

Coalsce = strList

End Function

select switch(Coalsce(SELECT DISTINCT ', ' + obj_phase2.name as [text()]
        FROM ((((t_object obj_ds2
            INNER JOIN t_connector co2
              ON (obj_ds2.object_id = co2.start_object_id))
            INNER JOIN t_object obj_adu2
              ON (co2.end_object_id = obj_adu2.object_id
              AND obj_adu2.Stereotype='EUM_ADU'))
            LEFT JOIN t_object obj_prx2
              ON (obj_prx2.Classifier_guid = co2.ea_guid))
            LEFT JOIN t_connector pc2
             ON (obj_prx2.Object_ID = pc2.Start_Object_ID AND pc2.Stereotype = 'trace'))
            LEFT JOIN t_object obj_phase2
             ON (pc2.End_Object_ID = obj_phase2.object_id)
        WHERE obj_ds2.Stereotype = 'EUM_Data-Stream' AND obj_ds2.Object_ID = 13919)='', '', 
1=1, 'teste') as [test]
from t_object

Is this possible?

Upvotes: 0

Views: 85

Answers (1)

qwerty_so
qwerty_so

Reputation: 36313

No. EA only allows SELECT statements in the scratchpad. EA is doing some black magic with the SQL before sending it to the database. Misty and obscure that is. But for sure you can not send VB code (or any other advanced stuff) through the pad.

If you want to get the above working you need to place the VB code in a script calling the SELECT via repository.SQLQuery.

It would probably be possible to run such stuff via a native ODBC or driver connection. However, I would absolutely not recommend doing that. I wouldn't do that myself and I've done a lot of strange things with EA.

Upvotes: 2

Related Questions