SQL Server - limited functions available

[spligosh]

I am accessing a SQL Server database and when I try to add a calculated column it only offers the following list of functions:

AVG, BETWEEN, CONVERT, COUNT, COUNT(DISTINCT), LEN, LOWER, LTRIM, MAX, MIN, RTRIM, STDEV, SUBSTRING, SUM, UPPER

This doesn’t seem many and doesn’t include any date functions - am I missing something?

[Joshua.Liong]

To spligosh:

Thank you for posting in Toad World!

In the Editor, in Toad Data Point 3.3 and later, one can select aggregate functions from the menu that appears as you type. This is known as Code completion.
It appears a finite amount of aggregate functions are appearing on your menu. This could occur when the "Include Rarely Used Items" option is disabled.

To Enable "Rarely Used Items"

1. Open the editor (Tools -> Editor)
2. Type out a query. As you type it out, the Code Completion menu should appear.
3. Select the "Include Rarely Used Items" button at the bottom.

The Code Completion window should refresh all functionality should appear.

Alternatively, you can enable "Rarely Used Items" in Options:

1. In the Toad Data Point Main Window, navigate to "Tools -> Options"
2. In the left-hand navigation tree, expand "Editor" (the third folder)
3. Select "Code Completion"
4. On the right-hand side, check the box "Include Rarely Used Items."

Let me know if this resolved your issue. If it has not, let me know of the version of Toad that you are currently using and the reproduction steps.

Regards,

-Joshua Liong

[spligosh]

Thanks Joshua but it appears that my ‘include rarely used items’ is already ticked?

[Joshua.Liong]

To spligosh:

I apologize that I have been unable to reproduce this scenario. Basic SQL Server aggregate functions should appear in Code Completion. One should be able to utilize Code Completion like so:

Alternative: Code Snippets
Code snippets are similar to code completion and may reveal the functionality that you would like to access.

1. As you type your query, push CTRL + SPACE. This should reveal the Code Snippets interface for your platform.
   
2. Use the Arrow Keys to navigate and the ENTER Key to make a selection. You can also use your mouse.
   
   
   
   I do have a couple of questions. This information may provide insight on the lack of availability on the functions through code completion:

• What is the version of SQL Server you are utilizing?
• Do the functions you desire appear through Code Snippets?
• What version of Toad Data Point are you using?
• Can you post a screenshot of your Code Completion in action with the missing functions?
  Thank you for your time,

-Joshua Liong

[Debbie_Peabody]

Are you using the SQL Serer Native connection or connection to SQL Server through ODBC? You need to use the native connection to get the features Joshua is talking about.

[spligosh]

I am using the native connection - not an ODBC one.

[spligosh]

Thanks for all the feedback so far. Just to confirm that I can see all the functions using code completion - but I can’t see them when I use the calculated field option in the Query Builder - is this expected behaviour?

[Debbie_Peabody]

Yes, we only put some of the most common functions there. Are there some that you think should be added? I would consider adding if they seemed to be used alot.

[brinehart]

SYSDATETIME() is a common function in SQL Server. I recently used a calculated column in a sub-query where I needed to compare a date/time in a table with the current date/time. I created a calculated column as SYSDATETIME() and then used MyColumn <= MyCalcColumn in the where clause.

I am not aware if there are any generic date and time formulas that would work for this and then Toad would convert to the DB specific function. So I used the SQL Server specific function.