Re: RUNSQLSTM Source File Record Length

[CRPence <crp@xxxxxxxxxxxxxxxxxxxx>]

  A recursive query could break the lines.  The following breaks lines 
on each semicolon for example [with assumed/implicit vs explicit varchar 
casting]:

 with
  manyline ( nextsplit ,thisline ,nextline ) as
 ( select cast( locate(';' ,srcdta  ) + 1 as int )     as nextsplit
         ,substr( srcdta  , 1 ,locate(';', srcdta  ) ) as thisline
         ,substr( srcdta  ,locate(';', srcdta  ) + 1 ) as nextline
    from        oneline
  union all
   select cast( locate(';', nextline) + 1
                + nextsplit               as int )     as nextsplit
         ,substr( nextline, 1 ,locate(';', nextline) ) as thisline
         ,substr( nextline,locate(';', nextline) + 1 ) as nextline
    from        manyline
   where nextline  <> ''
 )
 select thisline from manyline
  order by nextsplit

Regards, Chuck
-- All comments provided "as is" with no warranties of any kind 
whatsoever and may not represent positions, strategies, nor views of my 
employer

Mike Krebs wrote:
> Interesting question overall...
>
> There is support writing multiple records at one time in db2 SQL. If you
> just had a string, you could do something like this:
>
> INSERT INTO QTEMP/MAK (TEST1) VALUES  
> (substr('1234567890abcdefghij',1,10)),       
> (substr('1234567890abcdefghij',11,10))        
>
> But, I can't figure out how to use a select statement to drive multiple
> values.
>
> Googling finds several references to folks that have tried creating multiple
> records from a single record (for example, separating a comma delimited list
> into separate records). They all look quite "messy". Creating a function or
> some kind of temporary table might be an answer. There seem to a couple of
> "split" functions that folks have created that would have potential.
>
> If you don't get an answer here, try Howard Arner or Ted Holt at
> itjungle.com. They both seem to relish a SQL challenge.
>
> Mike