![]() |
Telelogic System Architect (steve huntington) | ![]() |
Topic Title: How to display individual properties in a SQLServer Statement. Topic Summary: You know we have individual properties held in the properties field. Who want to look through that though? Created On: 7-Feb-2007 02:37 Status: Read Only |
Linear : Threading : Single : Branch |
![]() |
![]()
|
![]() |
|
As I'm always running SQL queries on SQLServer for System Architect, I decided to create a formula that would extract properties individually from the Properties field and display them as individual fields in the returned recordset. Basically using the Left, Substring and Patindex functions you can display almost all of the properties. There are some exceptions: Edited: 12-Feb-2007 at 10:47 by Duane Hennessy |
|
![]() |
|
![]() |
|
Making it useful. So we can view the properties within an NText field in System Architect as separate columns with the above select statement. We can make this useful for doing updates to the meta-properties via a SQL query (in SQLServer) Today I had to update the Module property from value FI-AR to FI. First I tested the result by creating two columns, the Properties field column, and the result I want Column. Like so... select id, properties, So what I am doing is extracting the left side of the Properties field up to, but not including, "[[[Module]]]". I then append the property and value I want to appear with return chars: + '[[[Module]]]' + char(13) + char(10) + 'FI' + char(13) + char(10) I then append the rest of the Properties field to the end, knowing the next property is "[[[Module Application]]]". + substring(cast(properties as varchar(2000)),charindex('[[[Module Application]]]',cast(properties as varchar(2000))),2000) I included the ID field because without it Encyclopedia Manager in Version 10.0.22 doesn't expand upon the Properties field when the cursor hovers over it. The two resulting column, properties and result_i_want show the before and after shots of how the properties field will look once I have updated it. To be doubley sure I have got this right, I selected each column and saved them into two separate text files and ran a file compare over them. The result of the file compare was: fc fileone.txt filetwo.txt Exactly what I want. Now to run the update I changed the SQL string a little: update entity set properties= In this case I knew the ID number of the record to change. If I was doing a blanket change across many properties I would have to either: - WHERE Properties Like '%[[][[][[]Module]]]' + char(13) + char(10) + 'FI%' or if I have an indexed Properties field I could use: - select id, properties from entity where Contains(Properties,'Module near FI-AR' ) The latter would work well unless there is a FI-AR1 or some such value. Of course, always test the results on a Test Encyclopedia first.
|
|
![]() |
FuseTalk Standard Edition v3.2 - © 1999-2009 FuseTalk Inc. All rights reserved.