SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Is there a way to generate SELECTs with table aliases?

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
Is there a way to generate SELECTs with table aliases?
Author Message
SqlExplorer



Joined: 18 Sep 2011
Posts: 98

Post Is there a way to generate SELECTs with table aliases? Reply with quote
Hi,

Can I configure the Copy SQL Code as/SELECT statement/SQL to add ALIASES to the table, and precede each generated field with the alias?

E.g., for CUSTOMER the SELECT might be

Code:
SELECT
  a.account_number,
  a.customer_name,
  a.....
FROM
  accounts.customer a

Tue Apr 19, 2022 1:06 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7828

Post Reply with quote
I'm sorry, I overlooked your post yesterday.

The context menu based shortcut in DB Explorer Copy SQL Code As -> SELECT -> SQL generates SELECT query for a table, the same query it would use internally for quick data preview or data export, and it then just copies that internal query to the editor. Unfortunately you cannot customize that internal SELECT query. If the SQL Intellisense isn't enough for what you want,
here is an alternative method that you may find usable because it enables some degree of customization using code snippets.

So, create a code snippet with the following code
Code:
$$
SELECT line_text
FROM (
SELECT 0 AS ordinal_position, 'SELECT' AS line_text
UNION ALL
SELECT DISTINCT ordinal_position,
   CASE WHEN ordinal_position = 1 THEN '    ' ELSE '    ,' END + 'a.' + column_name AS line_text
FROM information_schema.columns
WHERE table_name = '$CURRENT(word)$'
UNION ALL
SELECT 9999 AS ordinal_position,  'FROM $CURRENT(word)$ a' AS line_text
) t
ORDER BY ordinal_position
$$


Note use of "+" or "||" in the above code depends on the database type you work with.

In the code snippet properties choose to insert the output into code (that's editor) and also choose formatting style you want to apply to the code before it gets inserted.

Now, to use that snippet, drag-and-drop table name from the database explorer on to the editor. Right after that press Ctrl+Shift+Space to invoke the command palette. Click your new code snippet in the list (look for command beginning with "SELECT line_text"). And viola, you got the code generated. Hint, to make that snippet always visible without scrolling, resize the command palette window, it will remember its size next time you use it.

While you can achieve similar result using built-in SQL Intellisense with the about the same amount of clicks and key presses, here with the code snippet you have the flexibility to customize output more to your liking adding additional conditions, your own way of choosing aliases, etc... basically adding more code logic to the snippet
Wed Apr 20, 2022 11:18 pm View user's profile Send private message
SqlExplorer



Joined: 18 Sep 2011
Posts: 98

Post Reply with quote
I'll give this a try, thanks.

SysOp wrote:
I'm sorry, I overlooked your post yesterday.

The context menu based shortcut in DB Explorer Copy SQL Code As -> SELECT -> SQL generates SELECT query for a table, the same query it would use internally for quick data preview or data export, and it then just copies that internal query to the editor. Unfortunately you cannot customize that internal SELECT query. If the SQL Intellisense isn't enough for what you want,
here is an alternative method that you may find usable because it enables some degree of customization using code snippets.

So, create a code snippet with the following code
Code:
$$
SELECT line_text
FROM (
SELECT 0 AS ordinal_position, 'SELECT' AS line_text
UNION ALL
SELECT DISTINCT ordinal_position,
   CASE WHEN ordinal_position = 1 THEN '    ' ELSE '    ,' END + 'a.' + column_name AS line_text
FROM information_schema.columns
WHERE table_name = '$CURRENT(word)$'
UNION ALL
SELECT 9999 AS ordinal_position,  'FROM $CURRENT(word)$ a' AS line_text
) t
ORDER BY ordinal_position
$$


Note use of "+" or "||" in the above code depends on the database type you work with.

In the code snippet properties choose to insert the output into code (that's editor) and also choose formatting style you want to apply to the code before it gets inserted.

Now, to use that snippet, drag-and-drop table name from the database explorer on to the editor. Right after that press Ctrl+Shift+Space to invoke the command palette. Click your new code snippet in the list (look for command beginning with "SELECT line_text"). And viola, you got the code generated. Hint, to make that snippet always visible without scrolling, resize the command palette window, it will remember its size next time you use it.

While you can achieve similar result using built-in SQL Intellisense with the about the same amount of clicks and key presses, here with the code snippet you have the flexibility to customize output more to your liking adding additional conditions, your own way of choosing aliases, etc... basically adding more code logic to the snippet

Thu Apr 21, 2022 8:43 am 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
Page 1 of 1

 
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.