SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Ehancement: Col Value Inteligence
Goto page 1, 2  Next
 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
Ehancement: Col Value Inteligence
Author Message
hitesh



Joined: 15 Oct 2007
Posts: 104

Post Ehancement: Col Value Inteligence Reply with quote
I would like to suggest to include a feature which will show column VALUE (top 100 rec) in WHERE clause for single table select statement. For example.

SELECT * FROM EMPLOYEE_MASTER
WHERE EMPLOYEE_ID = <show_values_for_col_EmpID> AND NATIONALITY = <show_values_for_col_NATIONALITY>

- when col value is selected who data type is char/varchar then prefix & suffix ' & ' e.g: 'AMERICAN'.
Sun Aug 30, 2009 12:51 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
Do you mean top 100 values for a given column or for a table? if for a table, how is that different from data preview accessible via Ctrl+Click on EMPLOYEE_MASTER?
Sun Aug 30, 2009 1:08 pm View user's profile Send private message
hitesh



Joined: 15 Oct 2007
Posts: 104

Post Reply with quote
Yes, I mean TOP 100 "DISTINCT VALUES" for a given COLUMN in drop down list bcoz the most obvious thing a user will do when writing SINGLE TABLE WHERE clause is to provide value for a given col.
Mon Aug 31, 2009 6:07 am View user's profile Send private message
hitesh



Joined: 15 Oct 2007
Posts: 104

Post Reply with quote
Hello, did you get my point explained above?
Wed Sep 02, 2009 4:18 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
Yes, I got it. Thank you.

We have a planned enhancement in the queue for executing user queries that don't insert new text into the editor. We think you could use that feature to run a dynamic query like SELECT DISTINCT .... for a given table/column. Basically, configure a custom query activated with a chosen hot key. The query text could pull the clicked table/column via new macro-parameters for referring to elements of the text in the editor.
Wed Sep 02, 2009 8:57 am View user's profile Send private message
hitesh



Joined: 15 Oct 2007
Posts: 104

Post Reply with quote
Would appreciate it if you can guide me one-by-one steps I need to follow to achieve my requirement as explained above?
Wed Sep 08, 2021 2:43 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
You can use Ctrl+click column name to preview its values, or Ctrl+click table name to preview table values. Other shortcuts are available as well.
Thu Sep 09, 2021 12:47 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
Here is a short video using the Ctrl+click method on the column to get its possible values (it is retrieving all of the possible values and is applying distinct as well).

And here is another showing the difference between Ctrl+clicking a column that behaves as described above, yielding a distinct list of all possible values, and Ctrl+clicking a table, which only gets you the first 100 records as a sample (all columns, no filtering, not any specific order) and the way to make the result pane show all of the records (which might not be the best of the ideas, depending on record count, but I did it anyway).

You can also see an example of how the feature you requested here works when showing the results in SA's result pane instead of the one SQL Server Management Studio displays the results in. You just have to select column(s) or cell(s) and you get a popup with the aggregates.
Thu Sep 09, 2021 3:27 am View user's profile Send private message
hitesh



Joined: 15 Oct 2007
Posts: 104

Post Reply with quote
gemisigo wrote:
Here is a short video using the Ctrl+click method on the column to get its possible values (it is retrieving all of the possible values and is applying distinct as well).

And here is another showing the difference between Ctrl+clicking a column that behaves as described above, yielding a distinct list of all possible values, and Ctrl+clicking a table, which only gets you the first 100 records as a sample (all columns, no filtering, not any specific order) and the way to make the result pane show all of the records (which might not be the best of the ideas, depending on record count, but I did it anyway).

You can also see an example of how the feature you requested here works when showing the results in SA's result pane instead of the one SQL Server Management Studio displays the results in. You just have to select column(s) or cell(s) and you get a popup with the aggregates.


Thank you for your prompt reply! I am completely aware of the steps you have mentioned and I am using it when I need to explore column/table value in the result pane.
My requirement is NOT that I want to explore value in the result pane separately but I want DISTINCT column values to be seen in the drop-down of the SQL assistant auto-completion list upon pressing special shortcut keys (maybe Ctrl+Alt+Space).

I tried to use macro but I am not able to get the table & column in the current context unless adding an inbuilt feature that will show value in the SQLAssitant dropdown.

I hope my idea behind my requirement is clear. If you can provide such a feature it will prove super productive to me and other SQLAssitant users.

If you are still not getting my requirements right then I can share a pictorial representation of the same.
Thu Sep 09, 2021 4:13 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
I see. Yes, it is clear now. You want that to work from a popup. I have a pending enhancement request (see here) that - if implemented - might achieve the same goal using snippets, but it would be quite a challenge to write the snippet for your use case. Getting the column would be easy, as it would be Parameter 1 for the $CURRENT...$ macro, but getting the proper table name might prove difficult.

Currently, the only way is the one I described, which isn't extremely slow but having this as a built-in feature would definitely be faster. As I'm all in for speed, efficacy, and efficiency, I support this idea. Here, take my upvote ;) I'd only ask for one thing, if it gets a hotkey, make that customizable instead of putting it on Ctrl+Alt+Space (I have that one already occupied, even overridden, multiple times).
Thu Sep 09, 2021 5:14 am View user's profile Send private message
hitesh



Joined: 15 Oct 2007
Posts: 104

Post Reply with quote
gemisigo wrote:
I see. Yes, it is clear now. You want that to work from a popup. I have a pending enhancement request (see here) that - if implemented - might achieve the same goal using snippets, but it would be quite a challenge to write the snippet for your use case. Getting the column would be easy, as it would be Parameter 1 for the $CURRENT...$ macro, but getting the proper table name might prove difficult.

Currently, the only way is the one I described, which isn't extremely slow but having this as a built-in feature would definitely be faster. As I'm all in for speed, efficacy, and efficiency, I support this idea. Here, take my upvote ;) I'd only ask for one thing, if it gets a hotkey, make that customizable instead of putting it on Ctrl+Alt+Space (I have that one already occupied, even overridden, multiple times).


Pl share the code snippet part using macro var to achieve this as you mentioned to use Parameter 1 for the $CURRENT...$ macro (but I am not getting the desired result or maybe I am doing something wrong)

I want to build a code snippet using macro variables like this "SELECT DISTINCT $CURRENT_COLUMN_IN_CONTEXT$ FROM $TABLE_IN_CONTEXT_OF_COLUMN$"

When I press Ctrl+click on the desired column, SA showing exactly what I want in the result grid but I want to see the same result in the SqlAssit popup.

Example: Imagin I have a case where there are 50 product items and I need to prepare SQL rpt where product name in (x,y,z.....) and for this to achieve I need to explore the column data first in the result grid and then one by one I need to copy-paste required product name/codes. If I can just simply get the value in the SA popup then it is simple.

The dedicated Shortcut key for this action can be anything as long as it is serving the purpose. So think about this. if you can show the result upon clicking Ctrl+click on the column then do that same for popup using a shortcut key and populate SA dropdown.
Thu Sep 09, 2021 6:07 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
There's no sense in trying to create that snippet as it won't work at this time. The only way you can make a snippet show you a list of data to choose from is the $PROMPT$ macro and those are evaluated before any other macros in the snippet.

Quote:

"SELECT DISTINCT $CURRENT_COLUMN_IN_CONTEXT$ FROM $TABLE_IN_CONTEXT_OF_COLUMN$"



This could work if snippets had the user-defined variables (combined with the $$...$$ macro) the Code Generators have. You could then define the variables $CURRENT_COLUMN_IN_CONTEXT$ and $TABLE_IN_CONTEXT_OF_COLUMN$. This is the feature/enhancement request I was referring to in my previous post. Or rather, this is another enhancement over the one I requested, as it wants to use other macros when defining the variables and that requires careful analysis to determine the exact order of macros and variable definitions. That's well beyond the scope I asked for the user-defined variables in snippets and it might not be worth straining it that way and make it tremendously complex just to be able to implement this feature via snippets. In my opinion, this feature should have its own popup, rather than trying to build it from snippets.

Also, the list of possible values would appear in a prompt dialog instead of a popup. A popup would be better because its ability to filter. On the other hand, it would lack the feedback (title, text message) the prompt dialog provides but I don't think that would be required for this use case. It might even get in the way, efficiency-wise.
Thu Sep 09, 2021 8:53 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
gemisigo wrote:
Getting the column would be easy, as it would be Parameter 1 for the $CURRENT...$ macro, but getting the proper table name might prove difficult.


My 2 cents, I totally agree with you. That wouldn't be possible. The parameter values are returned based on their positions, up to a certain number of words to the left of the edit cursor. That doesn't help when positions vary and are generally unknown. A table name might be 2 or 3 words to the left of the cursor, or might be 20 words to the left of the parsed text segment. And even if it's in the scope, it would be still difficult to recognize it among other words without full context, especially if it's not the only table referenced in the text.


My experience is showing that using Ctrl+click table name is fine for most use cases when one needs to see a quick/instant sample of the data. If a table has many columns, there is also Find Column feature in the result grid, which makes it easy to jump to a column of interest without straining eyes while trying to locate it in the result.
Thu Sep 09, 2021 9:29 am View user's profile Send private message
hitesh



Joined: 15 Oct 2007
Posts: 104

Post Reply with quote
SysOp wrote:
gemisigo wrote:
Getting the column would be easy, as it would be Parameter 1 for the $CURRENT...$ macro, but getting the proper table name might prove difficult.


My 2 cents, I totally agree with you. That wouldn't be possible. The parameter values are returned based on their positions, up to a certain number of words to the left of the edit cursor. That doesn't help when positions vary and are generally unknown. A table name might be 2 or 3 words to the left of the cursor, or might be 20 words to the left of the parsed text segment. And even if it's in the scope, it would be still difficult to recognize it among other words without full context, especially if it's not the only table referenced in the text.


My experience is showing that using Ctrl+click table name is fine for most use cases when one needs to see a quick/instant sample of the data. If a table has many columns, there is also Find Column feature in the result grid, which makes it easy to jump to a column of interest without straining eyes while trying to locate it in the result.


When I press Ctrl+click on the desired column, SA showing exactly what I want in the result pane grid but I want to see the same result in the SqlAssit code completion drop-down. What is so difficult here I don't understand? Instead of populating the result pane, just populate code completion upon pressing the special shortcut key (forget about special macro variables).

You have already done it; just need to fill another control with column values instead of the result pane grid.

Anyways I leave this suggestion here. After all, you guys are real developers behind SA and you know more than anyone else!
Fri Sep 10, 2021 5:28 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
The system catalog data for SQL Intellisense is cached in memory, else it would be so slow that it would be unusable. We cannot cache sample data for all columns in the database. And I'm certain that doing on the fly SELECT DISTINCT would kill it too. Unfortunately that is not a feasible option.
Fri Sep 10, 2021 3:59 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.