Example (using Split):
Select OrderNo, Element
from Mytable cross join Table(SysTools.Split(Shipments, ' ')) x;
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
„Train people well enough so they can leave, treat them well enough so they don't want to.“ (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Thomas Garvey
Sent: Montag, 23. November 2020 21:52
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Using a temp normalized table in SQL view...
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
midrange.com
