Re: Using a temp normalized table in SQL view...

Yes, you can use a CTE in a view...

If you're running 7.3 (7.4?) see if you have the systools.split() table
function...

If on an older version, you can always build your own. (In Fact the IBM
provided version include a caveat recommendation to build a custom version
that more closely suits your needs)

Charles

On Mon, Nov 23, 2020 at 1:51 PM Thomas Garvey <tgarvey@xxxxxxxxxx> wrote:

I have a stored SQL procedure that normalizes data elements from one
file into a temporary table.
For example, non-normalized data looks like this...

Order Number Shipment Numbers
1 A B C D E
2 A B
3 A B C
4
5 Z Y X

Would result in normalized data, in the new table in QTEMP.

Order Number Shipment Number
1 A
1 B
1 C
1 D
1 E
2 A
2 B
3 A
3 B
3 C
5 Z
5 Y
5 X

Now comes the problem. I want to include this normalized data from QTEMP
into an SQL view.
I get SQL7008 FileName in QTEMP not valid for operation
My read of the supporting message description is that you can't use a
table in QTEMP as part of a view.

Now I'm wondering if I can use the WITH SQL statement (using a CTE) in
the view definition. I've never used CTE but my understanding of it is
that the WITH AS is basically informing SQL of a table that will be used
in the following compound SQL statement is defined as the WITH AS
segment defines it. If that is right, then how/where does my stored
procedure come in that actually builds that QTEMP file?

You're probably thinking, why not just create the normalized table as a
permanent table in a standard library? The problem there is that the
file of non-normalized data can be updated at any time, necessitating a
rebuild of the normalized data table. If that normalized table is in use
by another user, it would be unable to be rebuilt. At best, it would be
inaccurate.

Any ideas/thoughts?

--
Warm Regards,

Thomas Garvey

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com