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

[VolkerBarth]

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

[former_SQLA_member1694868]

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.

[former_SQLA_member1694868]

Another one:

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

[Former Member]

did not know that!

[VolkerBarth]

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.

[Former Member]

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;

[former_SQLA_member1694868]

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.

[Breck_Carter]

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

[VolkerBarth]

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

[former_SQLA_member1694892]

Yes! Use heavily with rules based student billing.

[former_SQLA_member1694868]

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

[VolkerBarth]

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

[Breck_Carter]

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.

[VolkerBarth]

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

[reimer_pods]

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.

[VolkerBarth]

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

[Former Member]

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!

[MCMartin]

You meant Input Using...

[Former Member]

That's the one. Thanks.