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

[Breck_Carter]

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'
*/

[johnsmirnios]

Oh, I never said "you can't" -- I only gave as close an answer as I could come up with. I'm no SQL guru by far.

[VolkerBarth]

@John: SQLA 2 suggestion: A badge "Consider me a SQL guru" that anyone can apply to his profile - possible with further flavours "Code guru", "Performance guru" and so on...

[VolkerBarth]

@John: But honestly, personally, I would expect each and every experienced iAnywhere staff member to be a SQL guru. It may not be reasonable, but, man, that's the admiration me (and others, I guess) are willing to show for your great development team:)

[johnsmirnios]

@Volker: I work on the low level nuts and bolts of the server: tasking models, caching, IO, recovery, internationalization, indexing, validation, backup, that sort of stuff. You can think of it as the "operating system" that the SQL query execution engine runs on plus some other specific tools. The SQL language itself is far removed from that and I've never really need to know the finer points of SQL. I'll even admit that I had no idea what a lateral derived table was until Glenn and Ivan explained it to me today in the context of Breck's question. 🙂

[VolkerBarth]

@John: Well, I guess I have a rough idea what different developers in the iAnywhere teams are dealing with, and the answers you have been posted in both the newsgroups and here (as far as I can recall) naturally fit to the work-field description you've pointed out above (Something to add to your profile?). And I'm quite aware that this low level stuff is a totally different beast than SQL query execution. - That being said, I still think us customers like to treat all of you as our SQL heroes... But I see, "SQL Anywhere heroes" would more correct:)

[Breck_Carter]

@Volker: The badge should be called "We are not worrrrthy!" and should be awarded immediately to all iAnywhere folks, immediately upon first login. Or, to avoid misinterpretation, to everyone ELSE instead 🙂

[Breck_Carter]

@John: Your answer was a heck of a lot better than "invalid parameter" which was what I was getting. Bounty-worthy, in fact, except it was time to embarrass Ivan 🙂

[Former Member · Accepted solution]

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.

[VolkerBarth · Accepted solution]

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

[Breck_Carter · Accepted solution]

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 🙂

[Breck_Carter · Accepted solution]

@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 🙂

[Former Member · Accepted solution]

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).

[VolkerBarth · Accepted solution]

Thanks a lot:)

[johnsmirnios]

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.

[Breck_Carter]

Thanks! ...I can always use another blog topic, and there's NOTHING like "Undocumented" in a title to get people's attention... well, almost nothing 🙂

[johnsmirnios]

GRANT CONNECT TO ... IDENTIFIED BY ENCRYPTED ... is also undocumented but you have probably seen it countless times in reload scripts. The form of LOAD TABLE described above was created to solve a very specific problem and I don't think it was expected to be of general use to users.