Create view and order by

[CENTRINO]

How can I create a view ordered by ?

I need to create a "union" view using SQL so that RPG programs could see
table A and table B as the "sum" of the two tables that have the same
structure and field names, but I want RPG programs use the view being able
to "see" records/rows ordered in a particular way.

It seems that "Order by" cannot be used creating views in DB2 !! or I am
very wrong.

I want something like:

CREATE VIEW NEWVIEW AS
SELECT * FROM TABLE A
ORDER BY FIELD1
UNION ALL
SELECT * FROM TABLE B
ORDER BY FIELD1


Thanks in advance.

[Dr.UgoGagliardelli]

You cannot put "order by" clause in a view, you're right. RPG programs
will be able to select from the view using order by clause.
If you mean non-sql rpg, so you should create the view using DDS.

[CRPence]

On 02-Apr-2014 15:29 -0700, CENTRINO wrote:
> How can I create a view ordered by ?
>
> I need to create a "union" view using SQL so that RPG programs could
> see table A and table B as the "sum" of the two tables that have the

> same structure and field names, but I want RPG programs to use the

> view being able to "see" records/rows ordered in a particular way.
>
> It seems that "Order by" cannot be used creating views in DB2 !! or I
> am very wrong.

A VIEW can not include an ORDER BY for the final result. An SQL RPG
program can issue a "SELECT ... FROM the_view ... ORDER BY ..." to
obtain the ordered result set. The RPG program could consume an ordered
result set from a stored procedure.

If those "RPG programs" need to perform non-SQL, Row Level Access
(RLA), then use CRTLF with DDS to effect the UNION of the data from two
file.mbr named in the PFILE().

There is alternatively the option to use a Classic Query Engine (CQE)
query Open Data Path (ODP), created by the Open Query File (OPNQRYF)
command. The VIEW must not have any restrictions preventing CQE from
processing the VIEW. The Key Field (KEYFLD) parameter and Sort Sequence
(SRTSEQ) parameter, with appropriate specifications, can allow for
presentation of the result set of the SQL VIEW as an ordered set,
according to the requirement of the compiled non-SQL RPG program; i.e.
the query properly reflects the file and format referenced by the
compile, e.g. per the Record Format (RCDFMT) parameter specification of
the OPNQRYF request having named the same file referenced for the compile.

> I want something like:
>
> CREATE VIEW NEWVIEW AS
> SELECT * FROM TABLE A
> ORDER BY FIELD1
> UNION ALL
> SELECT * FROM TABLE B
> ORDER BY FIELD1
>

FWiW, with regard to the above pseudo-coded CREATE [i.e. proposed,
but invalid\disallowed], the last ORDER BY syntactically would be the
collation request for the overall result set, and the prior ORDER BY
would be specific to that subquery; i.e. written parenthetically, as a
valid query because the CREATE is omitted, the effect would necessarily be:

( SELECT * FROM TABLE A
ORDER BY FIELD1 )
UNION ALL
( SELECT * FROM TABLE B )
ORDER BY FIELD1

The reason is that without parentheses to clarify the query, the
intention of the final ORDER BY would need to be inferred.

--
Regards, Chuck

[CENTRINO]

Is it a limitation of DB2 for System I or whatever is called now ?

I understand that IBM is the creator of relational algebrae and SQL ... but
"views kapable of being ordered by" make life easier specially for data
warehousing.

Productivity goal is what I mean.


Thanks for your unswers.



"CRPence" escribió en el mensaje de
noticias:lhk2tu$o5v$1...@speranza.aioe.org...

[CRPence]

On 07-Apr-2014 18:36 -0500, CENTRINO wrote:

>>> <<SNIP>>
>>
>> <<SNIP>> <ed: reinterpreted\re-represented as this faux CREATE:>
>>
>> CREATE VIEW NEWVIEW AS
>> (
>> ( SELECT * FROM TABLE A )

>> UNION ALL
>> ( SELECT * FROM TABLE B )
>> ORDER BY FIELD1

>> )
>>
>> <<SNIP>>

>
> Is it a limitation of DB2 for System I or whatever is called now ?

The concept of an ORDER BY in a VIEW is incongruous to the SQL. The
SQL _query_ defines the collation for a result set. The SQL provides no
conceptual means to "open" and read a VIEW [similar to the Row Level
Access (RLA) or ISAM Indexed\Sequential Access Method], so the order of
the rows is undefined except to the query that references the VIEW as a
table-reference. Instead, the SQL provides a query _result set_ either
via a SQL cursor or a result-table. Intuitively, for a JOIN [or a
UNION, INTERSECT, etc.] defining a SELECT to generate a result-set from
rows requested from multiple VIEWs, any implied ORDER BY in any VIEW
referenced by that query, is meaningless to the query engine.

The DB2 for i, or the DB2 for i SQL, have long been the proper
nomenclature. The OS is the IBM i [for business] although some
documentation may still refer to the i5/OS; e.g.:

<http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/dbp/rbafodb2.htm>
IBM i 7.1 Information Center -> Database -> Programming -> Database
programming -> Database file concepts
_DB2 for i_
"DB2® for i is the integrated relational database manager on the i5/OS®
operating system.

DB2 for i is part of the i5/OS operating system. ...
..."

> I understand that IBM is the creator of relational algebrae and SQL

> ... but "views capable of being ordered by" make life easier

> specially for data warehousing.
>
> Productivity goal is what I mean.

I am not clear how an ordered VIEW can make life any easier for the
SQL.? Per mention of Data Warehouse (DW), have you investigated the
Materialized Query Table (MQT) support for providing the [ordered]
result; i.e. a TABLE as the result-set?

However for use by the /native/ database, the non-SQL integrated
database [the DB2 for i; specifically not the DB2 for i SQL], for which
there is file /open/ support, an ordered result-set provided directly
from a VIEW does make sense. But as noted, the SQL which implements the
VIEW has no such support, so a database feature such as the Open Query
File (OPNQRYF) could manipulate the result-set from the VIEW as an
ordered set... thus accomplishing the desired goal, even while using the
SQL. Or the purely /native/ database file support, the DDS Logical File
(LF) could define the logical view of the data with the capability to
include a key such that indexed\ordered reads are possible.

If using purely SQL then the productivity should be typical of any
SQL development to accomplish the overall goal. And if using some SQL
for Data Definition Language (DDL) but using native\RLA to open files,
adding a query Open Data Path (ODP) is nothing out of the ordinary for
typical coding on the system since the S/38, nor is using the Data
Definition Specifications (DDS) to create the keyed Logical File (LF) to
access the physical data in the desired order.

> Thanks for your answers.

Most welcome... Hopefully they assist [both to better understand the
OS and the integrated database, and to accomplish what is required].

--
Regards, Chuck