It depends on the type of virtual field you are using and the mapping type you define that determines if the results are stored in the SQL server or not.
There are 3 types of mapping you can use in the EDA mapping tool for virtual fields.
Simple – a simple virtual attribute/I-descriptor, such as A + B. These are virtual attribute formulas that are translated to expressions and SCALAR functions.
So if you mapped a SUBR type virtual field to the external database, then the external database would be updated when the record is updated within UniVerse automatically. If you didn't map the SUBR type virtual field, the SUBR virtual field would only be available in UniVerse.
I hope this answers your question.
Original Message:
Sent: 11-19-2024 16:06
From: Mark Mosbauer
Subject: EDA & itypes
Hi Jonathan, i broadly understand itype handling in universe - i'm more interested in EDA handling.
From what you are saying, sounds like you could use EDA to map an itype to a SQL calculated field?
ie if your universe field was defined like
| Fieldname | Field type | Definition/calculation |
| ProductID | Dtype | |
| QtyAvailable | Dtype | |
| Unitprice | Dtype | |
| InventoryValue | Itype | QtyAvailable * Unitprice |
EDA could create the SQL table to look like
CREATE TABLE dbo.Products ( ProductID int IDENTITY (1,1) NOT NULL , QtyAvailable smallint , UnitPrice money , InventoryValue AS QtyAvailable * UnitPrice );
OR alternatively you could choose for EDA to store the output of the itype & in effect EDA would create a table like
CREATE TABLE dbo.Products ( ProductID int IDENTITY (1,1) NOT NULL , QtyAvailable smallint , UnitPrice money , InventoryValue money);
In this example - EDA calculates InventoryValue & stores the output in the SQL column InventoryValue as records are created
I would assume what you can do in an Itype exceeds the functionality of SQL calculated fields , so in some cases , you'd be forced down the path of using EDA to store the output of an itype calculation - especially if you are using a subroutine.
In this scenario - I'm trying to understand what EDA does (if anything) if the logic of the itype changes.
Hope that explains it
------------------------------
Mark Mosbauer
Project Manager
Rocket Forum Shared Account
Original Message:
Sent: 11-19-2024 04:51
From: Jonathan Smith
Subject: EDA & itypes
Mark,
To help you better understand an I-Type can also be called a V-Type and a V-Type is a virtual field. The term Virtual Field may help you better understand what they do. The Virtual Field itself does not exist in the U2 File (or in the SQL Table). The Virtual Field is simply a dictionary definition used at runtime to use an expression (or group of expressions) to produce a result.
So in your example the SURNAME, MIDDLENAME and LASTNAME are data fields in the file itself or in the SQL Table if the fields have been EDA mapped into a SQL Table.
The Virtual Field (let's call it FULLNAME) gets the values of SURNAME, MIDDLENAME and LASTNAME while your report or query is being run and combines into one result, the I-Type or V-Type field so that it appears to be an actual field. When any of the values SURNAME, MIDDLENAME or LASTNAME change then the result of FULLNAME changes automatically. As easier comparison for you is to think of a Virtual Field in U2 like a calculated field in SQL. In the sense that a calculated field either performs some calculation on database fields to create a value that is not directly stored in the database.
If you are used to a calculated field in SQL returning a value that is not directly stored in the database, then you will understand that the I Type / V Type fields are doing the same in the U2 File. If you are used to this concept this should answer all your questions around the 'simple' and 'complex' changes you asked.
The virtual field result will be automatically recalculated when the underlying data changes, be it a simple virtual field or one using a subroutine. If the subroutine changes and produces a new result the virtual field will automatically reflect this.
From all the questions you are asking it seems as though you would benefit from a chat about EDA with your support provider or Rocket Sales Engineer. Have you tried contacting them, they are ideally placed to talk you through all the questions you have.
Regards,
------------------------------
Jonathan Smith
UniData ATS
Rocket Support
Original Message:
Sent: 11-18-2024 22:45
From: Mark Mosbauer
Subject: EDA & itypes
thanks - follow-up questions
not clear on above if EDA just handles after recompiling
some itypes scenarios i'm thinking about...
'simple' change
A data field/element in an itype changes
Itype before Name = First Name + Lastname
Itype after Name = First Name + Middle Name + Lastname
- Will changing the itype definition force Name to be recalculated & resynced in sql? / Do you have to redefine that field in EDA?
- What happens when you change Surname for one record – does Name change automatically
'complex' change
Itype containing a subroutine
- how do you resync all the records calling that subroutine for scenarios below – does EDA just take care of it/do you need to manually trigger something
- what's the impact if you have heaps of records in the d/b 100K->1mil +
- What happens when the subroutine changes
- What happens when the underlying data being called by the subroutine changes
------------------------------
Mark Mosbauer
Project Manager
Rocket Forum Shared Account
Original Message:
Sent: 11-18-2024 22:38
From: Greg Clitheroe
Subject: EDA & itypes
Data in a file based UniVerse database is typeless, all just character string. The datatypes can be defined and conversions applied in the DICT records.
I-descriptors need to be recompiled after being adjusted but there is no effect on the data itself.
Subroutines need to be recompiled and cataloged after any changes.
------------------------------
Greg Clitheroe
Rocket Forum Shared Account
Original Message:
Sent: 11-18-2024 19:57
From: Mark Mosbauer
Subject: EDA & itypes
Question on EDA - from someone outside of/new to the Rocket Community....(excuse the rookie knowledge/terminology)
1. Are there any good/bad practices around the handling I-types in EDA ie pros/coms
2. How does EDA handle changes in i-type definitions / changes to subroutines and/or changes in data that subroutines access - ie does the tool/dtabase automatically identity changes , or does the field being converted have to be reinitialised in some way + what is say the downstream impact of replicating a field change in a table of a million records
------------------------------
Mark Mosbauer
Project Manager
Rocket Forum Shared Account
------------------------------