As400 sql: How to create a view with keys?

Hi WatchMen,
As400 sql: How to create a view with keys?
So I can use in ROG program a CHAIN instruction?
Thanks
bobdylan75Asked:
Who is Participating?
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.

bobdylan75Author Commented:
Sorry:
RPG Program (and not ROG)
0
Kent OlsenData Warehouse Architect / DBACommented:
Hi Bob,

A view looks just like a table to a query.  Since the view is an extraction from a table, it cannot be indexed.

That said, if the view does not contain a filter or aggregation, any filter applied to a query of a view will attempt to use the indexes of the underlying table.  If the view uses a filter or aggregation, it results in a derived table that the query will then have to scan and filter.

Can't help with the RPG question...


Kent
0
bobdylan75Author Commented:
Ok, but,
I need a VIEW with KEYS,
as it is possible to have a LF DDS with keys,
I need to have samothing like a view with keys,
because I have a field which is the result of a calculation
and this field is the key.
And I don't think I have to create a news table with this field and so
create a LF DDS file (or index).
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

bobdylan75Author Commented:
Something like ORDER BY???
0
Kent OlsenData Warehouse Architect / DBACommented:
Hi Bob,

I'm not sure what you mean by, "view with keys".
0
bobdylan75Author Commented:
Ok:
I need this:
Create View DDSF0023 as                            
select FNRDOC, FDTDOC, FNRRIG, FIVA, FCDAG,        
MOD(FNRDOC, 10000) as CDBILL from ddsf002          
ORDER BY FCDAG, CDBILL, FDTDOC                    

THE COMPILATOR SAYS THAT ORDER BY IS NOT VALID.

In DDS Logical File, you can declare Keys (K) so,
in RPGLE program you can chain these records with
the right key.
0
Kent OlsenData Warehouse Architect / DBACommented:
Ah. Ok...

DB2 doesn't propogate the alias CDBILL to the subquery's filter. You'll have to substitute the MOD function.

Create View DDSF0023 as
select FNRDOC, FDTDOC, FNRRIG, FIVA, FCDAG,
MOD(FNRDOC, 10000) as CDBILL
from ddsf002
ORDER BY FCDAG, MOD(FNRDOC, 10000), FDTDOC

The alias WILL propogate out to an outer query, but that usually adds another step to the query and reduces performance.


Kent
0
MurpheyApplication ConsultantCommented:
You can't solve this with a view if you like to use key-fields.

But there are some other options:
1. Use RPG with imbedded SQL so that the program select the record you like to have (probably very slow)
2. Read the view in the program and chain to the table you designed to read first. (Not allways possible).
3. Build a table in DDS with a key and fill this with the information you have in your select statement now
and fill it as temporary just before you execute your program.
You can do that with a Query Menagement Query.
If needed I can give you the needed DDS inf and the QMQ source you need.

Regards,
Murph
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
bobdylan75Author Commented:
I made so. thanks.
(option 3) because I transformed a key field before chain it.
0
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.