My coworker Darren came up with something.
The other day I tried to understand his lateral example but couldn't quite
grasp it. Now I think it works like this:
You calculate a column on the fly and 'toss a lateral' which appends this
calculated column on the end of your currently selected row.
The beauty of this that you don't have to keep recalculating the position
of the first comma when getting to each subsequent comma.

This is working quite nicely:
SELECT
cast(substr(hmcoutput,1,c1-1) as char(10)) as LparName,
cast(substr(hmcoutput,c1+1,c2-c1-1) as dec(3,0)) as LparNumber,
cast(substr(hmcoutput,c2+1) as char(10)) as LparStatus
from rack2hst.hmcoutput t
, lateral(values locate(',',t.hmcoutput)) L1 (c1)
, lateral(values locate(',',t.hmcoutput,L1.c1+1)) L2 (c2)

May be slightly confusing because it's an internally defined table in
which the column name, record format and table name are all the same.

'toss a lateral' Yep, it's only 3.5 hours until gates open for the first
Friday Night Lights of the season. "Only an intersquad scrimmage!?" Don't
say that to the almost full stands desperate for some local HS football
action.


Rob Berendt

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].