SQL Assistant questions

Hello,
I am running a trial of SQL Assistant 6.3.. I should have started it Tuesday when I was back at work to get a full 14 days out of it, but I wanted to check it out tonight.

I had a couple of questions:

Is there a way to enable matching the single apostrophe? SQL Prompt matches this, so When I type Where ColName = ' it matches and adds the second apostrophe and puts my cursor between them. None of the other SQL addons I've found do this. Same goes for /* it adds a matching */ with the cursor between.

Second: Is there a way to enable using the word "AS" before Synonyms? I like the readability of it more, but when SQL Assistant auto generates table synonyms it doesn't add "AS"

third: how do you use the CRUD generator? Every time I try to do a "generate bulk code" on a table I get some sort of error, or it completes but never goes to an editor window (I'm clicking generate, not save to file).

Fourth: is there a way to tell the join logic to reverse the ON references? So instead of tbl1.a = tbl2.a it can read tbl2.a = tbl1.a? Devart SQL complete has an option for this

Fifth: in the table/ column picker can it be set to allow tab to chose the highlighted table/column versus enter and space bar used to check a column for inclusion or removal?

Lastly, is there a way to tell SQL Assistant NOT to add the comment header to formatted code?

And I also use database.net (fishcodelib.com) is it possible to use SQL assistant with this some how? I mean it works in notepad!!

Right now I'm using this in SSMS, but I also spend time in Notepad++ and Access.. if this works on all 3 like you claim it does this will be much more valuable to me than say SQL Prompt 5 or Devart SQL Complete.

THanks for your time!

Also is there a way to add a space between column names and tailing commas in the formatter?

Eg: select x ,
Y ,
Z
From

1. Use code snippets to auto-match quotes.
See existing snippets and docs to leans how to do that.

2. Yes, choose the option in auto-complete section to use AS alias

3. Please let us know what kind of error you get, need the compete error message, type and version of your db server

4. No. We font support this style.

5. Yes. Please see Options

6. Yes. It does support all 3 of them.

7. You you can achieve that using code formatting patterns.

More questions:

Is there any way to expand a wildcard on tab?

Say..

	Code:
	SELECT * from FOO where bar='y'

Come back around and put the cursor after the star and hit tab. Other similar products (devart complete and sql prompt) will expand the wildcard into every column in the table.

Also.

I'm having problems with CTE formatting:

	Code:
	WITH CTETBL as (SELECT * FROM FOO WHERE BAR='Y')

If I hit Ctrl+F11 on that it breaks the parens apart on different lines, but pushes the select statement way out to the right, press ctrl+f11 again and it pushes the select further out to the right... formatting doesn't reset the position for CTE it keeps moving it further out.

I see there is no code formatting logic for "WITH".. I tried to add it but doing this over VPN to my workstation at work isn't the greatest today.

Last edited by Mindflux on Sun May 26, 2013 12:20 pm; edited 2 times in total

SysOp wrote:

1. Use code snippets to auto-match quotes.
See existing snippets and docs to leans how to do that.

2. Yes, choose the option in auto-complete section to use AS alias

3. Please let us know what kind of error you get, need the compete error message, type and version of your db server

4. No. We font support this style.

5. Yes. Please see Options

6. Yes. It does support all 3 of them.

7. You you can achieve that using code formatting patterns.

Thank you, I've been working through most of these... I am disappointed about #4. Is there possibility that that enhancement can be added later? As an option? "Reverse ON criteria for join?"

I'm really liking your product above and beyond the competition. It's really robust! Bravo.

Ok. Addendum to the CTE formatting issue.

If your cursor is after the last parens in the CTE and you continue to mash Ctrl+F11 it keeps pushing the CTE out further to the right.

If you don't do that, it's ok.. HOWEVER:

If my cursor is at Char 1 and I press Ctrl+F11 the CTE keeps pretty close to left justified... but if I'm at Char 16 and press Ctrl+F11 the CTE pushes itself out 16 characters, and so on and so forth. Of course pressing Ctrl+F11 puts your cursor at Char 1 of the last line in the code window, so Pressing Ctrl+F11 puts your CTE (or perhaps anything in parens) back over on the left side.

Just a little quirky?

It's becoming difficult to respond to different questions when everything is mashed together. Please use different topics for unrelated questions or issues.

Yes, wildcard * can be easily expanded. Please don't get me wrong, I think at this point the best course of action for you is to spend some time reading the documentation provided with the product, because you are asking very basic questions. I'm happy to help with solving issues.

By the way, thank you very much for the CTE formatting feedback. I will try to reproduce it later, and will open a ticket if it's reproducible.

Re 4: I'm not sure, I'm unable to answer this question.

Last edited by SysOp on Sun May 26, 2013 6:54 pm; edited 1 time in total

Sorry I didn't want to make 10 different threads

As far as wildcard expansion I tried tabbing after the asterisk and even highlighting the asterisk and neither worked.

I will look into the docs when I have more time.

	Mindflux wrote:
	As far as wildcard expansion I tried tabbing after the asterisk and even highlighting the asterisk and neither worked.

Putting your cursor after the asterisk will result in a popup saying 'Click here or press Ctrl + Space to choose columns for the query'. Doing so will give you a popup with a column picker where you can either pick individually or right-click and select all from the menu. A bit more complicated than simply pressing tab but you hardly ever need all the columns from a query and if there's only one table its columns will expand selecting it after the SELECT keyword anyway, no need to type 'SELECT * FROM'.

	Mindflux wrote:
	As far as wildcard expansion I tried tabbing after the asterisk and even highlighting the asterisk and neither worked.

Putting your cursor after the asterisk will result in a popup saying 'Click here or press Ctrl + Space to choose columns for the query'. Doing so will give you a popup with a column picker where you can either pick individually or right-click and select all from the menu. A bit more complicated than simply pressing tab but you hardly ever need all the columns from a query and if there's only one table its columns will expand selecting it after the SELECT keyword anyway, no need to type 'SELECT * FROM'.

Regarding the CTE, I made a formatting rule for them and its a simple line of

	Code:
	;WITH ...

Formatting you code of

	Code:
	WITH CTETBL as (SELECT * FROM FOO WHERE BAR='Y')

will result in

	Code:
	WITH CTETBL AS ( SELECT * FROM FOO WHERE BAR = 'Y' )

no matter how many times you apply formatting. But I guess this depends on other formatting rules as well.

Last edited by gemisigo on Mon May 27, 2013 9:14 am; edited 1 time in total

Great tip. Thank you. I tried "WITH ... " and works fine for me. I''m going to ask the team to add this to a list of predefined formatting rules.

So if you enter your cte and put the cursor after the last parenthesis and the format hotkey it doesn't continually jump to the right? I can make a video to show.

It doesn't jump for me. I keep pressing Ctrl+F11 and nothing jumps. I'm trying it in a empty editor and I'm using 6.4.199 build.

Which specific code are you trying to format? Is that part of a larger procedure? Can you post the complete text so I can try it too?

	SysOp wrote:
	It doesn't jump for me. I keep pressing Ctrl+F11 and nothing jumps. I'm trying it in a empty editor and I'm using 6.4.199 build. Which specific code are you trying to format? Is that part of a larger procedure? Can you post the complete text so I can try it too?

Nothing large, just was testing out some of the formatting to tweak it the way I like.

	Code:
	WITH CTETBL AS (SELECT * FROM Foo where BAR = 'y')[Press Ctrl-F11 here after the parens)

http://screencast.com/t/7y6qDni0

First, if you leave it on the last parens and hit Ctrl-F11 it keeps going to the right.

Second: If you leave your cursor mostly to the left it'll bring it back over to the left.

But then if you are way out in the middle of the editor and hit format it'll push it way out past your cursor.

Most of my formatting rules are still default, all I did was add a space to trailing commas by editing SELECT and UPDATE rules to look more like

	Code:
	SELECT ... AS ... , ... = ... FROM ... AS ... , ... JOIN ... ON ... = ... AND ... WHERE ... = ... AND ... OR ... GROUP BY ... , ... HAVING ... , ... ORDER BY ... , ...

Just added a space between ... and ,