Please be aware that the SAP SQL Anywhere Forum will be shut down on August 29th, 2024 when all it's content will be migrated to the SAP Community.

Prolog: I admit, that's obviously no question to solve a real problem but to satisfy my curiosity. Feel free to take it as some kind of gossip:)

Often enough, someone in the newsgroups or on this site yells - or sighs - or shakes his head: "Ah, why on earth wasn't I aware of this?!" just after getting a hint to use a particular feature (say, derived tables, the MERGE statement or whatever).

Or in a similar way, one has read about this new feature (possibly in a What's new section of the docs) but it takes months/years until one comes to use it the first time - and then comes the conclusion it could have made several tasks much easier in the past.

Or from another point of view, say, when you're working with different SA versions, what are the typical features you are about to use and then realize (for the nth time) that they aren't supported in the older version?

If this sounds familiar to you, I think you could share these moments of enlightenment and help others to get to know those facilities, too, in case we haven't yet noticed their usage.

Epilog: This may be connected to Breck's current "Pushing" series on his blog where he tells of such features...

asked 28 Nov '10, 19:31

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

edited 29 Nov '10, 08:18

One of my favourites (well, one I have been aware of since ASA 9 was released but one I'm regularly missing while working with ASA 8):

SELECT ... FROM procedure();

From the ASA 9 What's new docs:

SELECT statements can operate on stored procedure result sets

In SELECT statements, a stored procedure call can now appear anywhere a base table or view is allowed.

It's very convenient to be able to join procedure result sets with other tables/views or to filter out rows/columns or use a different sort order.

In older versions, you would have to use some logic to insert the procedure's result set into a temporary table or the like and then use that table for further processing - cf. this question.

permanent link

answered 29 Nov '10, 12:25

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

Another one:

  • In ISQL you can highlight a block and use Ctrl + / or Ctrl + - to comment or uncomment the block.
permanent link

answered 29 Nov '10, 21:57

Siger%20Matt's gravatar image

Siger Matt
accept rate: 15%

did not know that!

(30 Nov '10, 00:24) Calvin Allen

Yet another one:

  • In Sybase Central you can right-click on just about any object and click copy, and when you paste you have a formed create statement ready to run.
  • You can highlight multiple rows of data and right click or use the Edit menu option (often my right-click takes the focus from the multiple selection to a single selection) and click Generate to put the necessary insert statements on your clipboard.

Between these two I can quickly generate the SQL to take structure and data from one setup to another.

permanent link

answered 07 Dec '10, 21:50

Siger%20Matt's gravatar image

Siger Matt
accept rate: 15%

  • Create or Replace syntax saves me from having to use alter and fail sometimes or create and fail sometimes (V11.0.1)

  • Combining Declare variable with initializing the variable's value (V12)

permanent link

answered 29 Nov '10, 15:27

Siger%20Matt's gravatar image

Siger Matt
accept rate: 15%

edited 29 Nov '10, 16:53

Volker%20Barth's gravatar image

Volker Barth

execute immediate

The below example isn't something I run very often. I originally created it so we could "do something" when users complained their databases suddenly slowed down. Instead I keep it around as a template to use with execute immediate.

CREATE PROCEDURE "DBA"."usp_optimize"()
  for names as curs dynamic scroll cursor for
    select 'CREATE STATISTICS ' || table_name || ';' as A 
       from SYSTAB 
      where creator = 1 
        and table_type = 1 
    execute immediate A 
  end for;
permanent link

answered 29 Nov '10, 22:41

carolstone's gravatar image

accept rate: 22%


We often use this type of setup. We setup some SQL within a text variable and then "fill in the blanks" with dynamic information from another variable.
There are some gotchas, such as avoiding the "result set in batch" type of error if things get too nested, and some surprises, such as once I received the result of a procedure as a correctly formed SQL statement, and that statement was executed upon being returned.

(30 Nov '10, 14:42) Siger Matt

@Siger: Sounds like SQL injection! :)

(01 Dec '10, 21:03) Breck Carter

@Breck: One of my favourites, too, w.r.t. xkcd:)

(02 Dec '10, 08:25) Volker Barth

Yes! Use heavily with rules based student billing.

(11 Feb '21, 09:40) rsnyder

One of my favourite features now is the Import Wizard in ISQL (menu entry: Data->Import). I must admit I didn't discover it until this year with SQLA 11, only to learn it has been there since 9.0.1.
But still I'm unable to locate a chapter that describes the import and export wizard in the manual. That might be one of the reasons I stumbled over it rather lately.

permanent link

answered 29 Nov '10, 18:18

Reimer%20Pods's gravatar image

Reimer Pods
accept rate: 11%

Me, too - and v12 has even the option to skip the usual column descrption in the first line of typical CSV/text files that will hinder v11 from importing the column in the adequate format (say, date or numbers).

(30 Nov '10, 08:49) Volker Barth

Another secret favourite: MESSAGE ... DEBUG ONLY and the debug_messages option (both introduced in ASA 9.0.1).

Used for a bunch of complicated stored procedures with a lot of diagnostic messages. In older versions, I usually insert such messages during development and remove them (or comment them out) once the code works as expected - but that means one has to alter the code again.

With DEBUG ONLY, I usually leave them inside and can activate them on demand. (Sometimes, one wishes one could differ in finer degrees, i.e.not just DEBUG Yes/no, but that's a different issue).

That being said, my heavy usage of MESSAGE statements testifies that I had not yet explored the full debugging facilities within Sybase Central until lately... a further "I wish I had knew you before" moment:)

permanent link

answered 01 Dec '10, 08:51

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%


One of my favorites too... it is often much faster to find a problem using MESSAGE than with [cough] "more powerful" techniques. It is a challenge, however, when you have many services and events because each one must turn DEBUG_MESSAGES on or off.

(01 Dec '10, 21:01) Breck Carter

@Breck: "each one must..." - are you sure? I've always thought that option (as a database option) just must be toggled on/off once?

(02 Dec '10, 08:24) Volker Barth

One I recently ran across was the INSERT USING from a dsn (system or user). Great when working with two copies of one database on your local system and need to compare. It's so great I wish I could come up with other uses!

permanent link

answered 21 Dec '10, 20:43

zippidydo's gravatar image

accept rate: 0%

You meant Input Using...

(22 Dec '10, 10:44) Martin

That's the one. Thanks.

(30 Dec '10, 18:17) zippidydo
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 28 Nov '10, 19:31

question was seen: 6,466 times

last updated: 11 Feb '21, 09:40