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

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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