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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MurpheyApplication 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 OlsenDBACommented:
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
Gary PattersonVP 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Operating Systems

From novice to tech pro — start learning today.