SAP SQL Anywhere

Question

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

Answer 1

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.

Answer 2

5	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 Matt 3.3k●56●72●101 accept rate: 15% did not know that! (30 Nov '10, 00:24) Calvin Allen flat view

Answer 3

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.

Answer 4

4

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 Matt
3.3k●56●72●101
accept rate: 15%

edited 29 Nov '10, 16:53

Volker Barth
39.5k●355●539●811

flat view

Answer 5

4

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"()
begin
  for names as curs dynamic scroll cursor for
    select 'CREATE STATISTICS ' || table_name || ';' as A 
       from SYSTAB 
      where creator = 1 
        and table_type = 1 
  do
    execute immediate A 
  end for;
end;

permanent link

answered 29 Nov '10, 22:41

carolstone
341●3●8●17
accept rate: 22%

2

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

1

@Siger: Sounds like SQL injection! http://xkcd.com/327/ :)

(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

flat view

Answer 6

3

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 Pods
4.5k●38●48●91
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

flat view

Answer 7

3

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 Barth
39.5k●355●539●811
accept rate: 34%

1

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

flat view

Answer 8

1

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
377●15●15●21
accept rate: 0%

You meant Input Using...

(22 Dec '10, 10:44) Martin

That's the one. Thanks.

(30 Dec '10, 18:17) zippidydo

flat view

SAP SQL Anywhere

What's the SQL Anywhere feature you wish you had got to know much earlier than you did?

Follow this question

Related questions