SAP SQL Anywhere

Question

Update: The comments and suggestions in this Q&A conversation led directly to a blog post about OPENSTRING and CROSS APPLY.

This was originally a "How do I ... ?" question until John Smirnios provided a gentle "You can't" answer, so now it is a "Product Suggestion".

Here is the text of the original question...

Here is a working FOR loop that invokes OPENSTRING to repeatedly analyze a tab-delimited string stored in the column raw_text.line_text, in 12.0.0.2589.

Is there a way to eliminate the FOR loop and use a single set-oriented INSERT statement that invokes OPENSTRING ( VALUE raw_text.line_text ) instead of using a local string variable OPENSTRING ( VALUE @line_text )?

If LATERAL is the answer, the syntax escapes me :)

CREATE TABLE raw_text (
   line_number BIGINT NOT NULL 
                  DEFAULT AUTOINCREMENT
                  PRIMARY KEY CLUSTERED,
   line_text   LONG VARCHAR NOT NULL DEFAULT '' );

/* sample input...
line_number,line_text
128,Play - not available now        WAAV    Mon-Fri 10am    2 hours Leland  NC  980 AM      
*/

CREATE TABLE radio_programs (
   line_number BIGINT NOT NULL PRIMARY KEY CLUSTERED,
   station     VARCHAR ( 100 ) NULL,
   dates       VARCHAR ( 100 ) NULL,
   times       VARCHAR ( 100 ) NULL,
   duration    VARCHAR ( 100 ) NULL,
   city        VARCHAR ( 100 ) NULL,
   state       VARCHAR ( 100 ) NULL,
   frequency   VARCHAR ( 100 ) NULL );

FOR f_fetch AS c_fetch INSENSITIVE CURSOR FOR
SELECT raw_text.line_number AS @line_number,
       raw_text.line_text   AS @line_text   
  FROM raw_text
 ORDER BY raw_text.line_number
FOR READ ONLY
DO

INSERT radio_programs
   SELECT @line_number,
          programs.*
     FROM OPENSTRING ( VALUE @line_text )
             WITH ( TABLE radio_programs (
                       filler(),
                       filler(),
                       station,
                       dates,
                       times,
                       duration,
                       city,
                       state,
                       frequency,
                       filler() ) )
             OPTION ( DELIMITED BY '    ' ) AS programs;

END FOR;

/* sample output...
line_number,station,dates,times,duration,city,state,frequency
128,'WAAV','Mon-Fri','10am','2 hours','Leland','NC','980 AM'
*/

Accepted Answer

5

You should be able to combine the statements using something like the following:

begin
    declare local temporary table T_strs( str long varchar );
    insert into T_strs values ('1,a1\n2,a2'), ('3,b1\n4,b2');

select  *
    from    T_strs T cross apply openstring( value T.str ) 
                                 with( a char(10) ) as DT
end

Unfortunately, there is a bug^H^H^H unintended limitation that prevents the above from working correctly: instead, the server returns an error "invalid parameter".

There is a work-around for the limitation, you can use T.str || '' in the value clause of OPENSTRING. The requirement is that the value expression must not be a simple expression, nor can it be used elsewhere in the query. With this workaround, you can use something based on the following (here I show the LATERAL syntax in case you prefer it). I tend to prefer CROSS APPLY myself but LATERAL is from the ANSI standard.

select  *
from    T_strs T, lateral( openstring( value T.str||'' ) 
                           with( a char(10) ) as DT1 ) DT2

Look forward to this limitation being corrected in a future version of the server.

permanent link

answered 17 Nov '10, 16:58

Ivan T. Bowman
2.8k●2●27●32
accept rate: 39%

@Ivan: Does "being corrected in a future version of the server" refer to an EBF or a coming maintenance release or even main version?

(18 Nov '10, 08:12) Volker Barth

1

Ha ha ha ha ha ha [end maniacal laughing sound]... I always assume messages like "invalid parameter" are computer for "you are out of your depth"... occasionally, apparently, that assumption is incorrect :)

(18 Nov '10, 09:32) Breck Carter

@Volker: My guess is "EBF" but since there is a perfectly easy workaround, maybe they won't hold up the next EBF for this... or, heaven forfend, hold up the release of 12.0.1... I say "perfectly easy" before trying it, but after all, it's Ivan speaking :)

(18 Nov '10, 09:36) Breck Carter

Comment Text Removed

1

In line with Volker's polite request, I am tardily adding some bug fix information. The issue that Breck reported is fixed by CR648799. The fix appears in 12.0.1 GA, 12.0.0(3268), and 11.0.1(2541).

(02 Apr '11, 14:38) Ivan T. Bowman

Replies hidden

Thanks a lot:)

(02 Apr '11, 14:57) Volker Barth

flat view

Answer 2

I think I can give you an almost but not quite approach that may be of use to you. There is a form of LOAD TABLE that is, strictly speaking, undocumented though you will see it if you translate a log that contains a LOAD TABLE ... WITH CONTENT LOGGING. It looks like the following:

LOAD TABLE table_name USING COLUMN string_column FROM input_table ORDER BY column_list

column_list must be a verifiably unique set of values (ie, there is a primary key or unique index on non-nullable columns included within column_list) and string_column must be a string type (char, nchar, or binary).

Effectively, the values of string_column are concatenated to produce the data stream that will be processed by LOAD TABLE. In your case, your input doesn't appear to have row delimiters at the end of raw_text.line_text; however, if it did you could possibly get away with the syntax above. If there is an ENCODING clause and string_column is CHAR or NCHAR, the encoding must match the encoding of string_column. If string_column is BINARY, any encoding is permitted.

As I said, you won't find it documented in the LOAD TABLE docs; however, since our tools generate that syntax it's not exactly a secret. I'll add a standard disclaimer that undocumented behaviour is subject to change without notice though.

-john.

SAP SQL Anywhere

Allow FROM table-name ... OPENSTRING ( VALUE table-name.column-name )

Follow this question

Related questions