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' */ |
You should be able to combine the statements using something like the following:
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
Look forward to this limitation being corrected in a future version of the server. @Ivan: Does "being corrected in a future version of the server" refer to an EBF or a coming maintenance release or even main version? 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 :) @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 :) Comment Text Removed
Comment Text Removed
Comment Text Removed
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
|
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.
@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...
@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:)
@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. :)
@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:)
@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 :)
@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 :)