[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2117
  • Last Modified:

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)
0
volking
Asked:
volking
3 Solutions
 
theo kouwenhovenCommented:
Yes, no problem just something like :

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

Regards,
Murph
0
 
theo kouwenhovenCommented:
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 OlsenData 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
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now