Sparx Systems Forum
Enterprise Architect => General Board => Topic started by: Richard Freggi on December 25, 2019, 03:36:59 pm
-
I need to copy/paste from the query result window into Excel. For results to paste correctly into Excel using "Paste - Use text import wizard" I need to enclose each field in the query result into a delimiter: I use ".
So I need to concatenate " before and each query result, using the || operator. However this operation FAILS for the trailing concatenation for Memo fields of Tagged Values!
For example, the line
'"' || op4.Notes || '"' AS "Remarks",
Produces this result (note no " at the end of the string):
"Constrained by xyz interface
Only the following workaround seems to work (it's kludgy and I need to drop the last character of the memo field to make it work - so I'm looking for a cleaner solution)
'"' || (SUBSTRING(op4.Notes) FROM 1 FOR CHAR_LENGTH(op4.Notes)-1) || '"' AS "Remarks"
The result is then (the trailing collation succeeds but the last character is lost):
"Constrained by xyz interfac"
I checked the Memo fields for non-printing characters or anything that could mess up the collation but cannot find anything strange (actually I clean up the memo string before collation for carriage return and " text that could confuse Excel - my actual query is:
'"' || SUBSTRING(REPLACE (REPLACE (REPLACE (REPLACE (COALESCE(op4.Notes, ''), ASCII_CHAR(13), ' '), ASCII_CHAR(10), '. '), ASCII_CHAR(9), ' '), ASCII_CHAR(34), '''') FROM 1 FOR CHAR_LENGTH(op4.Notes)-1) || '"' AS "Remarks",
I checked Firebird documentation and googled around but cannot find anything related. I suspect it's a EA bug related to Memo fields.
If anyone knows a more elegant solution that does not require me to lose the last character of the field let me know!
p.s. I add a couple spaces when I write the tagged value memos so all that gets cut off is just a space, but it's a chore
I did not have this problem with *.eap Access repositories
I could start messing around with either concatenating a sacrificial character to the string then removing it with SUBSTRING, or adding back the last character after collation or weird stuff like this but before I go down that kludge-fest I'd like to know if there is a better solution!
EA v. 1310
Thanks!
-
Are you using EA to query the DB? If so, cross check with a native client and eventually send a bug report.
In any case: do the string operation outside the query in a script.
q.
-
Yes I am using the EA search function CRTL-F and "my queries"
I need to go from copying the search function result to paste directly into Excel
I made a macro to modify the result in Notepad++, doable but not ideal. I need to do the string manipulation as part of the DML query.
-
EA interferes with queries. And as we know there are a couple of quirks (the capital AS and accessing Notes come to mind immediately). Since a fix for 13.5 will not be made you were to wait for EA16 or so. I could think of using an add-in query to do the string magic. I only did this once. Also: if you need that for Excel, then why not writing a macro there. For sure you can get native client dlls to access a firebird (or was it -base? I always get confused) database.
q.
-
Interestingly, I only get the behavior you describe when querying t_operation.
Using the following queries, the results are correct for me.
select '"' || Note || '"' as Remarks from t_object where Object_Type='Class'
This is loses a character.
select '"' || Notes || '"' as Remarks from t_operation
This works for all fields until it doesn't. (Changing one field can break other fields)
select '"' || COALESCE(Notes, '') || '"' as Remarks,
'"' || COALESCE(Code, '') || '"' as Code,
'"' || COALESCE(StyleEx, '') || '"' as StyleEx,
'"' || COALESCE(Behaviour, '') || '"' as Behavior
from t_operation
My suspicion is that the error is inside the ODBC driver. Unfortunately, none of that helps you get what you are after.