[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

As400 sql: How to create a view with keys?

Posted on 2010-04-08
9
Medium Priority
?
3,669 Views
Last Modified: 2013-12-06
Hi WatchMen,
As400 sql: How to create a view with keys?
So I can use in ROG program a CHAIN instruction?
Thanks
0
Comment
Question by:bobdylan75
  • 5
  • 3
9 Comments
 

Author Comment

by:bobdylan75
ID: 30113759
Sorry:
RPG Program (and not ROG)
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 30114164
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
 

Author Comment

by:bobdylan75
ID: 30114464
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

Author Comment

by:bobdylan75
ID: 30114486
Something like ORDER BY???
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 30114713
Hi Bob,

I'm not sure what you mean by, "view with keys".
0
 

Author Comment

by:bobdylan75
ID: 30115096
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
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 30115601
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
 
LVL 17

Accepted Solution

by:
Murphey earned 2000 total points
ID: 30199541
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
 

Author Closing Comment

by:bobdylan75
ID: 31712294
I made so. thanks.
(option 3) because I transformed a key field before chain it.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows 7 does not have the best desktop search built in. This is something Windows 7 users have struggled with. You type something in, and your search results don’t always match what you are looking for, or it doesn’t actually work at all. There ar…
Windows 10 is here and for most admins this means frustration and challenges getting that first working Windows 10 image. As in my previous sysprep articles, I've put together a simple help guide to get you through this process. The aim is to achiev…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Suggested Courses

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question