Is "Calculated Field" possible in AS400 iSeries SQL table?

Is a calculated field possible in AS400 iSeries V5R4 SQL ?

I can't find any documentation on "Calculated Field" is it called something else?

Like .... MyTable.FieldABCD = (MyTable.FieldA - MyTable.FieldB) * (MyTable.FieldC + MyTable.FieldD)
LVL 5
volkingAsked:
Who is Participating?
 
Gary PattersonConnect With a Mentor VP Technology / Senior Consultant Commented:
Create a view:  http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/sqlp/rbafyviewnt.htm.

Then use the view in your query instead of the underlying table.  Be aware that the system has to maintain ever view and index you create, so excessive view creation can impact database performance (especially WRITE and UPDATE performance).

Sometimes, it is better to just use calculated columns at the time that you actually consume the data in an application in the SELECT statement that you use to create a recordset, for example.  That way the system doesn't have to maintain the view around the clock - it just creates it temporarily while you use it, and then deletes it until you ask for it again.

This is particularly true for calculated columns that are used infrequently (once a day, for example).

SELECT SELECT FieldA, FieldB, (FieldA * FieldB)
    FROM MyTable

- Gary Patterson



CREATE VIEW MyView (FieldA, FieldB, FieldAB) AS
   SELECT FieldA, FieldB, (FieldA * FieldB)
    FROM MyTable

Open in new window

0
 
MurpheyConnect With a Mentor Application ConsultantCommented:
Yes, no problem just something like :

Select AMT, PCT / 100 where AMT * (PCT / 100) > 5

Regards,
Murph
0
 
MurpheyApplication ConsultantCommented:
OOPS

Select AMT, PCT / 100 from MyLib/MySales where AMT * (PCT / 100) > 5

0
 
volkingAuthor Commented:
Opps My Goof ... should have been more specific ... I need a calculated field in an MQT ...

For example ... the below DOES WORK, but is horribly slow because the three function calls are horribly slow and all three function are each called TWICE.
     CSIDEV.CALCFREIGHT ( SALIND00.INVOICENUM )
     CSIDEV.CALCENGINER ( SALIND00.INVOICENUM )
     CSIDEV.CALCSHIPPNG ( SALIND00.INVOICENUM )

------------------------------------------------------------------------------------
CREATE TABLE CSIDEV.SALINDMQ0 (
     GROSS ,
     FREIGHT,
     ENGINEER,
     SHIPPING,
     NET )  
     AS (
     SELECT
               SALIND00.SIIAMT ,
               CSIDEV.CALCFREIGHT ( SALIND00.INVOICENUM ),
               CSIDEV.CALCENGINER ( SALIND00.INVOICENUM ),
               CSIDEV.CALCSHIPPNG ( SALIND00.INVOICENUM ),
               SALIND00.SIIAMT -
                    ( CSIDEV.CALCFREIGHT ( SALIND00.INVOICENUM )
                    + CSIDEV.CALCENGINER ( SALIND00.INVOICENUM )
                    + CSIDEV.CALCSHIPPNG ( SALIND00.INVOICENUM )
                    )
          FROM
               CSIDB.SALIND00 SALIND00
          GROUP BY
               SIIVNO
     )
     DATA INITIALLY DEFERRED
     REFRESH DEFERRED
     MAINTAINED BY USER
     ENABLE QUERY OPTIMIZATION;

------------------------------------------------------------------------------------
So I hoped there was a way of declaring that NET = (GROSS - (FREIGHT + ENGINER + SHIPPNG))

A calculated field .....

Possible?
0
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:
Hi Fred,

That should work just fine.  The DB2 Materialized table supports indexing so if the query on the table is slow, that suggests a large number of rows and that an index on the key elements should help performance a great deal.


Good Luck,
Kent
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.